comboBox mulitiple fields (1 Viewer)

Dick7Access · Dec 8, 2017

If I create a table with multiple fields, is it possible using a combo box to select and save into the main table more than one field?

EXAMPLE: Lets say I have a table called tblNamecode and a tblMain and from my main form I have a combo box that when I choose a name from the list in tblNameCode it will post both the name in the name field and the code in code field in the tblMain.

isladogs · Dec 8, 2017

Yes.
Just reference the appropriate column number for each field other than the default column remembering the first column is Column(0)

Pat Hartman · Dec 8, 2017

HOWEVER, you're missing the point of a relational database. In a relational database, we use tables to store data and we want to be as efficient with that as possible so we do not store the same piece of data multiple times. We use queries to retrieve data and a query that joins the tblMain and tblMainCode would bring back both the ID and text value with no problem and no potential for a data anomoly (when you store data twice, you have to ensure that one value doesn't get out of sync with the other.

BUT, there are situations where you would want to pick up an additional value from the lookup table. One example would be an Order Entry application. UnitPrice is the price at a POINT IN TIME and therefore, when you choose a product from the product table, you would also copy the price at that point in time because next week if there is a price change, you wouldn't want it to affect orders that had already shipped.

To expand on Ridders explination, the RowSource of a combo or listbox is a zero-based array. Typically we refer to the first column (which is also almost always the bound column) as Me.cboProductID. But, if we used the .column property, it would be Me.cboProductID.Column(0). To address subsequent columns of the you would use the correct index value. So, assume the RowSource is ProductID, ProductName, UnitPrice.

ProductID is the bound column and is hidden so the combo shows the second column. It could also show the third column if you set the width to something other than zero but the combo at rest will show only the first visible column. To address the UnitPrice (third column) and save it use

Me.txtUnitPrice = Me.cboProductID.Column(2)

In the AfterUpdate event of cboProductID.

If your example is real rather than made up - don't do it. You do not need to store this particular piece of data twice. Use a query with a join for your form/report to pull up the text value.

Dick7Access · Dec 8, 2017

Pat Hartman said:
HOWEVER, you're missing the point of a relational database. In a relational database, we use tables to store data and we want to be as efficient with that as possible so we do not store the same piece of data multiple times. We use queries to retrieve data and a query that joins the tblMain and tblMainCode would bring back both the ID and text value with no problem and no potential for a data anomoly (when you store data twice, you have to ensure that one value doesn't get out of sync with the other.

BUT, there are situations where you would want to pick up an additional value from the lookup table. One example would be an Order Entry application. UnitPrice is the price at a POINT IN TIME and therefore, when you choose a product from the product table, you would also copy the price at that point in time because next week if there is a price change, you wouldn't want it to affect orders that had already shipped.

To expand on Ridders explination, the RowSource of a combo or listbox is a zero-based array. Typically we refer to the first column (which is also almost always the bound column) as Me.cboProductID. But, if we used the .column property, it would be Me.cboProductID.Column(0). To address subsequent columns of the you would use the correct index value. So, assume the RowSource is ProductID, ProductName, UnitPrice.

ProductID is the bound column and is hidden so the combo shows the second column. It could also show the third column if you set the width to something other than zero but the combo at rest will show only the first visible column. To address the UnitPrice (third column) and save it use

Me.txtUnitPrice = Me.cboProductID.Column(2)

In the AfterUpdate event of cboProductID.

If your example is real rather than made up - don't do it. You do not need to store this particular piece of data twice. Use a query with a join for your form/report to pull up the text value.

thanks Pat. Yes the DB I am making is a mess. but I don't have time to do it right. I will make a new one at a later date, I am in a weight lost program at the VA and I have to track carbs, calories etc. I need it right now. It is working fine, I can print out each days input for class leader. Their method is hard copy, and going from nutritional chats and hard copy is even more tedious. Some of my entries are repetitious such as my coffee every day, twice a day. I am hoping to automate entries. I tried looking for a good Access template but did not find one. I have an app on my smart phone but haven't figure out how to use it.

Pat Hartman · Dec 8, 2017

but I don't have time to do it right

As one of my mentors was fond of saying - "If you don't have time to do it right, what makes you think you have time to do it twice?" He was right.

Is it really harder to use a query when you need to get the text field rather than using the table? I don't think so.

