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 form which's button openned the form that the subform is on). I'm trying to get the value from a combobox that contains the data that will filter the subform in question. 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].Form.[cboSelectUser].Column(0)))"
strSQL1 = strSelect & strFrom & strJoin
strSQL2 = strSelect & strFrom & strJoin & strWhere

If IsNull([Forms]![frmMainEntry].Form.[cboSelectUser].Column(0)) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = strSQL1
Else
Me.RecordSource = strSQL2
End If
==========================================

I can't resolve the error:
==========================================
Runtime error '3085':
Undefined function
'[Forms]![frmMainEntry].Form.[cboSelectUser].Column' in expression.
==========================================

Somehow access thinks that the references to the combobox is a function. Can someone help me with this?

Thanks,
PC

 

[ghudson]

If IsNull([Forms]![frmMainEntry].Form.[cboSelectUser].Column(0)) Then

If IsNull(Forms!frmMainEntry.cboSelectUser) Then

 

[PC User]

Thank you for your reply, ghudson. I must be getting a little closer, but I get a little popup window 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 "". What am I doing wrong. Here's the changed code.
===========================
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!cboSelectUser) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = strSQL1
Else
Me.RecordSource = strSQL2
End If
===========================
Thanks,
PC

 

[PC User]

I did find the solution. When 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 & "'"

After making this change, my code works.

PC

 

[ghudson]

Glad you solved your problem.