Combo box shows like id, Firstname, Lastname. Then stores like 1,2,3 in the database.

[bbarclay]

Combo box shows like id, Firstname, Lastname. Then stores like 1,2,3 in the database based of the multiple select lookup field. This is ok, i want it by id stored in the database, not by name. Though, is there a way to make it show last name in the combobox like, Barclay,Smith,Johnson, instead of 1,2,3. If so please share




Access 2010.



Thanks

[Bob Fitz]

If I understand, and my assumptions are correct, then you need to change the Column Widths property. If you take a look at this you should see a figure for each of the columns. Change the first to 0cm which will hide the column. If you currently have the first name in the second column and the last name in the third column but would like to swap their positions, you will need to swap the order in which they are placed in the Select statement that can be found in the Row Source property.

[bbarclay]

That will hide the ID field in the drop down open combobox. We need to show the id in the drop down, also need to store the id in the database like 1,2,3,4. But instead of showing it in the combobox when closed, as 1,5,8,40,43 it would be nice if it would return the last names instead.

[Bob Fitz]

It will hide ths ID but the value of the first column is the stored value even if it is hidden. Do you want to make a selection from the list using the ID or the last name. If you want to make a selection using the last name but need to see the ID then the ID can be put in the list in another column as well. If you want to make the selection using the ID but not show it after, I will need to give it some thought.

[bbarclay]

Yeah, we have ID, First, Last. Which show. The problem is if I store it as Bill Anderson, and Have another Bill Anderson, then if 1 is bill and 2 is Bill as well. It will query the wrong bill anderson if not by ID. But it would be nice to show Anderson, Anderson, but the database still stores 1,2. Because they have different addresses.

[ssanfu]

Then you need to create a concatenated field. The SQL would look something like:

SELECT ID, ID & " - " & LastName & ", " & FirstName as IDName FROM YourTableName ORDER BY LaseName, FirstName

Set the
Bound Column to 1
Column Widths property to 0; 1.5

(Change the field names and table name to your names)

[bbarclay]

This is what it looks like when it's closed. If it would actually show the WYO number like you see in the next field, when closed, that would be ideal. In the next screen shot, this is how I need it to display when dropped down. // Though, it still needs to save in the database by ID number instead of the WYO number.


See the WYO field, it should show the WYO number in the closed combo box "not like in the first picture where it shows ID". // But still needs to store the ID in the database for tracking purposes and reporting, it's easier to pass by id and not by string.



This image is what my ROW source is saying for the combo box.

SELECT Permanent_Records.ID, Permanent_Records.WYO, Permanent_Records.Well_Name FROM Permanent_Records ORDER BY Permanent_Records.ID DESC;

That's what i have so far. I noticed someone said above to change this. Though do i change it for the combobox and leave it the same in the table.

Hope this helps others too.


Thanks,



Brandon

[bbarclay]

Here's the solution. You change the field you want it to show to to be the first field. Then you change the bound by column to the one you want the database to store it by. For instance permanentrecords.wyo is the first then permanentrecords.id this way it still shows the id, WYO will show in the closed box but the database will store the id number into the database instead of the WYO number. Problem solved.

Hope this helps others.
http://www.pcreview.co.uk/forums/sto...-t3989333.html

That's where i found the solution.

[ssanfu]

Try this as the row source for the combo box:

SELECT Permanent_Records.ID, Permanent_Records.WYO & " - " & Permanent_Records.Well_Name AS [WYO - WellName] FROM Permanent_Records ORDER BY Permanent_Records.ID DESC;

Set the
Bound column to 1
Column Count to 2
Column Widths to 0;1.5 (or greater)

Though do i change it for the combobox and leave it the same in the table.

If you have look up fields , you should think about removing them.
See: http://access.mvps.org/access/tencommandments.htm especially #2
Also follow the link at the end of #2 - "Lookup fields"