One way:
To work with any form, any field, you'll have to code something into the
AfterUpdate event of each field, like:
if not isnull(me.FieldName) then call SearchDB(Me.FieldName, dataTypePassed)
(SearchDB is a function that updates the SQL of your query with the value
passed to it
Me.FieldName is the name of the field on the form
dataTypePassed is "number" or "text" or "date" )
Public Sub SearchDB(queryVal as Variant, dataTypePassed as String)
Set qItem = db.QueryDefs("query name")
if dataTypePassed = "number" then
qItem.SQL = "SELECT * FROM TABLE WHERE FIELD = " & queryVal & ";"
elseif dateTypePassed = "text" then
qItem.SQL = "SELECT * FROM TABLE WHERE FIELD = '" & queryVal & "';"
elseif dateTypePassed = "date" then
qItem.SQL = "SELECT * FROM TABLE WHERE FIELD = #" & queryVal & "#;"
endif
qItem.Close
db.QueryDefs.Refresh
End Sub
"Laertes" <bi******@btopenworld.com> wrote in message
news:b2**************************@posting.google.c om...
Hi gurus!
I want to setup a query that uses data from a field from the currently
open form. Is there a way to do that? i.e. I don't want the query to
be form specific, but to work with any form, as long as it is the
current form. I tried to use "Me" in the query, but it didn't work...
Also even though there is a currentDB command there is no CurrentForm
:(
any ideas?
thank you in advance
Laertes