Ok. I hadn't selected the proper reference--Microsoft DAO 3.6 object
library.

Also I had mispelled the table name in the sql statement.

So now the code I'm using (shown below) doesn't populate the combobox.

I know sql part is working becuase I typed my varialbe in the
Immediate window and copied the sql to the access database and it
returned the proper data.

However 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 suggestion?

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