As a new comer to VBA programming please, can any one point me in the right direction, I am using a combo box to allow changes to the linking of two tables the the main key in the combo being bound to a key in the matching table, My question is ? Can values from other column's in the combo list be inserted/bound to other field in the same record. the reason for this I would like to use some of the values at a later date as links between forms, where adding the table into the form query makes data entry not possible. Thank you for any help Colin . To elaborate:-
 In a project (an inventory of items). I am using a front end rear end method to connect multiple databases, the front end database having of course all the forms /reports the rear end has one holding data the remainder basically hold a key field & a thumbnail photo's of each item (for security)(in access 2002 each db limited to 2GB).
 On one of my forms (frm1)using table(A)I have a button, the VBA of which uses a CASE method to select which database & its specific input form (frm2)using Table(B)to open my item's are grouped AA , AB etc each being assigned to a specific database to store the graphics the varible number used by CASE being created in the form query using an IF ie AA =101 BA = 201 this method is limited how many variables it allows to be created.
 Thus restricting the number of attached database that can be accessed. I thought I could solve this by using two tables the one (C)that hold two fields, the lookup values AA, BA, BC,& an autokey bound to table (A)) and a second table (D)hold two fields one holds the autokey bound to table (C)the second the values 101 201 etc these being selected from a combo list by the user to assign which database (table B)they want the graphics stored,
 These two table (C,D)then can be included in (frm1)query of Table(A)+(C) +(D) thus giving me a bound field on the form (frm1)that supplies the CASE variable as my first method, using the IF exspression. to select (frm2)but when I added the third table(D) to my form (frm1)query is gave me the variable but made the form (frm1)read only, data could not be edited or added. although with a query of (A)+(C) works ok.
 Therefor I thought if I can bind/insert/autopaste another field from table(D)into table (C)make this table have three fields or is there another way I can declare the CASE 101, CASE 201 variables or do I forget the whole thing & try again,

I am not sure I understand what you want to do.

Do you want more columns of meaningful data to show up in the combo box? Do you want all the fields in a record to be linked using the FK? (This already happens.)

Can you explain more?


mmcdonal

When you have a multi-column combobox, you can refer to all columns in selected item as such

Example: Combobox called cboChoice with four colummns

Me.cboChoice.Column(0,0) is the first column
Me.cboChoice.Column(1,0) is the second column
Me.cboChoice.Column(2,0) is the third column
Me.cboChoice.Column(3,0) is the fourth column


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thank you Greg for your advice, I have used your info & created a text box on my form with the source as " =Me.cbomylink.Column(1,0)" & a value is shown smashing, but is it possible automatically to insert/save that value to the records table when you move to another record, the same as the column(2)that is already bound to the combo "second table" so that I can use the value at a later stage if required, without actually linking the tables. your help appreciated.
Thanks again Colin

You can set the Bound Column property of that combobox to the second column so that when you move to a new record, the second column value is stored. Open the form in design view, click on the combobox and choose, say, 2 for the bound column. Unlike the indexing of the columns, e.g. 0 to 3, the bound column property actually starts at 1, i.e. 1 to 4. So if you want the second column to be bound, enter 2.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division