Hello and Thanks in advance for any help. I am using Access 2000.
I have a data entry form that opens a main form (using the On Click
event of the combo box [cboDESeledct]) with tab controls and 1 subform
on the last tab control. The main form is opened using a where clause
to restrict the contact type. The subform is built on a separate
table that lists other contact types that the contact could be a
member of. Example a volunteer could also be a donor or a vendor,
etc. I would like to open the form where the contact type in either
the main form or the subform is the same.
I can get correct results using a query, but when I paste the SQL
statement into the procedure I receive an error "Syntax error. in
query expression...
Here is the code (the strLinkCriteria is all on one line):
Private Sub cboDESelect_Click()
On Error GoTo Err_cboDESelect_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmContacts"
stLinkCriteria = "SELECT tblContacts.*,
tblContacts.ID_contact_type, tblMulti_Contact_type.ID_contact_type AS
[member of] FROM tblContacts LEFT JOIN tblMulti_Contact_type ON
tblContacts.ID_contact = tblMulti_Contact_type.ID_contact WHERE
(((tblContacts.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]))
OR (((tblMulti_Contact_type.ID_contact_type)=[forms]![frmDataEntryMenu]![cboDESelect]));"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cboDESelect_Click:
Exit Sub
Err_cboDESelect_Click:
MsgBox Err.Description
Resume Exit_cboDESelect_Click
End Sub
I tried to remove the parens and join the parts of the statement with
"&" (as per other posts on the subject) but received an error of
Object Required.
Again, Thanks for any and all help. Liz