Referencing "Current" from in query

[wstobbs]

Hi guys, I think that I am being dumb but here goes.

I need the query associated with a Combo Box row-source to filter records based on another field on the form.

I can get this to work just fine if I explicitally include the form name in the query, ie SELECT Client.Client FROM Company INNER JOIN Client ON Company.Company = Client.Company WHERE (((Company.Company)=[forms]![allcalls]![company])); My form name is AllCalls.

However, I will be having many similar forms so do not want to reference the form by name. I am sure that the form can be referenced by me! or something like that but try as I might I cannot get it working.

Am I making sense? and does anyone have a solution?

Cheers

Wayne

 

[Pat Hartman]

No. With SQL, you need to reference a form specifically. An alternative is to use a user defined function. This method is acceptable ONLY if your tables are Access. If they are SQL Server or something else, you'll need a different method.

Create a global variable to hold the search value.
Populate the global variable from your form.
In a standard module, create a public function to return the variable you want.
Use the function in your query rather than the form field.

I have posted more detailled code on this topic if you care to search for it.