Trying to open a form based on two values? 2

[b31luv]

I've tried looking through the threads to find something to lead me in the right direction. I have I made some changes to my database based on information I received in this form. Previously I was concatenating cells to get my primary key. Now I'm leaving the cells or fields as they are and making two attributes equal my primary key. I use a button control to open a form. When I setup the button, I was only able to use one stLinkCriteria.

Private Sub OpenEquipment_Click()
On Error GoTo Err_OpenEquipment_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmEquipment"
    
    Select Case fraContract
    Case 1
        strLinkCriteria = "[SiteNameID]=" & "'" & Me![SiteNameID] & "'"
    Case 2
        strLinkCriteria = "[AreaCode]=" & "'" & Me![AreaCode] & "'"
    Case Else
    End Select

    stLinkCriteria = "[SiteNameID]=" & "'" & Me![SiteNameID] & "'"
    stLinkCriteria = stLinkCriteria 'And "[AreaCode]=" & "'" & Me![AreaCode] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenEquipment_Click:
    Exit Sub

Err_OpenEquipment_Click:
    MsgBox Err.Description
    Resume Exit_OpenEquipment_Click
    
End Sub

This is some code I found and revised to what I thought was correct.

I removed this portion on purpose because I keep getting this type mismatch error:

'And "[AreaCode]=" & "'" & Me![AreaCode] & "'"

Any help would be deeply appreciated.

 

[traingamer]

If AreaCode is a number it doesn't need to be quoted.

 stLinkCriteria = stLinkCriteria 'And "[AreaCode]=" & Me![AreaCode]

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

Syntax error, replace this:
stLinkCriteria = stLinkCriteria 'And "[AreaCode]=" & "'" & Me![AreaCode] & "'"
By either this:
stLinkCriteria = stLinkCriteria & " And [AreaCode]='" & Me![AreaCode] & "'"
Or this:
stLinkCriteria = stLinkCriteria & " And [AreaCode]=" & Me![AreaCode]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Try the following concatenation:

[tt]stLinkCriteria = stLinkCriteria & " And [AreaCode]='" & Me![AreaCode] & "'"[/tt]

- i e - concatenation a string startering with " And... and, if area code is numeric, follow traingamer's advice on removing the single quotes.

Roy-Vidar

 

[traingamer]

duh. I looked right at that.

 

[b31luv]

Man, you guy are on the ball today. Thanks for your assistance. The winning combinations was:

stLinkCriteria = stLinkCriteria & " And [AreaCode]='" & Me![AreaCode] & "'"

Have a nice day!!!

 

[b31luv]

Also, based on the information provided. I realized I didn't need to use the Case Statement. When would I need to use a Case Statement? Or does anyone know of a good link that goes into that?

 

[HappyRed]

A Case does the same thing that an "If then Else"
but "If then Else" only allows you to choose between 2 options.

It's either Blondes or Brunnets?

You have to use Case when there are more than 2 options.

Your Equiment could be shipped from Asia, Europe, USA or Latin America then if you had to branch and do different things depending on the country of origin you would use a
Case statement

Case Asia

Case Europe

Case Latin America

Case USA

Because you can't use an IF Then Else.

I hope this helps.