Populate combobox with sql from DAO

[dan dungan]

With Excel 2000 and windows 2000, I'm attempting to use the following
code to populate a combobox, but I'm getting the
compile error: "method or data member not found" one the
with statement:

With rstFromQuery
..AddItem is highlighted in blue

Does anyone have suggestions about what I'm doing wrong?

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 = Sheet6.TextBox3.Text
strEAIPart = Sheet6.ComboBox2.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
With rstFromQuery
If Not .BOF Then .MoveFirst
While Not .EOF
.AddItem rstFromQuery
.MoveNext
Wend
End With
'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 textbox2
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]

Hi,

I neglected to mention that I'm not using a userform--the control
object are directly on the worksheet.

I'm running the code from a command button click event.

Textbox2 holds the search criteria:

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
If TextBox3.Value = "" Then
If KeyCode = 13 Then
TextBox3.SelText = Replace(TextBox2.Text, "-", "")
End If
Else
Exit Sub
End If
End Sub

Textbox3 holds the search criteria with dashes removed

Then I search the database

Combobox2 should hold the results of the query.

Thanks,

Dan

 

[SeanC UK]

Hi Dan,

Because of the With statement you are effectively saying:

rstFromQuery.AddIten rstFromQuery

So you appear to be attempting to add the recordset as an item to itself. I
haven't looked into this, but it would seem something that wouldn't be
allowed, and I would presume that a recordset would not be the correct data
type to be an item within a recordset. Also, if you add something to the
recordset whilst you iterate through the recordset, you would be in an
eternal loop, the end would never be reached as you keep adding an item as
you loop through it.

Having just looked at your question again, I presume you wanted something
like:

With cboComboBox
rstFromQuery.MoveFirst (no need to check BOF, just do this anyway)
While Not rstFromQuery.EOF
.AddItem ......... etc

Hope this helps,

Sean.