Combo box - forcing more than one bound column

[Z1ppy]

I have a Combo Box on a Form which looks at 4 columns on a Table which contains data which the user uses to lookup and choose for saving into the Main Table.

I want to bind Column 1 and Column 4 of the Combo box to relevant fields in the Main Table.

Usually you can only bind one column of a Combo Box in this way, but I know there is a string that forces two columbs (or more) to be bound.

The string I have is below:

field2 = combobox.column(1)

This is supposed to go in "the afterupdate module of the combo box" (quoted from my Google search).

I would like help with the following:

I presume this code needs to go in the Combo Box properties under Event - After Update as an [Event Procedure]. Please confirm?

My Combo Box Columns (the lookup table fields) are called Address.fldname and Address.Code. My Main Table Fields are called Client_Address and AddressCode respectively.

Somehow the above string needs to contain these field names but I am not sure about syntax.

Any help much appreciated - and it's fun - isn't it?

 

[RuralGuy]

How is the RowSource of the ComboBox related to the RecordSource of the form? Done correctly and using a query as a RecordSource for the form, only one column of the ComboBox need be bound and the data from the RowSource of the ComboBox would magically appear in the form.

 

[Z1ppy]

Hi RuralGuy

Are you saying I should build a Query to look at the lookup table fields and then connect the Control Source of the Combo Box to the Query instead of the lookup table?

 

[Z1ppy]

I just tried building a Query and setting this between the Combo Box and the Table. This allows the one bound column to save across to the Table but not additional columns in the Combo.

I still feel I have found the correct piece of VB

field2 = combobox.column(1)

but can't work out how to relate the string to my required fields (see above).

 

[RuralGuy]

What you are proposing {field2 = ComboBox.Column(1)} would actually put the value of ComboBox.Column(1) in two fields in your Database. That violates normalization rules and can compromise the integrity of your data. All that is required is that the PrimaryKey field value of the RowSource table (address??) be place as a ForeignKey in the RecordSource table. Joining the two tables on that field would make all of the fields of the Address table available in the query.

 

[Z1ppy]

All that is required is that the PrimaryKey field value of the RowSource table (address??) be place as a ForeignKey in the RecordSource table. Joining the two tables on that field would make all of the fields of the Address table available in the query.

I think I may have misled you here. I am using the Main Table to input Client details (name, address etc.) and using the lookup table to input additional information (their registered surgery address) to the record in the Main Table. The Main Table does not hold details for reference, it is an input table.

In my mind then, you cannot create a Relationship between the two tables.

I am just looking to be able to save two columns of data from the Lookup Table to the Main Table via the Combo Box on the Form. Without the correct code, you can only bind one column, but I'm thinking there must be a way to force this.

 

[Z1ppy]

Ureka!

After a quiet moment away from the PC so away from the problem it dawned on me. Of course you can’t force a Combo Box to bind more than one column to an input Table from a Lookup table – because you don’t need to. To do this would cause a duplication of data in one database which is a waste of space. The old Granny’s Cupboard adage – “one of everything and everything in its place” came to mind.

The solution is to have a unique reference column in the Lookup Table and a Field in the Main Table which stores this reference and then interrogate the data through Queries; which is what RuralGuy was trying to explain – unfortunately to someone inexperienced with the basic principles of databases!

Many thanks – problem solved or at least understood!

 

[rsmonkey]

normalisation big guy... its where all the kids should be at!

 

[RuralGuy]

It sounds like you are on your way Z1ppy!