Global Parameter in form's query 1

[Soundsmith]

I'm trying to use a global parameter as part of a SELECT query (qry1Claim)as the Recordsource for a form, which I typically call from another form via a command button.

In the Open event of the form, I have a test to verify there is a value set for theClaim (there is) but it does not appear to be seen by the form.

(global declaration in the main module)
Public theClaim AS Long

(the query, qry1Claim)
SELECT * from qryX WHERE [qryX].[claimNo]=[theClaim]

the Form's Recordsource is set to qry1Claim

If I click on qry1Claim, it asks for theClaim, then returns the correct data set, as I would expect.

If I open the form from my main form, a value for theClaim exists, and I would expect the query to execute-but it always asks for the value for theClaim again (it also asks every time I do a me.requery.) If I re-enter the claim no, the data displays.

I have tried this as a normal SELECT and also by setting the query to be a parameter query, but there is no difference.

What am I missing/doing wrong? David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[LonnieJohnson]

Where is [theClaim] coming from? If it is not part of the query, it is acting like a prompting parameter. If it is a field on the form, your SQL should be...

"SELECT * from qryX WHERE [qryX].[claimNo]= " _
& theClaim

If [theClaim] a text field then you will need to include apostrophes...

"SELECT * from qryX WHERE [qryX].[claimNo]= '" _
& theClaim & "'"

I hope this is what you are looking for.

ljprodev@yahoo.com
ProDev
MS Access Applications

 

[CosmoKramer]

Dasher,

If I'm reading this correctly, you are being prompted for a claim because it is a parameter in qry1Claim and that is your form's record source. Each time the query runs, it will ask for that parameter. Try changing theClaim in qry1Claim to be the field on your form.

 

[Soundsmith]

Thanks, guys. The answer I was looking for is
SELECT * from qryX WHERE [qryX].[claimNo]= " _
& theClaim

I never remember that! X-)

David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com