ronnie10165
asked on
MS Access run query on form
Hi
I have an Access DB which stores parts in bins. As I enter a part I need to calculate it's volume and select a bin which has enough space to fit it in.
I have a query that uses a calculated field on the parts table to add up individual parts volume and compare that answer to the volume of the bin.
What I can't work out is how to get the current record's calculated field value and check if it will go in a bin's remaining volume.
Thanks
I have an Access DB which stores parts in bins. As I enter a part I need to calculate it's volume and select a bin which has enough space to fit it in.
I have a query that uses a calculated field on the parts table to add up individual parts volume and compare that answer to the volume of the bin.
What I can't work out is how to get the current record's calculated field value and check if it will go in a bin's remaining volume.
Thanks
ASKER
Thanks Jeff I can do the maths but how do I get the query to run on the form?
Not sure if you want the query to run FROM the form (Open in a separate window)
Or if the query is the Recordsource for the form, and you need the result to appear in a field...?
For the query to run separately:
1. Create a button on the form
2. The code on the button onclick event will be:
Docmd.OpenQuery "YourQueryName"
To use the query as a form's recordsource:
1. Test the query to see if it returning the correct values
2. Create a form using the query as the source
If this still is not what you want, then you will have to post a small sample database that demonstrates what you have.
Also post the explicit steps to use your sample db and see the issue.
Post the exact steps you need the user to perform.
Finally post a clear graphical example of the *exact* output you want.
JeffCoachman
Or if the query is the Recordsource for the form, and you need the result to appear in a field...?
For the query to run separately:
1. Create a button on the form
2. The code on the button onclick event will be:
Docmd.OpenQuery "YourQueryName"
To use the query as a form's recordsource:
1. Test the query to see if it returning the correct values
2. Create a form using the query as the source
If this still is not what you want, then you will have to post a small sample database that demonstrates what you have.
Also post the explicit steps to use your sample db and see the issue.
Post the exact steps you need the user to perform.
Finally post a clear graphical example of the *exact* output you want.
JeffCoachman
ASKER
Ok I can see that is what I need, but how do I add the if..else logic. This would be easy for a webpage but I need it i the Access app for my customer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's it, in the query itself..thanks
ok great
;-)
;-)
Part will fit
else
part will not Fit
end if
Example: part Volume=3
6 parts=18Vol
bin Vol=21
So, ...
21vol-18vol=3vol, part will fit
21vol-12vol=4vol, Part will fit
21vol-20vol=1vol, Part will not fit
So as long as the remaining vol is greater that the Vol of 1 part, the part will fit.
There may be more to this, but that should be it basically...
JeffCoachman