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