Defining Combo Box selections in a Form View (data validation issue) - Access 2016

[Gemini]

Hello,



I have a very simple database in Access 2016, used for collecting and sorting records of vendor's that our company buys equipment from. Currently the database is comprised of only a table and a form view of that table, useful for entering new records but I haven't got much beyond that.

This is a screenshot of the records table:



I set one of the data fields ("Business Model") as a Combo Box so that users will be forced to select one of only a few possible categories for that data point. Entering data in the table works fine like this - see below screenshot.



I'm running into a problem however in the Form design. I defined the data field as a combo box but the form wizard didn't give me the same "prompt" to define the acceptable values for the drop down selection list. I tinkered in the property sheet with data validation rules but can't find the appropriate setting. I am using this form as a new record entry mechanism, so I want the same combo box to apply for the same data field.

Here is a sample form view. You can see the "Business Model" data field in the upper right. I defined that as a combo box, but can't find the place in the form design to define the values for the combo box drop down list.



For the record - the Form works just fine for manually entering new records. It does populate the data table so I believe that the text boxes are correctly "assigned" back to the table. I just can't get the combo box settings the work in the Form design.

Any help is greatly appreciated. I am new to database design but trying to learn. Please forgive newbie question and possible misuse of database terminology.

Cheers,

[orange]

Here is a link to information related to Database Planning and Design.

You should research Normalization.
You will learn if you work through 1 or 2 of the tutorials from RogersAccessLibrary as mentioned in the linked info.

Good luck.

[Gemini]

Thanks for the links and resources. I just started a tutorial on SQL basics and will work through yours next.

While I learn I really need to make some progress on a work project so I hope it's okay if I go ahead and ask some specific questions. I know once I work through all the tutorials I may be able to answer these basic inquiries for myself but I hope that is a forgivable offense for a newbie who is trying to learn

I was able to solve the problem with my combo box on the vendor entry form. See below.



I set the combo box to generate it's list from a new table I created for drop down lists.

Now the problem I am facing is that when I enter a new record through the Form, everything is fine on the form view and it gives the options I want in a drop down list... but if I go back to the table to examine that new record the cell that populated from the combo-box reads as the ID of the source-table that had generated the text of the Form combo box! See example below.



I expect that somewhere I can change that setting so that the cell in the table record populates from the actual text of the drop down combo box instead of the ID of the separate table I created to make the list of combo box values.

Any idea where I could find that setting? I want the table record and the form record to both be populated with the text that I defined in that second table I mentioned.

Thanks in advance.

[orange]

We all have our favorite ways/preferences of learning.
It's always good to have a project so you can see a concept/options and results in your own "context".
You may find this link interesting.

Good luck.

[ArviLaanemets]

You need access to tables when designing the database. The users don't need the access to tables at all - the best practice is hide the Objects Pane from users and display only forms (for yourself too, when you aren't designing something). So having combos in both tables and forms is moot - and sometimes can give you problems.

That in table is business model ID instead of text for new entries is so because you defined the RowSource property of form combo as 2-column query (BusinessModelID, BusinessModelText), and linked the combo to BusinessModelID. A ways to correct the situation:
1. (The best one.) Update the business model field in your table, replacing all texts with proper number, after that change the field type to numeric (and rename the field like BusinessModelID);
2. Change the query in RowSource to single-column one, like "SELECT BusinessModelText FROM tblBusinessModels", the ColumnCount to 1, and the ColumnWidth to "2.5". And then reselect entries with numeric values.

When the list of business models is fixed one, you can manage without table tblBusinessModels too. You simply set combo's RowSourceType property to "Value list", and type the value list into RowSource field:
"Manufacturer, Distributor, ..." for ColumnCount = 1
or
"1, Manufacturer, 2, Distributor, ..." for ColumnCount = 2

When using value list in combos, I advice to enter this value list as comment for this field in table structure. It will be handy to have a certain place to look for it when there is a need for it.

[Gemini]

Thanks so much for the help!

I actually found a simpler solution. The RowSource of the Form Combo-Box had been assigned to Table/Query, which linked to another Table with a column of "Business Model" values I had defined (ex. "manufacturer", "distributor", etc.)... I had set it up that way so that the list would be dynamic - if I ever want to add another category to the Combo-Box all I have to do is add it to the table and it automatically shows up in the Form drop down list.

The problem turned out to be in the "Bound Column" property of the Form Combo-Box. The column that was bound was "1", which was the ID column of the drop down list record table. So as soon as I changed that property to "2" (Bound Column = 2nd column from the RowSource Table/Query, which was the column containing my defined values), then the problem was solved! When I entered a new record in the form, it populates the data table with the same value that displays in the Form view, which for my purposes is how I want the data to display.

Thank y'all both very much for your help!

[Gemini]

Okay, I've run into another quandary.

I took ArviLaanemets' advice to hide the main data table from users (including myself) and created a navigation form to get around where I need to be.

There are really only two Forms that are needed at this stage of my database project: a Vendor Entry Form and a Vendor Search Form.

The Vendor Entry Form was the one in question earlier in this thread and I managed to get the combo-box drop down list issue sorted out.

Below is a screen shot of the combo box in the Vendor Entry Form and the Property Sheet for that combo box. This is working properly.




Now I am having a similar issue with a second form I created: the Vendor Search Form.

This form was created as a split form with a simple text filter search box for the "Products" field of the data. Users may search for a product key word and generate a filtered list of vendors for the product they are looking for.

Now I'm having a similar problem with the combo box in the search form. It only populates with the record ID from the table of drop down list values. I am pulling values from the same Table, and I set all the properties identically - yet it yields the ID instead of the second column of textual descriptions that I am looking for. See below.

Screen shot of Vendor Search combo box:



The property sheet for this combo box:



Finally the Table I created for drop-down list values. (This was created so that I could dynamically update the combo box selections).



What I want is for the combo box on the search form to behave the same way as the combo box on the entry form. I want both combo boxes to be dynamically related to the table of drop-down list values I created.

The entry form combo-box seems to work just fine. The search form combo-box isn't behaving. I have attempted to configure all the controls the same way.

What am I missing here? What is the variable that is off that is creating the problem?

Thanks in advance.

[ArviLaanemets]

The problem is on Format sheet of properties. Access displays in combo only 1st column of non-zero width - obviously you have set the width for first column in ColumnWidths property >0.

You prefer to have business model texts as entries in BusinesModel field. In this case having the table for business model selections is really overhead. The main reason to have such selection tables is the possibility easily edit displayed text for selected business model, and this works only when BusinesModelID is stored. An Example:
You currently have "Supplier / Trading Company / Broker" as 3rd business model. Now someday you decide, that this text is too long, and "Supplier" is enough. You change the text for 1 row in tblBusinessModels, and with your current design, all future entries will have new text, but all older entries will have old text - you have to edit all older records in all tables where business model info is stored to correct them. When you have BusinessModelID instead BusinessModelText in your tables, the new text for edited business model is displayed in all older entries too (i.e. in combo boxes linked with BusinessModelID) without any need to edit them.

[ssanfu]

I would first fix the object names: REMOVE ALL SPACES. This includes field names, query names and form names.

I would get rid of any LOOK UP FIELDS.
See:
The Evils of Lookup Fields (in tables)
http://access.mvps.org/access/lookupfields.htm

Then you can configure the combo box controls as Arvil has suggested....


Also, I would move the contact info fields to a "Contacts" table and use a sub form for the contacts.



Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.