Multiple Column Lookup Display- table & form (2)

[BrianBoyes]

Using Access 2010; starting point for this post was 2011 post "Multiple Column Lookup Display- table & form" .
I have a lookup table with 4 data fields whose values I need to associate with a Master record in my "Main Table". (The main table was initially created by importing an Excel spreadsheet so had all 4 lookup data fields already populated). Having failed to get the lookup wizard to populate more that 2 fields I "purified" my approach, added an atoincrement primary key to the LU table and added the LU key field as a new field in my Main form with the view of deleting the duplicated fields in the main table and comining the values at Report or Excel Export stage by means of a query.
The Access Lookup wizard gave me exactly what I wanted in the Main Table, that is a view of the 5 fields (Auto KEY + 4 data fields) allowing me to make an appropriate record choice and appending the key value in the Main Table. Perfect.
When I went to start editing using the form, only the primary key field was displayed - absolutely useless !
I therefore need to replicate exactly the functionality achieved for the table entry version. That means I need to;
view all the LU data fields prior to record selection,
view all the LU fields after selection (for checking purposes) when the combo box has focus
enter only the LU primary key numeric value in Main Table
I do not want to;
enter a concatenated value in the main table
enter the 4 LU data values in the main table.
The previous post implies that the concatenated value will be entered into the main table field.

 

[pbaldy]

Does this help?

http://www.baldyweb.com/Autofill.htm

 

[BrianBoyes]

Many thanks, it did indeed help.
Some points for less experienced Access database builders;
Microsoft did not like me typing in "SELECT" so the Row Source only worked after using the query builder.
I still only saw 1 field until I noticed that only one column width was defined !! I defined the 5 that I wish to see, found the "Column count" "Column widths" & "List width" properties, set all these as I wished and voila ! The form now works in the same way as the table !
The most frustrating part of most Microsoft programmes is that many things are simple but very little is obvious, c'est la vie !

 

[pbaldy]

Happy to help.