Passing RowSource back to Form 1

[ve7epq]

I am using a Combo box to limit a search of my data. So far if I change strSQL = strSQL1 & Me!cboCOMPANY.Value to strSQL = strSQL1 & "Me!cboCOMPANY.Value" and let it prompt me for the choice made in the event cboCOMPANY.Value the event works correctly. Otherwise it returns a blank screen.

Here is the code I have so far

Option Compare Database
Option Explicit

Const strSQL1 = "SELECT [qrySEARCH].[COMPANY NAME],[qrySEARCH].[TYPE],[qrySEARCH].[SERIAL_NUMBER],[qrySEARCH].[ORDERID],[qrySEARCH].[PO_NUMBER] FROM [qrySEARCH] WHERE [qrySEARCH].[COMPANY NAME] ='"
Private strSQL As String

Const strMsg1 = "Click below to display updated search information"
Const strMsg2 = "Select Company Name from List"
Const strMsg3 = "Select Serial Number from List"
Const strMsg4 = "Select PO number from List"

Private Sub cboCOMPANY_AfterUpdate()
If Me!cboCOMPANY.Value <> &quot;&quot; Then
Me!lblLIST.Caption = strMsg1
Else
Me!lblLIST.Caption = strMsg2
End If
End Sub

Private Sub MAINLST_GotFocus()
If Me!cboCOMPANY.Value <> &quot;&quot; Then
strSQL = strSQL1 & Me!cboCOMPANY.Value
Me!MAINLST.RowSource = strSQL
Me!MAINLST.Requery
Me!lblLIST.Caption = &quot;WORK ORDERS for &quot; & Me!cboCOMPANY.Value
End If
End Sub

Any help you can offer would be appreciated...I am stumped

 

[RoyVidar]

Hi!

strSQL1 finishes of with a text qualifier. I cannot see you adding it after the combo.value. From your code, it looks like the combo value is a string, then perhaps:

[tt]strSQL = strSQL1 & Me!cboCOMPANY.Value & &quot;'&quot;[/tt]

would do?

HTH Roy-Vidar