default values for a field on a table

[Guest]

I have a table with a drop down box which has different hockey leagues in it,
i.e. mite, squirt, bantam, etc. For each league there is a certain price
that I pay officials to officiate the game. When I select one of those
leagues, I would like an automatic pre-determined price to appear in another
collum of the table (example when bantam is selected, it will place $22.00 in
the another column). This way when I run a query, it will post the names of
officials, the games they did and the amount of each game. I will be placing
this on a report and hopefully have a total amount at the bottom (I don't
know how) so I can send them in for payment each month. My ref's would
appreciate any help anyone can give.

 

[tina]

recommend you remove the "drop down box" (called a Lookup field) from your
table. you don't need it (data entry should be done in forms, not tables),
and it causes a number of problems. see
http://www.mvps.org/access/lookupfields.htm for details.

in a form, you can do what you're describing easily enough. let's start with
the table design, to make sure we're more or less on the same page.
presumably you have three tables (involved in this issue), along the lines
of

tblReferees
RefID (primary key)
FirstName
LastName
(other fields that describe a referee.)

tblLeagues
LeagueID (primary key)
LeagueName
LeaguePrice (what you pay one referee for one game, in a specific league)
(other fields that describe a league.)

tblRefAssignments
AssignID (primary key)
RefID (foreign key from tblReferees)
GameDate
LeagueID (foreign key from tblLeagues)
Payment

create a form based on tblRefAssignments. you can do it very quickly by
selecting the table in the database window and clicking the AutoForm button
on the toolbar. in form design view, change the RefID control to a combo box
(Format | Change To | Combo Box, on the menu bar). set the properties as
RowSource: SELECT RefID, LastName & ", " & Firstname As FName FROM
tblReferees ORDER BY LastName, FirstName;
ColumnCount: 2
ColumnWidths: 0"; 1.25"
BoundColumn: 1
ListWidth: 1.5"
LimitToList: Yes

change the LeagueID control to a combo box. set properties as
RowSource: SELECT LeagueID, LeagueName, LeaguePrice FROM tblLeagues ORDER
BY LeagueName;
ColumnCount: 3
ColumnWidths: 0"; 1.25";0"
BoundColumn: 1
ListWidth: 1.5"
LimitToList: Yes

in the AfterUpdate event property, click the droplist and select [Event
Procedure], then click the Build button at the right (...). when the VBA
Editor window opens, your cursor will be at the correct place to add the
following, as

Me!Payment = Me!LeagueID.Column(2)

i used the table and field names from my example tables. your "real" names
are different, of course, so you'll need to substitute the correct names
when following the guidelines above. if your setup is substantially
different, then just use the above to give you ideas on how to get the
result you're after.

hth