stLinkCriteria clue???

[Kyriakos]

I used the command button wizard to create a button to open a new form. The wizard only allows you to link just one field to another but I need to link two in order to filter out records...Searching through the site helped me discover the stLinkCriteria command and its usage;and now I have an error appearing on my creen that the second field cannot be found? How is that possible? Any clues? My code goes something like

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Species"
   
    
    stLinkCriteria = "[Species]=" & Me![Species] & "AND [Offer_No]= " & Me![Number_Offer]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Am I missing something?

 

[clerics]

"[Species]=" & Me![Species] & "AND [Offer_No]= " & Me![Number_Offer]

Change "Me![Species]" to "[Form]![Your form's name]![Species]"

and make sure [Species] is really in this Form ! goodluck

this code is in my program. May be you can know how to set your code right

Private Sub EditButton_Click()

Dim strSQL As String

strSQL = "tblmain.ID1=" & [Forms]![Fmain]![F_Sub].form![ID1] & " AND tblmain.ID2=" & [Forms]![Fmain]![F_Sub].form![ID2] & " AND tblmain.ID3=" & [Forms]![Fmain]![F_Sub].form![ID3] 
DoCmd.OpenForm "FEdit", , , strSQL

End Sub

 

[Kyriakos]

Thanx for answering. But perhaps I wasn't clear enough. If I omit the offer number and link only the species field, it is working properly. If I add the offer number the problem occurs...And yes I have the field in my form

BTW Now it says type mismatch but I can't figure out the reason. All fields are text so why is this happening? Can someone figure this out?

 

[clerics]

Cuz you set [Offer_No]'s type is number , right ?
From Bat17 's way --> you can use CLng() to change the text which user inserted to number

---> "[Offer_No]= " & CLng (Form![Your Form 's name]![Number_Offer])

 

[Kyriakos]

Pat thanx for replying. I've tried it like you said and it does not prompt me for error but I still can't get the wanted results. It is like as if only one criteria is linking. After I enter the AND operand it is as if I don't enter anything at all. I still getting matching results for the first field only. I need matching two fields....

Any more ideas?

 

[Kyriakos]

I've changed the order and this is the code I used...

stLinkCriteria = "[Offer_No]= " & "'" & Me.[Number_Offer] & "'" & " AND [Species]= '" & Me.[Species] & "'"

Now I have noticed that the word AND does not turn into blue so there's certainly a syntax error. The matter is what exactly?

 

[sphere_monk]

I think you're missing single quotes in your statement. Try:

stLinkCriteria = "[Species]= '" & Me![Species] & "' AND [Offer_No]= '" & Me![Number_Offer] & "'"

In other words, the statement you should be trying to create is:

[Species] = 'value' AND [Offer_No] = 'value'

(note the single quotes around the values)

A good way to check what value stLinkCriteria is recieving is to use a msgbox to print is to the screen before actually executing it. Then you can see if it is what you're expecting.

Hope this helps,

Dan