In my split Access (2007) database I have two linked tables "Consumer" and "Consumer Equipment". In the Consumer Equipment table I have a field named "Assignment". Assignment is the field that is linked in a one to many relationship with the Consumer table. When we enter a new item in the Consumer Equipment table there is a combo box that allows us to select from a list of our consumers.
The row source I used for this was: SELECT Consumer.[Last Name] & "," & " " & Consumer.[First Name], [Consumer].[ID] FROM Consumer ORDER BY [Last Name];
The bound column is "2" ,the column count is "2" and the column width is 1";1".
This provides a tidy list of the consumer's last name, first name and then their ID number.
Now that I have created a SQL database I tried using the row source of: SELECT [dbo_Consumer].[Last Name] & "," & " " [dbo_Consumer].[First Name], [dbo_Consumer].[ID] FROM dbo_Consumer ORDER BY [Last Name];
Now the Assignment field shows the Consumer ID (primary key) and if I pull the combo box down there is nothing there.
Obviously I am doing it wrong.....