Can't create recordset to populate userform combobox

[dan dungan]

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

 

[Simon Murphy]

Hi Dan
why have you got this in before you loop:
'Move to the last record in the recordset
rstFromQuery.MoveLast

?
That will take you straight to the end of your rs.

What happens if comment out that line?

Cheers
Simon
Excel development website: www.codematic.net

 

[dan dungan]

Hi Simon,

I commented that out, but the program still jumps to the end of the
sub.

Then I commented out the line:
On Error GoTo CreateRecordSetErrorHandler

and the line:
Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot)

returned the error, run time error 13: type mismatch

Dan

 

[dan dungan]

I changed the data type from Dim rstFromQuery As Recordset
to Dim rstFromQuery As Variant, and it seems to be working now.