Populate combobox with sql from DAO
 

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

Populate combobox with sql from DAO
 

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