Combo Box for Data Entry

[Bill Phillips]

I have a form that I use for both data entry, editing data and finding
records. In order to use the form to find records I set my recordsource to
the table I am editing.(For the record the table is named tblFactory)

I have a separate table with Country names, It has both a 2 character
country code and a longer contry description. This combo box is based on
tblCountry. This allows me to have consisten data entry. When looking up
data, I display the country information from tblFactory and hide the combo
box:

Code snippet: Me.FactCountry.Visible = True
Me.cboCountry.Visible = False

When I click the add new record button I reverse the code and get the
desired effect. My Add New Record is simply the code from the Add New Record
wizard from Access. I can fill in the form, but when I complete the form and
try to add it to tblFactory, I cannot get the Country to populate. I have
tried an AfterUpdate event on cboCountry:
Me.FactCountry = Me.cboCountry.Column(0)

But it doesn't work. How can I use a combo based on on a separate table to
populate the field I need in this scenario?

Thanks in advance.

 

[Beetle]

First, your tblFactory should only be storing the CountryCode (or
whatever is the PK of your Country table). The combo box on your
form should have CountryCode (from tblFactory) as the Control Source.
The Row Source of this combo box would be a query of the Countries
table. So, the properties of this combo box would look like;

Control Source: CountryCode (from tblFactory)
Row Source: Select CountryCode, CountryDescription From tblCountries
Order By CountryDescription
Bound Column: 1
Column Count: 2
Column Widths: 0", 2" (or whatever width works best)

The combo box would then retrieve the CountryCode and CountryDescription
from tblCountries and store the CountryCode value in tblFactory. Only
the CountryDescription would be displayed in the combo box because
the first column is hidden (0").

The above assumes that you are trying to store a value in the record and
not just wanting to search for a record based on a value.

I'm not really sure what you're doing with the code to hide cboCountry
and display FactCountry, so I can't really comment on that.