Default Value from Other Table?

[Egad]

Is there any way I can have a default value in a table set to the value in a field in another table?

I have a table that has event information including the number of the event, e.g., this year is the 51st one. This event information table only has one line because after the event I'm going to save the record to an archive table and then update the information for the next year.

I have another table with participants in the event and records for it come from a form. I'd like to have it so that it automatically inserts the event number for each record. In this way when I archive that information, we'll know which event the participant was involved in. The participant could be involved for a number of years.

I'd rather not have the input person have to type in the event number every time. Doing a combo box also involves an extra step for them. It seems like I ought to be able to automate this info. I don't know anything about VBA.

Would one of you help me? By the way, I've learned an awful lot reading on this forum. I can't tell you how many problems you've solved for me. Thank you.

 

[Pat Hartman]

Create a new form with a single control that will hold the default value for EventNum. Bind the form to the event table. Have this form open when the db opens and then immediately hide itself and open your normal startup form. Whenever you need the default value for Event, get it from this hidden form.

Me.EventNum = Forms!YourHiddenForm!EventNum

I recommend a hidden form rather than a global variable because there are some conditions under which a global variable will loose its value. The global variable would also take some code to implement whereas the hidden form method simply requires a single statemtent in the form's Current event:

Me.Visible = False

 

[Egad]

Thanks, Pat. I'll give this a try.