How To Use Multiple Criteria To Open Records? 3

[HMJ]

I have a form with a sub-form that works fine – up to a point.

The sub-form is a datasheet listing of cases that are assigned to a specific client. So far so good.

The sub-form has a button that will open the specific record(case) if clicked. NOT GOOD. In order to do this, I have to look for both the ‘client id’ and the ‘client case’ to insure that the correct record is opened.

The code I am trying to use is:

Private Sub Details_Click()
On Error GoTo Err_Details_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Client Case Details"
    
    stLinkCriteria = "[Client No]=" & "'" & Me![Client No] & "'" 'And' "[Client Case]=" & "'" & Me![Client Case] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Details_Click:
    Exit Sub

Err_Details_Click:
    MsgBox Err.Description
    Resume Exit_Details_Click
    
End Sub

It does not open the correct record.

Anyone able to give me some guidance here?

Thanks.


Harry Jessen
HMJessen@Yahoo.com

 

[RoyVidar]

Hi!

[tt]stLinkCriteria = "[Client No]='" & Me![Client No] & _
"' And [Client Case]='" & Me![Client Case] & "'"[/tt]

This is assuming both fields are of datatype text. Since the first ones name indicates a numeric variable, numerics don't need qualifier:

[tt]stLinkCriteria = "[Client No]=" & Me![Client No] & _
" And [Client Case]='" & Me![Client Case] & "'"[/tt]

Roy-Vidar

 

[HMJ]

Roy:

Even though the field name is 'Client No' it is a text field. This is the customers desire, not mine.

I am currently home, but will try this first thing in the morning.

Could you breakdown the structure of the 'and/or' multiple criteria statement? I barely understand the single criteria, trying to understand the multiple one is what has me blown away.

If you want, you could use email, or just send me a link to a site that would help.

Thank You!!

Harry Jessen
HMJessen@Yahoo.com

 

[RoyVidar]

If you take a look in the help files on the openreport or openform methods of the docmd object, you'll find the following sentence (but it shows only a one field criteria):

"WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE."

So, if you know queries, it's just the Where part, with and, or, like etc and add the qualifiers needed to make the jet engine avare of the datatype.

[tt]...Field1 = " & lngNumeric & _ ' numerics, no quelifier
" AND Field2 = #" & dtMyDate & _ ' dates, hash (#)
" AND Field3 = '" & strMyString & "'" ' text, apostrophe (')[/tt]

Don't know any specific sites, but Learn Thy SQL;-) Perhaps someone else have sites?

Taking a look in the Access specific fora here on TT might be worth the time. Having a good Access/VBA reference book is also "a must"...

Roy-Vidar

 

[Storyteller]

Hello RoyVidar,
I've been searching for the syntax to allow for multiple criteria in the WhereCondition.

It turns out that I didn't have the single quote(s) ' in the proper location. Arrgghh.
Here is my code with the proper quote(s)
stLinkCriteria = "[Company]='" & Me![Company] & " ' And [Surname]='" & Me![Surname] & "'"

But, thanks to your example I was able to get it to work.
Thanks and here is a star for you.

Regards,
Michael