Dick7Access · Dec 8, 2017

Pat Hartman said:
As one of my mentors was fond of saying - "If you don't have time to do it right, what makes you think you have time to do it twice?" He was right.

Is it really harder to use a query when you need to get the text field rather than using the table? I don't think so.

Maybe not, but right now I am packing to go on a ministry trip. the junk db I built in a hour three days ago, and I am able to record all the food. I have a lots of time in motels to create db's.
what do you mean using a query (in this instance) Please elaborate

Uncle Gizmo · Dec 9, 2017

Dick7Access said:
EXAMPLE: Lets say I have a table called tblNamecode and a tblMain and from my main form I have a combo box that when I choose a name from the list in tblNameCode it will post both the name in the name field and the code in code field in the tblMain.

What you've actually got is one table and a lookup table...

The table "tblMain" is where you are storing your information. The table "tblNamecode" which stores the name and the code is actually a lookup table. All you need to do is add an extra field to this table "tblNamecode" an identity field let's call it "ID"... Have it set to auto number. Now add a field to your "tblMain" let's call it "fldNameCode" set it to Long data type. Now the "main form" you mentioned let's call it "frmMain"... Have it's recordsource set to "tblMain" bind the combobox to your newly created field "fldNameCode" and set the rowsource of the combobox to "tblNamecode".. Have the combo set to place the "ID" selected from the table "tblNamecode" into the field "fldNameCode" ... This way you get your cake and eat it!

Edit:-

Re:- >>>save into the main table more than one field?<<<
I forgot to mention, you don't save the name and the code, you leave them in the lookup table. If you want them for anything you just find them using the ID you stored in the main table, and display them or whatever you need. This has several advantages the main one being that if you ever need to edit somebody's name let's say you spell Mr Smith as Mr Smyth you can change it in one place throughout your database...

In this YouTube Video I explain one way of getting the info - Get Info From a Combo-box 1 - Nifty Access

Dick7Access · Dec 10, 2017

Uncle Gizmo said:
What you've actually got is one table and a lookup table...

Do you have a snippet of one (multiple field combo box ) (look up) or a link to one that you can send me?

Pat Hartman · Dec 11, 2017

Here's a database with some samples that might help you.

Dick7Access · Dec 13, 2017

Uncle Gizmo said:
What you've actually got is one table and a lookup table...

The table "tblMain" is where you are storing your information. The table "tblNamecode" which stores the name and the code is actually a lookup table. All you need to do is add an extra field to this table "tblNamecode" an identity field let's call it "ID"... Have it set to auto number. Now add a field to your "tblMain" let's call it "fldNameCode" set it to Long data type. Now the "main form" you mentioned let's call it "frmMain"... Have it's recordsource set to "tblMain" bind the combobox to your newly created field "fldNameCode" and set the rowsource of the combobox to "tblNamecode".. Have the combo set to place the "ID" selected from the table "tblNamecode" into the field "fldNameCode" ... This way you get your cake and eat it!

Edit:-

Re:- >>>save into the main table more than one field?<<<
I forgot to mention, you don't save the name and the code, you leave them in the lookup table. If you want them for anything you just find them using the ID you stored in the main table, and display them or whatever you need. This has several advantages the main one being that if you ever need to edit somebody's name let's say you spell Mr Smith as Mr Smyth you can change it in one place throughout your database...

In this YouTube Video I explain one way of getting the info - Get Info From a Combo-box 1 - Nifty Access

Uncle G,
What am I missing?

Dick7Access · Dec 15, 2017

I have been studying everything I can find on multiple field combo box, and can not get it to work. If someone would be so helpful as to include a two field combo box for me on the attached example db I would greatly appreciate it.

Pat Hartman · Dec 15, 2017

I made a number of changes.
1. Changed the Foreign key in tblMain to be FoodID because you need to point to the PK of the related table not some random data column.
2. Changed the combo to bind the first column which is FoodID since that is what needs to be saved in tblMain
3. Changed the RecordSource of frmMain to be a query so that it will display the calories field. But, I locked the calories field since you should not modify it on this form. On this form it is only for display. You will have a different form where you manage the data in tblFoodItems.
4. Neatened up some names.

Dick7Access · Dec 16, 2017

