Access 2010 Lookup Column Display

[cartotech81]

Tables


MANFACTURER
MODEL
INVENTORY

MANUFACTURER
MAN_NUM
MAN_NAME

MODEL
MODEL_NUM
MODEL_NAME
MAN_NUM (FK)

INVENTORY
INV_NUM
MODEL_NUM

I am trying to create a lookup in the Inventory Table for the MODEL_NUM column. I would like when the user clicks the drop down box they see the choice options as MAN_NAME, MOD_NAME.

Then when someone chooses the option they want it displays the MODEL_NAME in the field. I would actually like it to display a calculated column of MAN_NAME and MODEL_NAME, just not sure that is possible. Below is the code I have. When running a query it displays the data that I want. When looking at in datasheet mode it only shows MOD_NAME. It then displays the MOD_NUM.

SELECT MANUFACTURER.MAN_NAME, MODEL.MOD_NAME
FROM MANUFACTURER INNER JOIN MODEL ON MANUFACTURER.MAN_NUM = MODEL.MAN_NUM
ORDER BY MODEL.MOD_NAME, MODEL.MAN_NUM;

For extra information my properties are as follows:

DISPLAY CONTROL: Combo Box
ROW SOURCE TYPE: Table/Query
ROW SOURCE: Code Above
BOUND COLUMN: 2
COLUMN COUNT: 3
COLUMN HEADS: YES
COLUMN WIDTHS: 0";1.6459";1.3646"

Any help would be greatly appreciated.

[June7]

You have BoundColumn set to the MODEL_NAME column, think should be MODEL_NUM. It is BoundColumn value that will be saved.
Which name is correct: MOD_NAME or MODEL_NAME?

SELECT MODEL_NUM, MAN_NAME & " / " & MODEL_NAME
FROM MANUFACTURER INNER JOIN MODEL ON MANUFACTURER.MAN_NUM = MODEL.MAN_NUM
ORDER BY MODEL.MODEL_NAME, MODEL.MAN_NUM;

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0",2"

[cartotech81]

Sorry about that. It is MOD_NAME and MOD_NUM throughout the db.