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

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

Populate text box with sql from DAO
 

Thank you!