Formatted AutoNumber field and combo boxes

[neo651]

I've got a table that keeps track of my company's ethernet Switches. It has an autonumber field called DeviceID that is formatted to display as "SW-*".



I also have a table called Switchports that keeps track of the ports on those switches and shows which devices are connected to which ports on the switch.

I created a lookup field in Switchports that pulls from the DeviceID field on Switches. The problem here is that when I add a Switchport and assign it to a specific Switch using the combo box I get an error saying "The value you entered does not match the NUMBER data type in this column."

I understand that the reason this is happening is because lookup fields look up numbers but the value being passed is now text because of the formatting. So the question I have is how do I get my lookup field and my autonumber formatting?

Thank you.

[rpeare]

if you're using a combo box have 2 columns, one is the DEVICEID field from your SWITCHES table, the second "SW-" & deviceID as Device, then make bound column the first column and make it's width 0. Then when you do data entry you will only see SW-1 through SW-x but the data stored will be the numeric DEVICEID field