Referencing A ComboBox

[PC User]

I have code on a subform that is referencing another form that is still
open (not the parent form, but the main form that has the opening
button on it). I'm trying to get the value from a combobox on the main
form that contains the data that will filter the subform in question.
The error that I get is a little popup window asking Enter Parameter
Value and there's a yellow highlight over Me.RecordSource = strSQL2.
When I put the "I" bar cursor over the formula there's a correct value
for strSQL2, but Me.RecordSource give me a "". The Me.RecordSource =
strSQL seems like it should work, but it doesn't. What am I doing
wrong.
See below.
==========================================
Dim strForm As String
Dim strSQL1 As String, strSQL2 As String, strWhere As String
Dim strSelect As String, strFrom As String, strJoin As String

strSelect = "SELECT tblMainData.* "
strFrom = "FROM tblMainData "
strJoin = "INNER JOIN tsubPermissionList ON
tblMainData.ResponsibleParty = tsubPermissionList.FullName "
strWhere = "WHERE tsubPermissionList.UserID = " &
Forms!frmMainEntry!cboSelectUser
strSQL1 = strSelect & strFrom & strJoin
strSQL2 = strSelect & strFrom & strJoin & strWhere

If
IsNull(Forms("frmMainEntry").Form.Controls("cboSelectUser").Value) Then
' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = strSQL1
Else
Me.RecordSource = strSQL2
End If
==========================================
Thanks,
PC

 

[Dirk Goldgar]

I have code on a subform that is referencing another form that is
still open (not the parent form, but the main form that has the
opening button on it). I'm trying to get the value from a combobox on
the main form that contains the data that will filter the subform in
question. The error that I get is a little popup window asking Enter
Parameter Value and there's a yellow highlight over Me.RecordSource =
strSQL2. When I put the "I" bar cursor over the formula there's a
correct value for strSQL2, but Me.RecordSource give me a "". The
Me.RecordSource = strSQL seems like it should work, but it doesn't.
What am I doing wrong.
See below.
==========================================
Dim strForm As String
Dim strSQL1 As String, strSQL2 As String, strWhere As String
Dim strSelect As String, strFrom As String, strJoin As String

strSelect = "SELECT tblMainData.* "
strFrom = "FROM tblMainData "
strJoin = "INNER JOIN tsubPermissionList ON
tblMainData.ResponsibleParty = tsubPermissionList.FullName "
strWhere = "WHERE tsubPermissionList.UserID = " &
Forms!frmMainEntry!cboSelectUser
strSQL1 = strSelect & strFrom & strJoin
strSQL2 = strSelect & strFrom & strJoin & strWhere

If
IsNull(Forms("frmMainEntry").Form.Controls("cboSelectUser").Value)
Then ' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = strSQL1
Else
Me.RecordSource = strSQL2
End If
==========================================
Thanks,
PC

If the UserID field is a text field, you need to enclose the value for
it (which you pick up from the combo box) in quotes. If that value
won't contain the single-quote qharacter ('), try this:

strWhere = _
"WHERE tsubPermissionList.UserID = '" & _
Forms!frmMainEntry!cboSelectUser & "'"