Populate text box with sql from DAO

[dan dungan]

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

 

[SmartbizAustralia]

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