Pat Hartman said:
I made a number of changes.
1. Changed the Foreign key in tblMain to be FoodID because you need to point to the PK of the related table not some random data column.
2. Changed the combo to bind the first column which is FoodID since that is what needs to be saved in tblMain
3. Changed the RecordSource of frmMain to be a query so that it will display the calories field. But, I locked the calories field since you should not modify it on this form. On this form it is only for display. You will have a different form where you manage the data in tblFoodItems.
4. Neatened up some names.

Pat or anybody
I want to add more lines of food. The example I am sending I have highlighted the Row source for each ling, in a label so I could identify where I would need to make a change. Being Identical of course, they put the same choice in both combo Box. I have look through all the items of the property sheet of each combo box, to see if I could identify where to make changes. Can’t come up with anything. Don’t code it for me, but please tell me where to look to get each line to stand alone. I need to learn this.

MarkK · Dec 16, 2017

What you might need here is a many-to-many relationship, not a bunch of combos. Here's a sample of what I'm talking about...
hth
Mark

Dick7Access · Dec 16, 2017

MarkK said:
What you might need here is a many-to-many relationship, not a bunch of combos. Here's a sample of what I'm talking about...
hth
Mark

Thank you my friend. Once you posted the light came on. I have other DB with a many to many for my meetings but it didn't occur to me to use it in this situation. Thanks again
Dick Sonier

MarkK · Dec 16, 2017

You bet, all the best,
Mark

Dick7Access · Dec 17, 2017

Mark or anybody
While I made a many to many years ago I did not make one with a combo box. I am trying to add fat, carbs, etc. but can’t figure out where to go from here.

MarkK · Dec 17, 2017

Can you say more about your overall purpose? In a many-to-many relationship you essentially have two parent objects connected by the join table, and each row in the join table defines a single relationship between two of those parents. It is not essential, however, in the interface where you make this selection--where the relationship is defined--that ALL properties of one or more of the parent objects MUST be displayed. The fPersonFood subform is not about the food, and it is not about the person, it is about the fact that they are joined. To me, if you want more info on the food, then open the fFood form to the food item you want to explore.

See what I'm saying? By showing all that food info in the fPersonFood subform, you are working against that grain of how those tables and forms are structured.

So, the question arises in my mind, what is your purpose here? Maybe you don't need a many-to-many.

hth
Mark

Uncle Gizmo · Dec 17, 2017

"Many to Many" is a good description of the actual table that stores the information. A basic many-to-many table would consist of two fields, a left field and right field. You could describe this table as many items on the left linked directly to many items on the right. However although that is a correct description, it's also a misleading description, as it focuses attention on the actual many to many table. Whereas when you extract information from the many-to-many table all you ever do is extract "One to Many!" More info in my blog here:-

Many to Many Relationship:- http://www.niftyaccess.com/many-to-many-relationship/

Note! the videos on my blog page show subforms. You should get an idea of how it works from the subforms example. When I get a minute, I will add a video demonstrating combos...

P.S. You Should heed Marks advice. - I don't have Access to Access at the moment, so I can't see your specific problem ...

Pat Hartman · Dec 17, 2017

There are issues with the m-m sample you are using. For example FoodID does not belong in tPerson. You also seem to have changed the data type of person to date in your version of the database.

Here's a MM sample that I built. It is not customized for you so you will have to interpret it although I did add a feature today that will help you. This example is Employees and classes and I added a grade average to the employee form. You can think of this as people and food and just add the columns you need. Using Avg() makes sense for grades but you will probably want to use Sum() because you would want the total calories rather than the average per food.

Pay attention to the details such as the relationship diagram and the BeforeUpdate code. The Classes form uses a popup form rather than a subform so you can see different techniques for viewing the junction data. It also makes more sense to have the Classes main form be a list rather than a single record form. So on the classes form, double click on the Class title to open the popup form. None of the VBA is complicated so you should be able to follow it.

comboBox mulitiple fields (1 Viewer)

Dick S

MVP / VIP

Super Moderator

Dick S

Super Moderator

Dick S

Nifty Access Guy

Dick S

Super Moderator

Attachments

Dick S

Attachments

Dick S

Attachments

Super Moderator

Attachments

Dick S

Attachments

bit cruncher

Attachments

Dick S

bit cruncher

Dick S

Attachments

bit cruncher

Nifty Access Guy

Super Moderator

Attachments

Similar threads

Users who are viewing this thread