Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate text box with sql from DAO
Hi,
I'm using Excel 2000 on Windows 2000 professional. I am using/revising code from http://support.microsoft.com/kb/q148361/-- Visual Basic Example to Open a RecordSet Using DAO I'm pulling from an Access database: EAIQuote_be.mdb from table: tblCompetitorScrubbed with two fields: CompetitorNumber EAIPartNumber Here is sample data: Competitor Number: EAI Number 209M418-19B 3140-18055 209M418-19B 3140-70918055 209M420-19B 3140-20055 211-585-9111 11140F18-55W 310AS001N22 3154-22055 310AS001NF12 3154-12055 310AS001NF28 3154-28055 310BS002B14A 3418-12C0-03 310BS002B15A 3418-16C0-03 On Sheet 1, I have two textboxes: textbox1 and textbox 2 The user will enter the competitor number in textbox1 and the procedure should populate textbox2 with the EAI number I'm unable to figure out how to populate textbox 2 with the EAI Part Number. I show where I think the code should change with dashes below. Does anyone have suggestions? Once I get this to happen, there are other requirements I must meet. 1. Sometimes the query will return more that one record. I need to find a way for the user to choose the proper record. 2. If there is no competitor number, the user will data enter the EAI number in textbox2. 3. We quote testing and certifications which do not have a part number. The user will need to type Cert or Test in textbox2 and enter the tested part number in textbox1 Thanks, Dan Here's the code : Option Explicit Sub CreateRecordSet() Dim oldDbName As String Dim wspDefault As Workspace Dim dbsEAIQuote As Database Dim strSQL As String Dim strCompetitorPart As String Dim strEAIPart As String Dim rstFromQuery As Recordset strCompetitorPart = Sheet1.TextBox1.Text strEAIPart = Sheet1.TextBox2.Text 'Set the path to the database oldDbName = "C:/My Documents/Quote/EAIQuote_be.mdb" 'Create a default workspace Object Set wspDefault = DBEngine.Workspaces(0) 'Create a Database object Set dbsEAIQuote = wspDefault.OpenDatabase(oldDbName) 'The SQL statement strSQL = "SELECT tblCompetitorScrubbed.CompetitorNumber, " & _ "tblCompetitorScrubbed.EAIPartNumber FROM tblCompetitorScrubbed " & _ "WHERE (tblCompetitorScrubbed.CompetitorNumber= '" & strCompetitorPart & "')" 'Create a Snapshot Type Recordset from the SQL query Set _ rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot) --I don't need the messageboxes-- --I don't know how to populate textbox2-- 'Show the number of fields returned MsgBox "there are " & rstFromQuery.Fields.Count & _ " fields that were returned" 'Move to the last record in the recordset rstFromQuery.MoveLast 'Put the EAI part number in textbox2 'strEAIPart = rstFromQuery. 'Show the number of records returned MsgBox "there are " & rstFromQuery.RecordCount & _ " records that were returned" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate text box with sql from DAO
On Jan 9, 9:47*am, dan dungan wrote:
Hi, I'm using Excel 2000 on Windows 2000 professional. I am using/revising code fromhttp://support.microsoft.com/kb/q148361/-- Visual Basic Example to Open a RecordSet Using DAO I'm pulling from an Access database: EAIQuote_be.mdb from table: * tblCompetitorScrubbed with two fields: CompetitorNumber * * * * * * * * * * * EAIPartNumber Here is sample data: Competitor Number: * * *EAI Number 209M418-19B * * * * * * 3140-18055 209M418-19B * * * * * * 3140-70918055 209M420-19B * * * * * * 3140-20055 211-585-9111 * * * * * *11140F18-55W 310AS001N22 * * * * * * 3154-22055 310AS001NF12 * * * * * *3154-12055 310AS001NF28 * * * * * *3154-28055 310BS002B14A * * * * * *3418-12C0-03 310BS002B15A * * * * * *3418-16C0-03 On Sheet 1, I have two textboxes: textbox1 and textbox 2 The user will enter the competitor number in textbox1 and the procedure should populate textbox2 with the EAI number I'm unable to figure out how to populate textbox 2 with the EAI Part Number. I show where I think the code should change with dashes below. Does anyone have suggestions? Once I get this to happen, there are other requirements I must meet. 1. * *Sometimes the query will return more that one record. * * * *I need to find a way for the user to choose the proper record. 2. * * If there is no competitor number, the user will data enter * * * * the EAI number in textbox2. 3. * * We quote testing and certifications which do not have * * * * a part number. The user will need to type Cert or Test * * * * in textbox2 and enter the tested part number in textbox1 Thanks, Dan Here's the code : Option Explicit Sub CreateRecordSet() * * *Dim oldDbName As String * * *Dim wspDefault As Workspace * * *Dim dbsEAIQuote As Database * * *Dim strSQL As String * * *Dim strCompetitorPart As String * * *Dim strEAIPart As String * * *Dim rstFromQuery As Recordset * * *strCompetitorPart = Sheet1.TextBox1.Text * * *strEAIPart = Sheet1.TextBox2.Text * * *'Set the path to the database * * *oldDbName = "C:/My Documents/Quote/EAIQuote_be.mdb" * * *'Create a default workspace Object * * *Set wspDefault = DBEngine.Workspaces(0) * * *'Create a Database object * * *Set dbsEAIQuote = wspDefault.OpenDatabase(oldDbName) * * *'The SQL statement * * *strSQL = "SELECT tblCompetitorScrubbed.CompetitorNumber, " & _ * * * * "tblCompetitorScrubbed.EAIPartNumber FROM tblCompetitorScrubbed " & _ * * * * "WHERE (tblCompetitorScrubbed.CompetitorNumber= '" & strCompetitorPart & "')" * * *'Create a Snapshot Type Recordset from the SQL query * * *Set _ * * *rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot) --I don't need the messageboxes-- --I don't know how to populate textbox2-- textbox.value = rstFromQuery!EAIPartNumber * * *'Show the number of fields returned * * *MsgBox "there are " & rstFromQuery.Fields.Count & _ * * *" fields that were returned" * * *'Move to the last record in the recordset * * *rstFromQuery.MoveLast * * 'Put the EAI part number in textbox2 * * 'strEAIPart = rstFromQuery. * * *'Show the number of records returned * * *MsgBox "there are " & rstFromQuery.RecordCount & _ * * *" records that were returned" * End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate text box with sql from DAO
Thank you!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I populate text from one page to several others? | Excel Worksheet Functions | |||
Auto Populate Text | Excel Discussion (Misc queries) | |||
Populate text box for graphs | Excel Discussion (Misc queries) | |||
Populate a cell from a text box. | Excel Programming | |||
Populate list or text box | Excel Programming |