[jmorlan]

I am using Access 2010-

I have a sql statement that has two variables to set the rowsource for a combo box

This is what I have-

Private Sub Form_Load()
'Form Name Information
Dim strFormName As String
strFormName = Me.Form.Name
'Form Number Information
Dim IntFormNumber As Long
IntFormNumber = DLookup("[frm_nmbr]", "Tbl_FormManager", "[frm_name] = '" & strFormName & "'")
MsgBox strFormName & "" & IntFormNumber ' Used to make sure I was getting the correct information back
'ComboBox RowSource Information
Dim FormCtrl As Control
Dim strCtrlName As String
Dim intCtrlNumber As Long
Dim strSQL As String
For Each FormCtrl In Me.Controls
If TypeName(FormCtrl) = "ComboBox" Then
strCtrlName = FormCtrl.Name
intCtrlNumber = DLookup("[ctrl_nmbr]", "Tbl_ControlManager", "[ctrl_name]='" & strCtrlName & "'")
MsgBox strCtrlName & "" & intCtrlNumber ' Used to make sure I was getting the correct information back
strSQL = "SELECT Tbl_DropDownManager.fld_value, Tbl_DropDownManager.fld_text, Tbl_BringItTogether.frm_nmbr, Tbl_BringItTogether.ctrl_nmbr, Tbl_BringItTogether.fld_pos FROM Tbl_BringItTogether INNER JOIN Tbl_DropDownManager ON Tbl_BringItTogether.fld_val = Tbl_DropDownManager.fld_value WHERE Tbl_BringItTogether.frm_nmbr = '" & IntFormNumber & "' And Tbl_BringItTogether.ctrl_nmbr = '" & intCtrlNumber & "'  ORDER BY Tbl_BringItTogether.fld_pos;"
FormCtrl.RowSource = strSQL
End If
Next
End Sub

I keep getting a data mismatch error when I click on the combo box. I think it has something to do with the quotation's and apostrophe's around the Variable names (Highlighted with Red Text), but I can't find how I should do it.

I think this is really simple I just have been trying to figure it out for a while that is has become really frustrating.

[Zack Barresse]

If you're trying to pass a number, you shouldn't need any qualifiers surrounding it. You may already know this, but here's some good info on these things:

http://www.fontstuff.com/access/acctut15.htm#5mincourse

HTH

[Ziggy1]

Access 2010, so hard to keep current, I have 2007.... anyways. have you tried removing the single quotes around the number types? in the SQL?

I removed them and changed the sql arrangement slightly to make it more readable....

strSQL = "SELECT Tbl_DropDownManager.fld_value, Tbl_DropDownManager.fld_text, Tbl_BringItTogether.frm_nmbr, Tbl_BringItTogether.ctrl_nmbr, Tbl_BringItTogether.fld_pos "
strSQL = strSQL + "FROM Tbl_BringItTogether INNER JOIN Tbl_DropDownManager ON Tbl_BringItTogether.fld_val = Tbl_DropDownManager.fld_value "
strSQL = strSQL + "WHERE Tbl_BringItTogether.frm_nmbr = " & IntFormNumber & " And Tbl_BringItTogether.ctrl_nmbr = " & intCtrlNumber & "  "
strSQL = strSQL + "ORDER BY Tbl_BringItTogether.fld_pos;"

I recommend you use the "Imediate" window to debug the sql string, along with break points..... or when it errors:

put this in the immediate window....

?strSQL then hit enter Then you can see the SQL better than trying to use a message box

[jmorlan]

Ziggy1,

I copied your code into my database to start working with the cleaned up version and well I guess you solved my problem, it works fine now.

I am not totally for sure how, but I will put your code example in my reference database for future use.
Appreciate the help!!!!

Thanks,

Jay

[Zack Barresse]

Originally Posted by jmorlan

I am not totally for sure how...

Did you read the thread I posted? It tells you how, or rather why, the solution works.

[Ziggy1]

that was actually a good link Zack.


Jay its easy to get lost in the VBA when doing the SQL, especially when quotes are involved.... so be sure to test with the immediate window.

Another trick you can do is copy the output from the immediate window and paste it into a SQL view of the query design grid.... then switch to design view or sheet view to check your output (may not work in every scenario).... this helps you troubleshoot.