ComboBoxes

Question

Hello, I am new to using Microsoft Access.

I am having trouble with ComboBoxes.

I have a situation where i want to know the State and City (and Department) for every order.

So, say order #1 is in Fairbanks, AK and is the men's clothing department.

I have a table that works linking the state and city (and, on the order form, the dropdown when choosing AK does limit my cities to Alaskan). However, when i move on to a new record and either go back to the Form or check the Table, neither the State nor the City is updated.

I have not yet tried to get the Department to work; i want to get this one done first. I am just not sure why it is not saving in the Table (the boxes are all unbound if that makes a difference).

There might be other pertinent details that i just don't know enough to put into this question so please ask.

Thank you.

Answer 1

@Work together122
If you hang out here a bit longer than 4 days, you will notice people in this forum are quite capable of answering a question about comboboxes.

Answer 2

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the third form in the section on 'entering data via a form/subforms' is a simple contacts form and subform. In the parent form the country, region and city for the contact are selected in three correlated combo boxes, whose RowSource queries are progressively restricted in the latter two as selections are made in the first two. The demo also illustrates how to add new values to the lists for each combo box by typing the new name into the control, which executes code in the control's NotInList event procedure.

An important point to note is that only the lowest level in the hierarchy, city in this case, is stored in a column in the table. The region and state combo boxes are unbound, with their values in existing records being inserted by code in the form's module. To include region and country columns in the table would introduce redundancy as these are determined by city, and the table would not be normalized to Third Normal Form (3NF) with the consequent risk of update anomalies.

The next form in the demo illustrates how the same is done in a continuous form. In this case it is necessary to use hybrid controls in which a text box is carefully superimposed on a combo box to give the appearance of a single combo box control. The form is based on a query which includes the referenced Cities, Regions and Countries tables. The superimposed text boxes are bound to text column in these tables.

If you do download my demo please feel free to post any follow-up questions to this thread.

Answer 3

Can you please arrange the relevant tables on the Relationships diagram and post a screenshot?

Working unbound is probably not the best idea. Especially as a rookie you need to take advantage of what Access has to offer out of the box (which is substantial).

Maybe it would help to review the new Northwind Starter edition. I was a member of the team that put it together. It can be a great learning experience for a beginner to read the documentation and view the forms in design view so you can figure out how to do it.

Neither the Starter or Dev edition have unbound data entry forms; just to give you an indication of the power of bound forms.

Answer 4

You may try posting your concern in following community: https://learn.microsoft.com/en-us/office/vba/articles/feedback-support