Filter a form field based on the value of another in that form

[kitandy]

Hi
I have a database recording competition entry's and results.
The competition has a range of numbered classes each of which is awarded first , second and third. Monetary prizes are awarded but values vary depending on class.
I have created

A table (classes)
A table (Prizes) which records the prize values for each of the 200 classes with the "Class" field on a lookup from the classes table
​

Now to the results
My Results 1 table contains the fields

Results 1 ID
Class - on a lookup from the classes table
Date logged
​

My Results 2 table contains the fields

Results 2 ID
Results 1 ID
Class
Position - on a lookup which included place and prize (storing the place) from the Prizes table
Entrant - on a lookup from the contacts table
Prize
​

I have created a query that looks up all the fields in Results 2 and used this to create a sub form Results 2
I have created a Form (enter results) from table results1 and dragged the sub form onto it. I the set both the Link master fields and link Child field to "Class.
When I enter a class in the main form it updates the class field in the subform but does not filter the place options to this class only.

Any assistance would be much appreciated

 

[arnelgp]

is it possible for you do upload your db?

 

[kitandy]

yes
please give advice re how to upload

 

[arnelgp]

click PostReply button, then on the message click the paperclip icon beside the happy face icon to upload your db, if your db size is more than 2mb, try compressing it using Zip utility, upload the zip file.

 

[kitandy]

DB attached as requested

 

[arnelgp]

hello again, which button on your menu will i work on.

 

[kitandy]

Hi
Its the"Enter results" button

 

[arnelgp]

please try

 

[kitandy]

Many thanks thats great except when a position is selected it stores the class number rather than the position in the "Position" field.see attached image
Also could I be cheeky and ask if you could arrange for the value of the prize to be auto entered in the prize field when the position is selected
Thanks in anticipation

 

[arnelgp]

sorry about that, here try again.

 

[kitandy]

Thanks that's the position sorted
However the prize value in this case should be £1.50, £1.00 and £0.50 respectively for first, second and 3rd.
As you will see from the screen shot this is not the case.
I am very appreciative of your assistance and patience

 

[arnelgp]

apology, what am i thinking? please try it again.

 

[kitandy]

Apologies for being a pain but we need to record the entrant number and select it by entering their number rather than the name as shown

 

[arnelgp]

dont you like names to be displayed (make your info more meaningful) instead of numbers, you can investigate your table T_result2, where you are saving your subform. see that it does not save the name but instead numbers. dont worry about the names on your entrant field its just there for display.

 

[kitandy]

Yes I agree seeing the name displayed is good.
However the person entering the data receives it in the format

Class Number

Position
​

Entrants Number (without their name)
​

 

[arnelgp]

here i included the entrant number.

 

[kitandy]

Hi Thats great.
Is it possible to also select the entrant by inputting their number

 

[arnelgp]

yes, ok try entering number on the entrant control.

 

[kitandy]

Thats great
Just a question now. While it all appears to be working fine why does the "position" field store the number 55 in the table regardless of the value (1, 2 or3) entered in the form. Also how will this display when I start building reports

 

[arnelgp]

try again pls. i made a sample report (report1), see the recordsource.