Using forms to set query criteria 2

[wmachin]

Hi,
I've set up a form (equipment_list) that pulls it's data from a query. I need to be able to specify the criteria for this query from a field on a number of different forms. I had it working by using the expression builder to set the value for the query criteria [form_name]![field_name]. The problem is that this was specific to the form I'd specified in the expression builder. I tried using the [me]![field_name] in the query but it wouldn't work - the form would ask for the Parameter Value for [me]![field_name]. The field_name will remain the same on all the different forms but I need the query criteria to refernce the active/open form. I hope this makes sense, I'd be more than happy to clarify anything as I'm quite desperate to sort this problem out. Thanks, in advance, for any suggestions.

 

[Crowley16]

hmm, I'm not sure if this'll work in a query, but you could try:

[screen]![active]![form]![control]

or something to that effect...

if that doesn't work, then you would have to get into some coding...

 

[RoyVidar]

Two approaches that might do:

1 - in some relevant event of the form you're using, dump the value of the control in question to a public variable, and retrieve that in the query

2 - if there is only one form open, you could try using the forms collection, and for the first open form, retrieve the value from the control in question

For both approaches, you would need a public function to retrieve the value:

#1
[tt]Public Funtion GetMyGlobal() as <your data type>
GetMyGlobal = gMyGlobal
End Function[/tt]

Call it with [tt]GetMyGlobal()[/tt] in the criteria row in the query

#2
[tt]Public Function GetMyValue() as <your data type>
dim frm as Form
for each frm in Application.Forms
GetMyValue = Forms(frm.name)("Field_name").Value
exit for
next frm
End Function[/tt]

- this is very simplified, and would probably need some errorchecking... for instance, if there might be more than one form open, is there some characteristics with the form that you could do to address the correct form? (.Tag property?, part of the form name?...)

Roy-Vidar

 

[wmachin]

Crowley16 - I tried your suggestion but unfortunately it produced an empty query.

RoyVidar - You'll have to forgive me because I'm a complete novice when it comes to playing with code. Where do I enter these functions? Using logic, I think that approach 1 is what I'm looking for but I have no idea as to where this Public Function needs to be set. If you could elaborate a bit for me it would be much appreciated.

Thanks for your ideas.

 

[RoyVidar]

The function, and the public variable needs to be located in a general module (not a forms/reports module, but in VBE - Insert | Module). Declaration of the public variable (on top of the module) would perhaps look something like this:

[tt]Public gMyGlobal as <your data type>[/tt]

Which event to use to populate it, would depend on your setup, but use an event where you're sure the control is populated with correct value (forms on current, before update, controls after update...?).

Roy-Vidar

 

[wmachin]

I'm afraid I'm not getting this!! I'm don't know how to populate this public variable?!?
I need to send the value of a combo box on a form to a Public Variable and then pick it up again using a form that pulls it's data from a query.

I've created the module using the following code

Public Function GetMyGlobal() As RecordSet
GetMyGlobal = gMyGlobal

End Function

You really are having to lead the blind with this one!!

I've set up the form (type_room) with the combo box (equip_type). How do I tell the form (type_room) to send the value of (equip_type) to the public variable? I ideally want this to happen using a command button.

Thanks again for your help

 

[RoyVidar]

Then in your command buttons on click event procedure, enter:

[tt]gMyGlobal = forms![form_name]![field_name][/tt]

But again, I'd look into the controls after update event, and perhaps also the on current event of the form (use both) in stead. Then the correct value at any time should be assigned to the global, without having to click a button.

You can't filter a query with a recordset, I would have guessed string, numeric (long?) or date are more suitable as datatype (must match the fields DataType (bound column of the combo).

Roy-Vidar

 

[wmachin]

I changed the Public Function to:

Public Function GetMyGlobal() As numeric

GetMyGlobal = gMyGlobal

End Function


I changed my combo boxes to run
gMyGlobal = Forms!type_room!equip_type
After Update and it appears to work (i.e. no error message).

I can't seem to retreive the data using the query.

I added the gMyGlobal() in the criteria row but whenever the query is run I get the message

Undefined Function 'gMyGlobal' in expression

Does this mean there is no data being held in the function? The field that is being queried is a numeric field - is my Public Function ok for this as 'numeric' doesn't seem to be a valid choice?

 

[RoyVidar]

Does this mean there is no data being held in the function?" - functions don't hold data, but can be used to return information. The "data" in this case would (should) be held by the public variable.

"Undefined Function 'gMyGlobal' in expression" - don't know. Might be that the function is not placed in a general module as stated above (second reply).

The datatype "numeric" doesn't exist (based on you saying this was a combo, I suggested Long (look in the parantheses above) but you haven't really provided any information, which leaves guessing)

Roy-Vidar

 

[wmachin]

I've been fighting this problem all night but I just can't make any progress!!

Here's some screen shots of what I've set up.

General Module named Module3

cont in next post...

 

[wmachin]

In a form named testt i've created a combo box (equip_type) that runs the following code on AfterUpdate

cont. in next post...

 

[wmachin]

In the query (test) that retreives the data from the Public Variable I've entered the following criteria:

Yet when I run this query, or open a form that uses the data from this query, I receive the message

"Undefined Function 'gMyGlobal' in expression"

Any ideas?

Cheers

 

[PHV]

The function name is GetMyGlobal, not gMyGlobal.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wmachin]

Ok, I changed the gMyGlobal to read GetMyGlobal in the query and After Update.
The query will now open up without any errors. But, I get an error message when I try to send data to the Public Variable using the After Update event procedure on the form - testt:

As you may have gathered, I'm an absolute newcomer to writing code. My apologies and thanks for any help.

 

[RoyVidar]

As above, assign the form controls value to the global, here you're assigning it to the function

[tt]gMyGlobal = forms!testt!equip_type[/tt]

In the query you use the function: [tt]GetMyGlobal()[/tt]

The declaration of the public, I can't see it, I'd say the top of the module should probably look like this:

[tt]Option Compare Database
Option Explicit
Public gMyGlobal as Integer

Public Function GetMyGlobal() As Integer
...[/tt]

The "Option Explicit" requires variable declaration, and is generally recommended (also in Tools | Options, check the "Require Variable Declaration" check box, to enforce this in all new modules.

Also try a Debug | Compile to see if it compiles (post any errormessages)

Roy-Vidar

 

[wmachin]

Genius!!! It's working!!!
Cheers for all your help.
What's more, I think I actually understand what's happening too.

Much appreciated

 

[wmachin]

Sorry, it's me again. I have another question regarding this issue.
Is there a way of clearing the data that is being held in the Global variable? Idealy, I need the data to be cleared when a form is closed.
Thanks in advance

 

[RoyVidar]

Thanx!

You can set it to 0 in the forms on close event.

[tt]gMyGlobal=0[/tt]

Integers can't be set to Null, but only a value, so for instance 0 or some other value that'l fit the challenge.

But, I'd rather, as stated before, use the On Current event of each form, in addition to the after update event of each control to assign the text control value to the public. This would ensure the public always contains the correct value. If it might be a problem that the text control doesn't contain any value, which would then provide an error, usage of the NZ function might perhaps avoid such error:

[tt]gMyGlobal = NZ(forms!testt!equip_type, 0)[/tt]

or just

[tt]gMyGlobal = NZ(Me!equip_type, 0)[/tt]

which should be enough when referencing a control on the current form.

Roy-Vidar