Query Returns Applicable Price Based on User Criteria (1 Viewer)

Local time
Today, 23:51
Joined
Feb 27, 2022
Messages
49
Hello everyone,

I haven't really used Access much in over 10 years. I used to use some of it's basic functionality (minus programming) for years. But now I'm super rusty and have been mulling over this for over 2 days and still blank, so thought I might ask for some input please. :)

So it's clearer, I thought I would attach a snippet of the table.

I'm wanting to return the applicable ' Base Price' based on
1) the selected 'Product Type' (either Standard or Non Standard AND
2) SizeTier (Small or Large) AND
3) Weight AND
4) Length AND
5) Width AND
6) Depth

Right now I've split the Weight and dimensions into Max and Min- I could change that if necessary. I did that thinking about a big IIF statement at the back of my mind!

This query will form part of a form where I'm wanting a user to enter to select the Product Type, SizeTier and then enter the weight and dimensions and have the form pick and return the respective price for that item.

For example, let's say I selected 'Standard', 'Large' and entered '14' for weight, '17' for length, '14' width and '7' for depth, I want the form to show $4.52 in the price field.

I know the query will need to refer to some of the form attributes (Product Type, SizeTier, weight and dimensions), but I have to admit that besides referencing the form where applicable, I'm blank in remembering how to actually query the rest so that it returns the respective Price.

Can someone please enlighten me?

Thank you
 

Attachments

  • SamplePriceSizing.zip
    7.9 KB · Views: 274

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:21
Joined
May 7, 2009
Messages
19,169
here is a demo that will filter the table according to your entry.
the same code/principle can be used on any form.
 

Attachments

  • boxSize.accdb
    1.1 MB · Views: 258
Local time
Today, 23:51
Joined
Feb 27, 2022
Messages
49
Hi arnelgp,

Thank you for investing so much time and taking the trouble to creating a demo database that would show and teach me how it works- I'm blown away! I will study your code and try and learn the concepts from it. That's awesome.

Thanks again bud.
 

SHANEMAC51

Active member
Local time
Today, 16:21
Joined
Jan 28, 2022
Messages
310
Can someone please enlighten me?
I always use a ribbon form
1 -the headers are fully visible
2 -green search fields strictly above the data fields
3 -you can set the selection conditions in any combination
4 -I do not let the user spoil something (yellow fields are closed from correction)
5 -when clicking on the selected line, a link to the selected entry of the subordinate form is returned, and therefore to the price
 

Attachments

  • Screenshot_3.png
    Screenshot_3.png
    33.5 KB · Views: 257

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:21
Joined
May 7, 2009
Messages
19,169
here is another demo. if you are recording "transactions" for you customers.
if there are more records with same criteria it will show a pop up form.
otherwise the price is immediately displayed.
 

Attachments

  • boxSize.accdb
    1.4 MB · Views: 295
Local time
Today, 23:51
Joined
Feb 27, 2022
Messages
49
I always use a ribbon form
1 -the headers are fully visible
2 -green search fields strictly above the data fields
3 -you can set the selection conditions in any combination
4 -I do not let the user spoil something (yellow fields are closed from correction)
5 -when clicking on the selected line, a link to the selected entry of the subordinate form is returned, and therefore to the price
Thank you ShaneMac51. That looks like a simple way to do this- I've not dabbled with ribbon forms before. Appreciate your idea.
 
Local time
Today, 23:51
Joined
Feb 27, 2022
Messages
49
here is another demo. if you are recording "transactions" for you customers.
if there are more records with same criteria it will show a pop up form.
otherwise the price is immediately displayed.
This is great. Very slick. I had a quick look at the coding- a bit above my pay grade! But I will try and study it to learn from it. Thank you for your time and this extended effort.
 

Users who are viewing this thread

Top Bottom