? Automatically setting a value based on value in another field 
Author Message
 ? Automatically setting a value based on value in another field

Hi...

I'm creating a database to manage data from an inter-school track meet.
Competitors are assigned points (for the 'team' competition) based on their
placement in an event.

I want to be able to put the placement in the Placings control (drop-box or
manually, I don't care), and then have the Points field automatically set
the value based on the Placings. 'IIf' works find for two values, but...

I've created a table (and a form) called Placing/Points which shows which
points go with which placings -- and then have both the placings and the
points be entered into the table in the underlying query of the form.

1st place = 7 points, 2=5, 3=4, 4=3, 5=2, >5=1, dns (did not show)=0, dnf
(did not finish)=0

I've been playing with this for hours now -- tried a sub-form thing, and it
was close, but no cigar.

The form I'm using to input the placings is based on a query that will pull
up all the competitors in a particular event so I can enter placings and
points and then print out a results report.

Am I missing something simple, or still too much of a novice to realize I
need to use code or a macro or ...

Any help would be greatly appreciated!

Lee
--
 "It isn't that they can't see the solution. It is that
they can't see the problem."...G. K. Chesterton...



Thu, 13 Nov 2003 12:09:14 GMT  
 ? Automatically setting a value based on value in another field
Using two controls (txtPlacings and txtPoints) on a form - with
the control txtPoints LOCKED. (there are many ways to do this,
such as "Select Case", but I will will use a simple If..Then:
======================
Private Sub txtPlacings_AfterUpdate()
If Not IsNull(Me![txtPlacings]) and me![txtPlacings] <> "" Then
    If Me![txtPlacings] = 1 Then
        Me![txtPoints] = 7
    End If
    If Me![txtPlacings] = 2 Then
        Me![txtPoints] = 5
    End If
    If Me![txtPlacings] = 3 Then
        Me![txtPoints] = 4
    End If
    If Me![txtPlacings] = 4 Then
        Me![txtPoints] = 3
    End If
     If Me![txtPlacings] = 5 Then
        Me![txtPoints] = 2
    End If
     If Me![txtPlacings] > 5 Then
        Me![txtPoints] = 1
    End If
     If Me![txtPlacings] = "DNS" Then
        Me![txtPoints] = 0
    End If
     If Me![txtPlacings] = "DNF" Then
        Me![txtPoints] = 0
    End If
'
End Sub
=========================
BUT there is another way that is simpler and which I prefer because so
little code is used. Create a combo box (called "cboPlacings") for Placings
on the form using the Wizard with TWO columns. In the first column you
want ALL possible placings, plus DNS and DNF. In the second column
you want the corresponding Points, so:
Placings  |   Points
1                        7
2                        5
3                        4
4                        3
5                        2
Quote:
>5                      1

DNS                  0
DNF                  0

You then HIDE the Points column in cboPlacings by changing its
width to zero (0) - so only the placings will appear.  (The Widths should
look someting like 1";0"; and make sure you allow for 2 columns in
cboPlacings) Now the following is true of cboPlacings:
    Me![cboPlacings].Column(0)   equals the value of Placings entered
and
    Me![cboPlacings].Column(1)   equals the value of Points corresponding
                to that of the Placings entered as per the listing above

So you use the following code for the cboPlacings AfterUpdate() event:
Private Sub cboPlacings_AfterUpdate()
    If Not IsNull(Me![cboPlacings]) then
        Me![txtPoints] =  Me![cboPlacings].Column(1)
    End If
End Sub
========
---Phil Szlyk


Quote:
> Hi...

> I'm creating a database to manage data from an inter-school track meet.
> Competitors are assigned points (for the 'team' competition) based on
their
> placement in an event.

> I want to be able to put the placement in the Placings control (drop-box
or
> manually, I don't care), and then have the Points field automatically set
> the value based on the Placings. 'IIf' works find for two values, but...

> I've created a table (and a form) called Placing/Points which shows which
> points go with which placings -- and then have both the placings and the
> points be entered into the table in the underlying query of the form.

> 1st place = 7 points, 2=5, 3=4, 4=3, 5=2, >5=1, dns (did not show)=0, dnf
> (did not finish)=0

> I've been playing with this for hours now -- tried a sub-form thing, and
it
> was close, but no cigar.

> The form I'm using to input the placings is based on a query that will
pull
> up all the competitors in a particular event so I can enter placings and
> points and then print out a results report.

> Am I missing something simple, or still too much of a novice to realize I
> need to use code or a macro or ...

> Any help would be greatly appreciated!

> Lee
> --
>  "It isn't that they can't see the solution. It is that
> they can't see the problem."...G. K. Chesterton...



Thu, 13 Nov 2003 12:51:31 GMT  
 ? Automatically setting a value based on value in another field
Phil...

Thanks! I'll try it out and see how it goes!

Lee

--
 "It isn't that they can't see the solution. It is that
they can't see the problem."...G. K. Chesterton...



Thu, 13 Nov 2003 14:01:39 GMT  
 ? Automatically setting a value based on value in another field
It worked just fine. I had to play around with control names a wee bit, but
nothing serious. Thanks again soooo much!

Lee

--
 "It isn't that they can't see the solution. It is that
they can't see the problem."...G. K. Chesterton...



Fri, 14 Nov 2003 10:05:24 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Automatically Set Checkbox value based on a combo box value

2. Set value of text box based upon value in another field

3. Field won't populate automatically based on the value of another field

4. automatically putting a value into a field based on data from 2 other fields

5. Auto increment new field value based on yes/no field value

6. value in field based on another field's value

7. Basing value of one field on value of another field

8. Calculating a field value based on value in another field when inserting record

9. Query Help: Return Highest Values in One Field Based on Values in Two Other Fields

10. Delete Field's Value Based on Another Field's Value


 
Powered by phpBB® Forum Software © phpBB Group