Variable in a query

[Alex]

Hi
I have a subform which is based on a query. Is there anyway to include
within the query a public variable, which will be used to restrict the
records selected?

Thanks

 

[Tom Ellison]

Dear Alex:

In order to use the value in your public variable, write a public
function that returns that value and then reference that function in
your query.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Alex]

Dear Tom

Happy New Year, and thanks for coming to my rescue again !!

I understand the first part of your suggestion, but am not sure how I would
reference the function from within a query.

I am wondering if I am trying to make my own life too complicated.
I have a main form linked to two subforms - frmSWPurchases and frmLicences.
With sfrmSWPurchases displayed, the user clicks on a button collects two
variables (Lid and SWID). I then go to the main form and go to the record
based on the variable Lid. I then display the subform frmLicences which has
all the related records to its main form records which is OK.

However in subform frmLicences I need to limit the records displayed to
those where [SWPurchaseId] field in form frmLicences = variable SWID.

I am floundering as to how to do this. Since the variable SWID is not
supplied until after the button is clicked do I have to uswe a filter, once
i have opened the subform? Any help much appreciated

Thanks

Alex

My current code:

Lid = Me.LicenceeId
SWID = Me.SWPurchaseId
Me.Parent.Form.RecordsetClone.FindFirst "[RecordID]=" & Lid
Me.Parent.Form.Bookmark = Me.Parent.Form.RecordsetClone.Bookmark
Forms!frmOrganisations!frmLicences.Visible = True