MS Access parameter query from form


I need to enter data in a form and then run a query which uses some of the data I have entered , the result of which is stored in the form.

The logical process I think may be

1. Open form
2. Enter data
3. Click button on form passing this record's data to a parameter query
4. Save query result to this record via the form.

The first thing I need to work out is how to pass the data to the parameter query.

Any ideas?

Thanks in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Billy RothTech Team VolunteerCommented:
Just to cover all the bases, maybe embedding a simple expression would be enough to solve your problem?
Billy RothTech Team VolunteerCommented:
Also, you may be able to use SetValue in macro.  This will require you to click "Show All Actions" button at the top of the Macro Design tool.  Also when using set value, you must completely be explicit when defining, such as [Forms]![Customer]![CustomerID]
This is a little busy... but once you've learned how to do this.. you'll be doing it all the time.

In new module:

public glbYourVariable_1 as string
public glbYourVariable_2 as long

and so on.

Now, create a function to "get" these variables:

Function get_glbYourVariable_1() as string
get_glbYourVariable_1 = glbYourVariable_1
End Function

Function get_glbYourVariable_2() as long
get_glbYourVariable_2 = glbYourVariable_2
End Function

Create a query and use this/these functions as criteria:


In your form, or any other code - simply assign a value to your public variable and run the query.

These "get_" functions also work for setting default values in text boxes, and such.  They can be used virtually everywhere.

I design entire systems using this method of control.  Just remember to clear your variables when you're finished with them.

Scott C

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
What clarkscott posted is what will work in all situations.
It will work in Crosstab and Group By queries where referencing controls can be troublesome.
Using functions allows you to set datatypes directly.
These global variables can be used in any other part of your application

You can use the shorter method by referencing the controls themselves in the query...
In the criteria section of the EmpID field, (for example)  you can do something like this:

The good thing here is that you can use the "Expression Builder" so simply navigate to, and select the controls/objects, and build this criteria.

<4. Save query result to this record via the form.>
Not sure what this means, or why it is needed, ...but lets get the parameter query going first.


ronnie10165Author Commented:
Thanks but can we rewind a bit. I know ASP and SQL very well but haven't really touched VBA and Access. I have spent a couple of days reading up but am finding it a bit difficult getting over the first few fences.

I have attached a sample of the project I am being paid to do. In essence it is to store parts in a warehouse in bins in the most efficient way. It's massively complicated but if I can get the sample right then I can apply that logic to many situations.

If you look at the simple example in the database I have a part table with name, dimensions and calculated volume. I have a bin table which stores the volume (empty) of each bin.

There is also a query which adds up the allocated dimensions of parts per bin.

So I need to see if a part can fit in a bin and I need to allocate it to the right bin on the part form.

Am I right in saying that I need to do the following
1. Write a function to get the partvolume variable
2. Write a function to run the query and store the results as an array
3. Do the maths to subtract the partvolume from what's left in the bin
4. Give the operator the choice of bin to select
5. Write it to the form
6. Kill the variable.

If so then the difficulty is how to do all that.
1. How do I set the partvolume variable from the form?
2. Do I need to open a form from a button to do all this?
3. Am I populating some sort of message box with a drop-down?

Thanks in advance
Jeffrey CoachmanMIS LiasonCommented:
Please know that this is a bit more that a single "question"...

Is your main question here how to see if the part will fit in a bin?
If so, I thought I answered this question previously?

There are a few odd things in this database..
1. You are storing the Part volume, instead of calculating it...

2. Your database does not have a Parts/Bins many-to-many table.
So it is not clear how you are knowing what parts are already in the bin...?

3. I am not sure what the "ChooesBin" query is doing...?

The bottom line is you need a query or function to what I stated in your previous question (to see if the part will fit):
SELECT BinVolume, TotalVolumeOfAllParstInTheBin, VolumeOf1Part,IIF(BinVolume-TotalVolumeOfAllParstInTheBin>=VolumeOf1Part,"Part Will Fit", "Part Will Not Fit") As PartFit
FROM YourTableOrQueryName

So in this regard you first need to get the basic design here straightened out. Then create a system to select a part and see if it will fit in a bin.
The system/(form) will:
Allow you to select a part.
Then select the bin
Then click a button/run a query/run a function to see if the part will fit.
If the part will fit, ...assign the part to the bin.
If the part will not fit, then alert the user...

ronnie10165Author Commented:
Sorry for the delay, been leaning this stuff.

I have managed to do everything so far except set and kill the variable from the form.
Do i have them as functions in a module? If so how do I call them?

Jeffrey CoachmanMIS LiasonCommented:
Not a sample of your database following these guidelines...

Sample database notes:

1. Back up your database(s).
2. If the database is split, combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any extraneous records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the Compact/Repair utility.
12. Remove any Passwords, Security and/or login prompts.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
Post the explicit steps to see the issue.
And if applicable, also include a clear, graphical representation of the *Exact* results you are expecting, based on the sample data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.