Combo Box and store 2 values in table

[Deirdre]

Good afternoon.

I'ev created a data entry form which will be used to store responses from a questionnaire. On this form, I've also created a combo box based on a query which contains 4 fields (DriveDate, SponNum, Site, Town). This is not used to auto-populate any fields, just to distinguish where the questionnaire originated.

My problem is that once the rest of the form is completed, only the date information from the aforementioned combo box is stored. At minimum, I would like the Date, Site, Town stored in the one field.

Your assistance is greatly appreciated.

 

[SydneyGeek]

You probably don't need to store those items again, if you already have them in the table on which the combo is based. They can be pulled onto the form as the result of a relationship.
Takes a bit of explaining, but in the Northwind sample database, look behind the scenes in the Orders form. When you select a customer, the combo stores the CustomerID, and pulls all of the customer's address details from the Customers table. The work is done by the related tables in the query behind the form.
In essence, you store the ID in a Number field if the related table uses an Autonumber ID, or a Text field if the other table's ID is Text.
If you need to pull in data which can then be changed just for that specific record, then you need to push the data into your second table. I can provide instructions if that's the way you need to go.

Denis

 

[Deirdre]

Thank you for the reply.

The reason I use the query is the original table consists of 12k rows. I use the query to only pull data from certain towns and certain dates. That query information is really just used to make the data entry simpler and provide an additional piece of information which cannot be extracted from the questionnaire. Once the form is completed, the information is stored in an alternate table which has no relationship to the original table.

 

[SydneyGeek]

OK, if you're putting the data in an unrelated table you can push it to the new fields using the combo. Do this:

In Design view, go to the Combo's Properties. Click the All tab, select Row Source and click the Builder (...) button to go to the underlying query for the Combo. You can put as many fields as you like in here -- make sure the first is an ID; the others can be more descriptive (Name, Item Description, List Price for example) -- these fields can come from two or more related tables if needed. Let's say you choose 4 fields.
Close the query, save when prompted.
Change Column Count to 4
Change Column Widths to 0;1.25;1.25;1.25 -- that will hide the key field and show the others when you pick from the combo. (Adjust these widths to suit the data). Once your selection is made, only the first of the displayed fields will show.
Change List Width from Auto to be the sum of the Column Widths.

Now the display is showing what you need, click the Events tab.
Double-click After Update. You'll see Event Procedure. Click the Builder button to go to the code sheet. Enter this code (adjust names to suit your controls):
[txtName] = MyCombo.Column(1)
[txtDescription] = MyCombo.Column(2)
[txtListPrice] = MyCombo.Column(3)

Note: Combobox and Lixtbox columns number from 0, not 1, so this will push fields 2, 3 and 4 from your combo into the text fields.

Close, save, and give it a go.

Denis

 

[Deirdre]

Denis,

Thank you so very much for your assistance. It seems my recent maternity leave has wreaked havoc on my Access knowledge and has made my ability to perform even the most basic functions in Access a monumental chore.

Thank you again.
~Deirdre

 

[SydneyGeek]

Happy to help

Denis

 

[testaccessuser]

OK, if you're putting the data in an unrelated table you can push it to the new fields using the combo. Do this:

In Design view, go to the Combo's Properties. Click the All tab, select Row Source and click the Builder (...) button to go to the underlying query for the Combo. You can put as many fields as you like in here -- make sure the first is an ID; the others can be more descriptive (Name, Item Description, List Price for example) -- these fields can come from two or more related tables if needed. Let's say you choose 4 fields.
Close the query, save when prompted.
Change Column Count to 4
Change Column Widths to 0;1.25;1.25;1.25 -- that will hide the key field and show the others when you pick from the combo. (Adjust these widths to suit the data). Once your selection is made, only the first of the displayed fields will show.
Change List Width from Auto to be the sum of the Column Widths.

Now the display is showing what you need, click the Events tab.
Double-click After Update. You'll see Event Procedure. Click the Builder button to go to the code sheet. Enter this code (adjust names to suit your controls):
[txtName] = MyCombo.Column(1)
[txtDescription] = MyCombo.Column(2)
[txtListPrice] = MyCombo.Column(3)

Note: Combobox and Lixtbox columns number from 0, not 1, so this will push fields 2, 3 and 4 from your combo into the text fields.

Close, save, and give it a go.

Denis

Hi Dennis,

I am facing the same issue.
Combobox autofilled values are somehow not getting saved.

The solution that you mentioned:

[txtName] = MyCombo.Column(1)

What is [txtname]??
Is it table name, or text box name??
Since i want to store the value in a table called Nominations, Should this be the correct syntax:
[Nominations].[Name] = ComboStaffID.column(1)

Where Nominations = Table
Name = Column name
CombostaffID = Combobox name

Please advice..help is badly required