assign results of query(recordset) to a combo box 2

[Valeriya]

Hi guys! Got a problem with the following code...once code executed I get an error - "Type Mismatch"...Here is what I'm trying to accomplish..I have a Form in which user types in Customer (Cust) Number...That triggers the combo box(called TID)on that same form to populate with TIDs (tissue ids for that particular customer that user just keyed in)...To accomplish this I wrote SQL code that pulls all the TIDs for that particular customer with WHERE criterea being the imput into the Customer field on the Form. My next step is creating a recordset to which resultes of the query are assigned. Finally I'm trying to open the recordset and with it populate the combo box on my Form...here is the code:

Private Sub Cust_AfterUpdate()

Dim rstTid As ADODB.Recordset
Dim strSQL As String
Set rstTid = New ADODB.Recordset

strSQL = " SELECT [0001 Bin Detail].TID" & _
 " FROM [0001 Bin Detail]" & _
 " WHERE [0001 Bin Detail].Cust = '" & Me.Cust.Value & "';"

rstTid.Open Source:=strSQL, ActiveConnection:=CurrentProject.Connection,
CursorType:=adOpenStatic, _
Options:=adCmdText

TID.RowSourceType = "Table/Query"
Set TID.Recordset = rstTid
              
Set rstTid = Nothing

Thank you very much! I keep on saing it all the time, but it is the truth - " What would I do without you?

 

[Remou]

You do not need the connection bit:

TID.RowSourceType = "Table/Query"
Set TID.Recordset = strSQL

 

[ClydeDoggie]

Connection does not matter one way or the other. It doesn't hurt to have it in.
I just duplicated your scenario and the only way I could get your error was if [0001 Bin Detail].Cust was not a text value. If this is the case just get rid of the single quotes in your SQL string and you will be fine.

 

[Zion7]

Valerija, as ClydeDoggie said,
is Cust not a string?

2ndly, why the recordset? a little overkill?

Simply

Private Sub Cust_AfterUpdate()

Dim strSQL As String

strSQL = " SELECT TID" & _
" FROM [0001 Bin Detail]" & _
" WHERE Cust = " & Me.Cust.Value 'If numeric

TID.RowSourceType = "Table/Query"
TID.RowSource = strSQL

End Sub

 

[Valeriya]

Thanks to all of you guys! The data type was in fact numeric, so I took out the single quotes. Now the code works just fine. Zion7, I will also try your simpler version. I do see now that I made the problem more complicated than it really is. Again, thanks a lot to all of you for the help!

Valeriya