Tim,
The values are numbers (autonumbers to be exact) so I replaced the
triple double quotes with a single double quote. But I still get a
Syntax error. in query expression.... OR (((tblMu'.
The first WHERE clause works by itself.
The second WHERE clause by itself gives the "Enter Parameter Value"
I followed another suggestion to have the hidden field in the combox
box displayed on the form but that didn't solve the problem (and it
isn't user friendly). The second criteria is from a subform linked by
contact ID to the main form. Not all contacts have a record in this
subform, only those that have two contact types, ex: Volunteer and
Donor, etc. I tried to throw in a Not IsNull but that gave me all
records. I tried to put in the column number but that doesn't work
either.
I tried Allen Brownes suggestion to debug.print the criteria and put
that into a new query which works and gives me the records I'm looking
for. I've been reading everything I could find in the NG for the past
3 days, but cannot seem to find my mistake.
Hoping someone can help with this.
Again Thanks Liz
emalcolm@bellsouth.net (Liz Malcolm) wrote in message news:<aea22059.0410291730.6438f0f4@posting.google. com>...[color=blue]
> Hello Again
>
> I used the sample suggested, but it won't go past the OR statement.
> If I strip out just the OR statement I get an "Enter Parameter Value"
> for tblMulti_Contact_type.ID_Contact_type. This is the 2nd table that
> has 3 fields; ID, ID_Contact_type as a lookup field and contact ID.
> What could I be missing or doing wrong?
>
> Thanks, Liz
>
>
>
phil03@globalnet.co.uk (Shuffs) wrote in message news:<54947ba2.0410280413.79d2620c@posting.google. com>...[color=green]
> > Hi Malcolm,
> >
> > Try this as your SQL string - watch for word wrap!
> >
> > 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] & "'));"
> >
> > The above would take the value of your combo boxes and use the value
> > as criteria.
> >
> > Hope this helps.[/color]
>
>
>[color=green]
> > Cheers,
> >
> >
emalcolm@bellsouth.net (Liz Malcolm) wrote in message news:<aea22059.0410271244.3a78f94d@posting.google. com>...[color=darkred]
> > > 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[/color][/color][/color]
