Hi

Using Excel 2000 and Access 2000, the code below was working to
populate a combobox on a spreadsheet.

I'm attempting to use it to populate a combobox--cboQpn--on Userform4.

I want the user to type a competitor part number in textbox--
txtCompNum.

Then the macro will use that value to find our part number in the
access table and put the result in the combobox. I'm using a combobox
because there may be more than one record returned.

The code fails on this line:    Dim wspDefault As Workspace with the
error, "compile error: User-defined type not defined"

Thanks for your feedback,

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 tblCrossNoDash.Scrubbed, " & _
        "tblCrossNoDash.EAIPartNumber FROM tblCrossNoDash " & _
        "WHERE (tblCrossNoDash.Scrubbed= '" & 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

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