I'm trying to use an SQL statement in the AferUpDate event of a Combo Box to fill a text box on a form.
I ran my SQL statement as a Query and it runs fine, but when I add the SQL statement to the AfterUpDate event, I get the SQL statement in my text box, not the value I'm looking for.

Private Sub cboBSCID_AfterUpdate()
Dim strSQl As String

strSQl = "SELECT Header.PONumber, Header.BSCID FROM Header WHERE (((Header.BSCID)=[forms]![JobInfo].[cboBSCID]));"


Me.txtPONumber = strSQl

End Sub

Change the Row Source Type of the combo box to Table/Query.

The Row Source Type of the combo box cboBSCID is Table/Query. My AfterUpdate Event is on this combo box. I'm trying to get the the value PONumber from the row that matches the cboBSCID number in the Header table into the text box txtPONUmber.

Hi
Could you use the DLookUp() to get that single value..
eg:

Me.txtPONumber = DlookUp("PONumber","Header","BSCID = "& forms!JobInfo!cboBSCID )

HTH's

I've change my form a little bit! I now want to use the SQL statement as the RecordSource for my subform(subJobInfo)

Sub SetFilter()
Dim strSQL As String


strSQL = "Select* From Header Where Header.[BSCID] = '" & cboBSCID & "';"
Forms!JobInfo!subJobInfo.Form.RecordSource = strSQL
End Sub

The Code bugs on the Forms!-RecordSource line. Is it the call for the sub form recordsource or my SQl statement?

Hi
Try:

The Code still bugs on the sub form RecordSource line.

Hi
"Bugs" or "Error" messages..!!

Run Time Error "3464"
Data type mismatch in criteria expression.

Hi
Can you place a Debug.Print after the building of the sql..

Then Ctrl-G to open up the window and view the statement and see what values are being passed..

eg:


Can you confirm that Header.[BSCID] is actually is a string type..!!

Select * From Header Where Header.[BSCID] = '2';

Returned in the Immediate Window
The Field BSCID is a Long Integer

Hi
So it's not a text/string type..

Ok..try:

the code runs, but it ask for a Parameter value for Me.cboBSCID.

Hi
Is cboBSCID an actual control on the form..!!
I was assuning it was...
So I place a reference in front of the control name..
Me.cboBSCID

yes, It's the name of the ComboBox which is choosing the value. When I start typing "Me." VBA has cboBSCID as a choice.

Hi
If it's in the list then it's in the form object list..
It should recognize the value within the selected control.

I'm trying to run this code as Sub on the AfterUpdate event on the same ComboBox " cboBSCID", could this be the problem?

Private Sub SetFilter()

Dim strSQL As String

strSQL = "Select * From Header Where Header.[BSCID] = Forms!JobInfo.cboBSCID ;"


Me.subJobInfo.Form.RecordSource = strSQL



End Sub


I made this change to the code, it now works, but (Always a But!) I'm not getting data for some jobs! Shouldn't this SQL select the entire row of fields where BSCID and cboBSCID are =. I'm getting all the fields or nothing!

I moved the code to the AfterUpdate Event Procedure and it works fine, It didn't work as a Sub Procedure.

If the combobox holds BSCID and BSCID is a number, not a string, then


Note, no single quotes around the BSCID.