Connecting to Access database to populate a combobox.

[dan dungan]

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

 

[dan dungan]

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