VBA Code for Access

[Guest]

I am new to Access so I hope someone can help me.

I have form where a field called CountryID is a foregin key in my main
table. On the form the CountryID is a combo box whee you can choose the
field Country and it is bound to the field CountryID.

My problem is this for every record in my main table I must populate the
field CountryID. However, on the form I only want to ask my users to do this
once the first time they open the form. I then want that field populated
automatically until the table is complete. How do I do this?

 

[Guest]

My problem is this for every record in my main table I must populate the
field CountryID. However, on the form I only want to ask my users to do this
once the first time they open the form. I then want that field populated
automatically until the table is complete. How do I do this?

Add a variable to the script, and a handler on the combo's afterUpdate. When
the user selects something the afterUpdate will fire, at which point you
write down what they selected.

Now add handlers to all the "movement" and "new record" handlers. In these
check to see if the variable is non-null. If so, simply copy the value from
the variable back into the combo. Events would include _current.

Maury

 

[Guest]

Rename the CountryID combo box as CountryIDSet. Make it unbound. Create a
hidden text box called CountryID and bind this to your CountryID field. Then,
set the DefaultValue of CountryID to
[Forms]![<CurrentFormName>]![CountryIDSet].

Add this code:

Form_Open
CountryIDSet.Enabled = True
'disable all other controls here to prevent entry while CountryIDSet is
blank
End Sub

CountryIDSet_AfterUpdate
CountryID.requery 'sets the DefaultValue for the first, current record
when CountryIDSet is selected
CountryIDSet.Enabled = False
'enable all other controls here to allow entry once a country has been
picked
End Sub

This allows the user to pick the country but not change it until finished.
It will remain until the user closes the form and will be reset to enabled
when the form is closed/reopened. If you don't care if they change it, leave
out the Form_Open code & CountryIDSet.Enabled = False above. Or, you could
make a NewCountry button that would clear the box and allow the user to start
a new country without closing & reopening the form.

There are many ways to ensure that the user picks a country before entering
anything else on the form. The method above simply disables each other
control when the form is opened until they pick a country.