Hi,

So with Excel 2000, the code I'm using (shown below) doesn't populate
the
combobox.

I'd like to type a competitor's part number in the textbox,
txtCompNum.text and use that to find a match in the access database
table, tblCompetitorScrubbed, and return my company part number.

I believe the sql statement is correct because I typed my varialbe, ?
strSQL, in the
Immediate window. That returned an sql statement that worked when I
copied that statement and manually created a new query in the access
database with it, and the query returned the proper data.

However, it appears to me the recordset is never created with this
line:
Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot)

and the combobox is not populated.

Here's the code I'm using.

Does anyone have a ideas how to debug?

Thanks,

Dan

Sub CreateRecordSet()
On Error GoTo CreateRecordSetErrorHandler
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 = UserForm4.txtCompNum.text
strEAIPart = UserForm4.cboQpn.text

'Set the path to the database
oldDbName = "K:/Customer Service/Quote/Database/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.EAIPartNumber " & _
"FROM tblCompetitorScrubbed " & _
"WHERE (tblCompetitorScrubbed.CompetitorNumber= '" &
strCompetitorPart & "')"

'Create a Snapshot Type Recordset from the SQL query
Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL,
dbOpenSnapshot)

'load up combobox

'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 Combobox2

Do While Not rstFromQuery.EOF
UserForm4.cboQpn.AddItem rstFromQuery(1).Value
rstFromQuery.MoveNext
Loop

' Sheet6.ComboBox2.DropDown = rstFromQuery!EAIPartNumber
'Show the number of records returned
' MsgBox "there are " & rstFromQuery.RecordCount & _
' " records that were returned"
Exit Sub
CreateRecordSetErrorHandler:

End Sub