Two Combo Boxes Update Form

[adamjon92]

Hello all,



I currently have one combo box that updates all of the information in a form and subform. The combo box has the company abbreviation that is updating the company name, contact name, address, etc. The subform has the company abbreviation computing the spend, tonnage, spend/ton, etc. Everything is being pulled from a table called "Company Information". Everything is currently working perfectly fine. However, I want to be able to add a combo box for the company name, so if you don't know the abbreviation, you can just select the company name and have it fill in the rest of the form and subform, just like the company abbreviation combo box currently does.

Hopefully it is a simple answer, but I can't figure it out right now. The company information table currently has all of the information included (company name, abbreviation, contact name, address, spend, tonnage, spend/ton, etc.) I tried making a separate query that just has the company name and company abbreviation fields (let's call it "Company 2"), then making a union with "Company Information" and "Company 2" by linking the company abbreviation fields. I added the company name in the row source for the company name combo box, but it didn't work.

Any ideas? Thanks in advance!

Adam

[cyanidem]

You can still use just one combobox for that. Change record source of combobox so it contains both fields (abbreviation and company name), change column count to 2 in properties but leave column 1 (abbreviation) as bound. You can change width of columns to be displayed in properties also so when combobox is dropped down user can see both columns.

[adamjon92]

You can still use just one combobox for that. Change record source of combobox so it contains both fields (abbreviation and company name), change column count to 2 in properties but leave column 1 (abbreviation) as bound. You can change width of columns to be displayed in properties also so when combobox is dropped down user can see both columns.

Awesome, it worked! I appreciate your help. Have a great Wednesday.

[619access]

I have a similar problem but the above solution will not help me. Here's what I have.

We collect exposure assessments for various chemicals in the workplace. As part of the recordkeeping process, our Access database includes the Chemical Abstract number (CAS) and the name of the chemical. We can select from a dropdown list in one cell which then populates that cell with the CAS number and the adjacent cell with the name of the compound (i.e. 7439-92-1 Lead and Inorganic Compounds (as PB).

Like the above recommendation, both CAS and Name appear in the dropdown for Cell 1 but the problem is that the CAS are numbered sequentially, small to large number, but the chemical names are not in alphabetical order because CAS numbers bear no relation to alphabetical order of the chemical.

Is it possible to have a dropdown for the CAS in Cell 1 that populates Cell 2 while also having a dropdown in Cell 2 that lists the chemicals alphabetically and then populates Cell 1? I'm looking for a solution whereby if an individual does not know the CAS number and only the chemical name that they can quickly populate the fields using the name only.

[orange]

619access,

It is probably better to create a new thread and post when you have a topic that is new.
When you attach yourself to an older post, your post may just get lost.

I see you are new and this is post #1 so here are a few general comments.

Cells are a spreadsheet (Excel) term, not database. With database there are tables that contain fields. Data is stored in tables. Forms are like a window into the stored data. That is, the form is an interface through which data in a table(or query) can be exposed to the user. On a form are controls(not fields) that can be populated directly from tables, or some controls (such as a combobox) have their own rowsource.

Perhaps you could tell readers what you are trying to do in plain English and reduce the use of quasi-terminology (cells and drop downs..). Once readers understand the issue, then more focused responses and options will be forthcoming.

The rowsource for a combobox can be a query with an alphabetic sort on Name, such as

SELECT  CAS, CompoundName from YourTableName
Order By CompoundName

Good luck.

[619access]

619access,

It is probably better to create a new thread and post when you have a topic that is new.
When you attach yourself to an older post, your post may just get lost.

I see you are new and this is post #1 so here are a few general comments.

Cells are a spreadsheet (Excel) term, not database. With database there are tables that contain fields. Data is stored in tables. Forms are like a window into the stored data. That is, the form is an interface through which data in a table(or query) can be exposed to the user. On a form are controls(not fields) that can be populated directly from tables, or some controls (such as a combobox) have their own rowsource.

Perhaps you could tell readers what you are trying to do in plain English and reduce the use of quasi-terminology (cells and drop downs..). Once readers understand the issue, then more focused responses and options will be forthcoming.

The rowsource for a combobox can be a query with an alphabetic sort on Name, such as

SELECT  CAS, CompoundName from YourTableName
Order By CompoundName

Good luck.

Mea Culpa. I'm new to Access but could run rings around most with Excel. Hence my 'Excel' terminology.

Let me be clear - when I speak of a 'cell', I'm referring to the visible 'cell' in a form. I guess you referred to that in the previous post as 'controls' is what I meant by 'cell'.

The official MS Office support site indicates drop-down as an acceptable term when using List Boxes and Combo Boxes, BTW.

It's the input I'm concerned with here. Hopefully someone has a good solution. If you think this is best addressed in a new post please let me know.

[orange]

No problem. My understanding of your post and the clarification confirms we're talking about controls on a form.
Your Excel proficiency may hinder your learning/adaptation to Access (or database generally).
Database is built on a different model and different concepts.

And yes, we see drop down quite often.

The query I provided in post 5, is the sort of thing you would need to populate your dropdown/combobox.
Typically, tables and relationships would be related based on the unique numbers (primary keys/foreign keys), but in the user interface the records (data) would be presented to users in "familiar terms". In your example, I would think the compound name would be the "familiar term/working language".

In database terms, these fields would be in the same record of a table.

Consider, MyChemTable as a "container"
records in the table would be made up of fields

CAS_Number
CompoundName
..other info about this specific entry


and the table could have records (mock up from the CAS link I found)
[code]
7439-92-1 Lead and Inorganic Compounds (as PB)
50-78-2 Benzoic acid, 2-(acetyloxy)-
.....
....
.....
[\code]

When these are displayed for selection in the dropdown, the name could be presented in alphabetic order.

[619access]

Since a picture is worth a thousand words, I'm attaching a screen shot of my input form. The arrow going both ways illustrates what I'd like to accomplish. I'd like to not only select CAS in the left control to populate the right control, but I'd like the ability to select the chemical name in the right control to populate the CAS in the left control Am I asking too much of MS Access or is this possible?

[orange]

I think it's possible, but we're talking conceptually here. Details and the nitty gritty may constrain the approach.
Can you post a sample database with some records and a form? Just a few records to illustrate the data and the form you're working with. Readers will have something real to work with.

The critical part of the 2 way thingy will be the relationship between the numbers and the names 1:1--hopefully.