Variable into query

[Guest]

I have a form that asks the user for 4 inputs. The user then hits OK which
does two things.

1. It takes the user inputs (txtName, txtManager, txtAddress, txtCode) and
puts them in a function called excelExport()

2. It runs the excelExport() function

In my excelExport function I change set all of the variables = varName,
varManager, varAddress, & varCode....respectively.

The first three variables I use within the excelExport function for misc.
things. The last variable (varCode) is the important one. Within my function
I call 24 queries (all different queries with one common field CODE). I want
to pass the varCode variable into the CODE field as the criteria for every
query. (I am not calling putting the actual SQL in my function, I am simply
calling the regular query)

Is there a way to get input from a form, pass it into a function and then
pass the variable into a query? I tried putting the following into the query
criteria:

excelExport(<<varCode>>) This does not work.

Help PLease

 

[Ted Allen]

Hi James,

There are different ways you could handle this. Perhaps
one of the easiest would be to list the form field
directly in the query criteria, such as:

Forms!YourFormName!YourControlName

Another way would be to define a parameter in each of the
queries. Then, in your code, you could attach to the
query and set the parameter = to the variable. Something
like:

Dim db as DAO.Database
Dim qdf as DAO.QueryDef
Dim....

Set db = CurrentDB
Set qdf = db.QueryDefs("YourQueryName")
qdf.parameters("YourParamName") = YourVariableName
.....

Another way of approaching it would be to read the
qdf.sql and then revise it to add a WHERE condition and
then use that revised sql for a temp query.

Others may have other ideas, but those where the ones
that came to mind.

-Ted Allen

 

[Guest]

Ted,

First thanks for the quick response. I tried using the first way

Forms!Form Name!Control Name. It did not work. However, looking at the
properties of my text field that I am trying to copy into it,

The Name is set to txtDestCode
Control Source = nothing....Is this my problem. How do I set the Control
Source, if I Type in txtDestCode then the form doesn't open properly?

 

[Guest]

Hi James,

Sorry for the long delay in responding, yesterday was a holiday for us so
this is my first day back (hopefully you'll still check back).

Your control does not need to have a control source to be used in the query.
The control source links the control to an underlying table or query to
store the data that is entered in it. That is independent of referencing the
control value in a query.

Does your form name have a space in it by any chance, if so, the name would
have to be enclosed in brackets, such as Forms![Your Form
Name]!YourControlName

Of course the control name would also need brackets if it has a space, but
it doesn't look like it has one from your post.

If you are still getting an error, post the sql of your query and I'll see
if I can spot anything.

-Ted Allen