Opening Forms with LINKED CRITERIA

[Gazer44]

Can anyone help. I'm having a problem opening a form with MULTIPLE linked criteria. I can open it fine with one linked criteria but not with more than one.

i.e. I can open form A and click on a button to open Form B with field 'RefNo' the same.

But cannot open form B with BOTH [RefNo] & [Initials] the same.

(I may poss. need 3 linked criteria as well :- [RefNo] and [Initials} and [TickID]

I'm sure it's easy but just can't get it !!

cheers

 

[vinanti]

hi,

I dont know whether this would solve your problem for sure...but just givin it try;

Listen,
u can do 1 thing,
try passing an argument with the openform action and then in the form load event check for the appropriate argument.
if argument satisfied then in the loaded form itself it would be very simple to get your queries fulfilled.

I think u should really try this.

Actually even i am not too much experienced but am working on such project so maybe it is fruitful.

anyways,all the best

bye,
vinanti

 

[Gazer44]

Do you have some VB code to show as an example as I've tried something similar and get an error.

I've tried the following code contained within the On Click event of the button :-

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FormB"
stLinkCriteria = "[Ref No]=" & Me![Ref No] And "[Initials]=" & Me![Initials]
DoCmd.OpenForm stDocName, , , stLinkCriteria


This throws out error of :- Type Mismatch

I had hoped this would open FormB with linked criteria of [RefNo] and [Initials] being the same as current form (FormA)

Any ideas ??

 

[jrbarnett]

Hi Gazer44,

Is the Initials field a string (ie it holds alphanumeric and punctuation characters?
If so, try

stLinkCriteria = "[Ref No]=" & Me![Ref No] And "[Initials]='" & Me![Initials] & "'"
---
Regards

John

 

[Gazer44]

yes the initials field can contain both letters and numbers but I still get the same error ??

 

[Gazer44]

p.s. It's a text field (should only have either letters or letters & no's in it)

 

[jrbarnett]

Hi Gazer44

I assume RefNo is numeric as it works with only this field, but if not put the apostrophes around the name of the field.

Another thing I can think of: If the refno is a numeric field and you enter, say 1234 as your ref no. is it saved as numeric or is it actually text in the table? I ask as this is often used as a workaround to preserve leading zeroes.

John

 

[Gazer44]

[Ref No] is an Auto number-(long intega) and [Initials] is a text field, does this help ??

 

[jrbarnett]

That is exactly what I thought. My posting of 7th May is correct for those two data types.

John

 

[vinanti]

hi gazer,

how did u solve it,
stuck up in something similar...
my code is:
even i get the error type mismatch
i even tried with and in place of & in the lnkcriteria.
please have a look;
suppnm = Forms!mod_frm!supp_nm
compnm = Forms!mod_frm!comp_nm
lnkcriteria = ("[supplier name] = '" & Me![supp_nm] & "'" & "[company]='" & Me![comp_nm] & "'&quot
modvar = "modify"
If compnm = "All" Then
DoCmd.OpenForm "mast", acNormal, , "[supplier name]= '" & suppnm & "'", , , modvar
Else
DoCmd.OpenForm "mast", acNormal, , lnkcriteria, , , modvar
End If
DoCmd.Close acForm, "MOD_FRM", acSaveYes

thnks in advance,

bye,
vinanti

 

[Gazer44]

Sorry, NOt actually solved it, tried the previous postings but they not worked !! still get type mismatch.

 

[vinanti]

hi gazer,

The solution to yur problem is here:

"thread702-545795"

Try it,if still dont get the soln tell me i have got one more way....


bye always there to help....provided i know

vinanti

 

[Gazer44]

Thanks for the info but STILL got probs.

I've put in code:-

stLinkCriteria = "[Ref No]=" & Me![Ref No] & "'And" & "'[Agent]=" & Me![Agent] & ""

but getting error:-

Syntax error (missing operator) in query expression '{Ref No]=1073'And'[Initials]=kb'


this must be just a typo error with the speech marks but can't find it.

p.s. the [Ref No] field is Number and the [Initials] field is text !

cheers

 

[Gazer44]

sorry, the code should read:-

should say :-stLinkCriteria = "[Ref No]=" & Me![Ref No] & "'And" & "'[Initials]=" & Me![Initials] & ""

and the Error should read:-:-

Syntax error (missing operator) in query expression '[Ref No]=1073'And'[Initials]=kb'


but can't see problem !!