Default Value in Forms based on value in another table

[gemadan96]

I have an Access 2007 database to manage tournament registrations. This


database will contain information for many different events, but only one
tournament will be active at any one time. It will be used by others that are
not access knowledgeble, so I need to make it as user friendly as possible.

Among the tables I have are Settings, Tournaments, and Registrations.
Settings identifies the current tournament. Tournaments identifies each
tournament and its various fees. Registrations contains info about each
registration including the tournament and the competitor. Settings and
Registrations each have a lookup field tied to the TID (Autonumber, primary key)
field of Tournaments.

I have a form, Set Current Tournament, that allows the user to set the
tournament in the Settings table.

I have a query, Registrations Extended, which calculates other values based
on the registration.

I have a form called Registration, used to enter the registration
information. That form includes a field (control) for the TID to select the
tournament to register for. I want that field (control) to have a default value
of the Current Tournament in the Settings table. I've tried a dlookup with no
success.

If it were just me using the database I would just change it manually,
however like I said the primary users won't be able to do that without getting
flustered.

Any sugeestions? Maybe a macro or VBA? Just need to know how to do it.

[June7]

Could try code in the form Open event to set the DefaultValue property, something like:

Me.controlname.DefaultValue = DLookup("ID","Tournaments","Active=-1")

[gemadan96]

I tried the following suggestion from another forum and it worked perfectly.

Create a function in a Standard Module (not form,
report, or class module) like this:



Code:

Function GetDefaultTID() As Long GetDefaultTID = Nz(DLookup("YourFieldNameWhichHasTheSetting", "YourSettingsTableHere"), 0)End Function
and set the DEFAULT
VALUE property for the control in the form to

=GetDefaultTID()