I'm new to this forum, so please let me know about and forgive me for any protocol errors.
I am constructing a very simple form based on two linked tables from SQL server. One table is data defining a Work Order(Work order number, date, product, etc...) and the other table is a list of products which can be produced (a uniqueidentifier, item number, description, price, etc...). The form is based on the Work Order table and has a combobox representing the product being produced for this work order. I want to present the user with a list of possible products from the table so that the users sees the item number, but the uniqueidentifier is stored in the table. I've set the row source for the product combobox as:
select products.[index], products.[item number] from products
The bound column is 1, the column count is 2 and the column widths are 0";1"
If I hit the down arrow on the combobox, the list appears correctly. After I select an item, the combobox correctly displays the item number and stores the uniqueidentifier. However, if I navigate to another record and then return, then the combobox text is blank. The uniqueidentifier is still in the table for the record, but the item number associated with this uniqueidentifier is not displayed. It's as if the combobox is trying to display the 0" width bound column.
Any ideas what I have wrong here?
Thanks.
________________________EDIT______________________ ______
The problem seems to be related to the how Access sees the SQL table. The Work Orders table has two uniqueidentifier fields in it, one for the work order and one for the product being produced. when I look at the table structure in Access, the uniqueidentifier for the product is identified as being 'short text'. I'm thinking that Access is not finding a match in the list because it is comparing a 'short text' value to a uniqueidentifier and failing. Any way I can make Access see the second uniqueidentifier for what it is?