Combo Box hell

[LeBron]

I'm new to Access. I have a database with two tables - Companies, and Employees - by importing data from Excel. I've created a form including all the fields from both tables (Company Name is the primary key and I have established a one-to-many relationship). I'm trying to create a combo box for one of the fields, Industry, to limit the entries that someone using the database can input ie. Electronics, Financial etc.

I'm going wrong somewhere, because after I change the Industry field (in Form Design View) to combo box, and switch to Form View and try to change the Industry of a Company, nothing appears when I start typing - no list, nothing.

I've set the validation rule to include only the entries I want to allow. I do get the error message when entering something that isn't part of that rule.

I had previously set the validation rule for this field in the Table, but then decided I wanted to enable people to start typing and see a drop down list. I've had the same problems trying to create a List Box too. Can anyone help? It's pretty urgent - I need to resolve this today or tomorrow at the latest. Thanks.

 

[RuralGuy]

I would use an AutoNumber as a PrimaryKey rather than the [CompanyName] field. do you have an [Industry] table with an AutoNumber PrimaryKey?

 

[LeBron]

I think I just figured out how to add Combo Boxes - using the Toolbox - but my fear is that I will have to enter the appropriate Industry entry each record manually, rather than just import directly from Excel. Is that right?

 

[LeBron]

I would use an AutoNumber as a PrimaryKey rather than the [CompanyName] field. do you have an [Industry] table with an AutoNumber PrimaryKey?

Thanks. What's the benefit of having an AutoNumber as PrimaryKey rather than the CompanyName field? I have established a one-to-many relationship between the Companies Table and Employees Table - each record on the Employees Table must match a Company Name on the Companies Table.

I don't have an Industry table. The Industry field is part of the Companies Table. I only have two tables. I have about 110 records in Companies and 150 records in Employees, so I'd love to be able to do this by importing from Excel.

I'd also like to use Check Boxes for about 12 fields in the Companies Table - as with the Combo Boxes, I've discovered how to add them via the Toolbox, but again it looks like I have to repopulate that data. In Excel those fields were marked "X" or blank. It's not the end of the world to go through each record like that but I would love to avoid it if I can.

 

[RuralGuy]

Not at all. If the ComboBox is bound to that field it will display what is in there until you change it.

 

[RuralGuy]

Company Names can change which would require cascading updates which I do not like. I also believe a search on an AutoNumber field is faster that a Text field.
CheckBoxes have default values to deal with your issue.

 

[rainman89]

I suggest reading up on normalization before you go digging to deep into this problem.

 

[RuralGuy]

To be flexable and increase maintainability and eliminate hard coding I would strongle recomment you create an Industry with your values in it. Then your ComboBox can use it rather than a hard coded list in the cbo.

 

[RuralGuy]

Anyone moving from a spreadsheet to Access should take Ray's advice *very* seriously. It can be a big learning experience.

 

[LeBron]

Thanks guys.

 

[LeBron]

OK. Read up on normalization so I've made some changes. Instead of two tables, I now have 5:

Companies
Employees (ie. name, email address)
Industry (which I want to be a Combo Box or List Box)
Geography (which I want to be a Combo Box or List Box))
Focus (about 10 fields in this, all "X"s or blank in Excel, which I want to be check boxes in Access)

The primary key is the Company Name (I know, I know, and I'm going to look at using an AutoNumber instead). I presume I don't set a primary key with the other tables. Each table includes Company Name as a field. I've established a one-to-many relationship between the Companies table and all the others, with Cascade Update, but no other relationships. Do I need to establish relationships between all the other tables too?

Forms:

I still can't change the Industry field, now in the Industry table, to a Combo Box that shows the drop down list even after entering the Validation Rule. It tells me when I enter something not on the list, but that's it. When I change it to a List Box (I'm just right-clicking on the field and going to "Change To" for these) and switch back to Form View, the Industry field for each record is now blank, and there's no drop-down menu to choose from.

I want the Focus field to be a check box - currently either X or blank - but am unable to change it to Check Box, again by right clicking and choosing it from the menu.

Thanks again for your help...

 

[RuralGuy]

Use the ComboBox wizard to add a cbo to your form and choose Option #1. "Pick a value in a table or query". This will be your permanent control for that field when you get it working and you can delete the other control. BTW, you never know when an AutoNumber PK will come in handy down the road.

 

[LeBron]

Thanks. What about the relationships - do I need to establish relationships between the four "other" tables, or is it OK to have them all linking separately to the Companies table? Which will now have an AutoNumber as Primary Key

 

[RuralGuy]

You can not force a relationship. Either there *is* one, or there isn't. If there is then use the PK FK system to define it.

 

[LeBron]

You can not force a relationship. Either there *is* one, or there isn't. If there is then use the PK FK system to define it.

Thanks. I think I'm unsure if there's a relationship between the other tables. Maybe this helps to explain the situation:

In the Excel file which I imported to Access, I originally had a column for:

Company Name
Industry
Focus Area 1 eg. Investments
Focus Area 2 eg. Insurance
Geography
Employee Name
Employee Email address

I had multiple employees for some companies and originally had them in one cell. I then split the spreadsheet into different worksheets, so that I could have one row per employee. This particular worksheet was imported as the Employee Table in Access.

So a row in the original spreadsheet could look like:

Company Name: Finance Corp Inc.

Industry: Finance

Investments: X
Insurance: [blank]

Geography: USA

Employee Name: Bob Smith
Email: Bob.smith@financecorp.com

I've left spaces above between the data which eventually populated separate tables in Access ie. Company Info, Industry, Focus (eg. fields are Investments, Insurance etc.), Geography, Employee Info.

So Bob Smith clearly has a relationship with Finance Corp, but does he also have a relationship (in Access) with Investments and USA?

Hope that makes sense...

 

[RuralGuy]

These other relationships can all be gleened by going through the Company table. No need to put any direct relationship for those in the system as I see it so far.