First off, you need to be a little patient. Posting the same message three
times in under 30 minutes is a little extreme.
Another technique, similar to the one that John Vinson recommends is to have
a function that actually stores the value that you want to test for in your
query. That way, no matter what form you are on, you can use the forms
current event to save the value to the function, and then call the function
in your query. The function might look something like:
Public Function fnFieldValue(Optional SomeValue as Variant = NULL) as Variant
Static myFieldValue as Variant
if isnull(SomeValue) = false then
myFieldValue = SomeValue
elseif isempty(myFieldValue) then
myFieldValue = NULL
endif
fnFieldValue = myFieldValue
End function
Because this function uses a static variable, it will retain it's value
between function calls. So, if you use a forms current event to set the
value, something like:
Private Sub Form_Current
Call fnFieldValue(me.txtID)
End Sub
You can then use the function(without a parameter) in a query; something like:
SELECT * FROM SomeTable
WHERE ID = fnFieldValue()
Another method is to have a form in your application that you open when the
application starts, but which you keep hidden. With this form always open,
you can add as many unbound text boxes as you want as place holders for
values you want to refer to over and over again throughout the application.
Then you can use the forms Current event or some other event to set the
values of these textboxes, and refer to them in your query.
HTH
Dale
If the form for my criteria is closed how do I write the criteria to look for
the same field name in another form that is open? I placed the 1st option in
[quoted text clipped - 3 lines]
