Hello all. This is a convoluted issue I am having.

I am having trouble with a combo box. I have an unbound control that is set up to retrieve a record from a query I set up in the combo box. The query is set up to give the user a list of customer names and serial numbers. When the combo box is used it shows both columns and is bound to the serial numbers.

The underlying table that these values are pulled from is a linked table that I am connecting to through ODBC. (An inventory/billing program called Max) The Serial Number is a primary key but duplicates are allowed. Which is causing me issues.

What happens is that the serial number may be assigned to a customer, lets say Serial Number 123, Customer A. If Customer A changes his mind that serial number is released and can be reused for another customer, Customer B. Well the db keeps both Customers assigned to that serial number so that there is a history for accounting purposes. I have corrected our ordering folks from re-using the serial numbers, but I have a few records where this is an issue.

When I go through and pick a Serial Number, the combo box grabs the first customer listed with that number. Customer A, when I really want Customer B. The same thing happens in reverse when I try to use the combo box to pick a customer it picks the first Serial Number listed for that customer. Since neither is unique, it is giving me a fit.

I have scoured through the table and there just aren't any unique values I can bind to.

I also have the scenario where there may be more then one serial number for the customer, so I can't use the customer ID.

I am not comfortable adding another column to it for concern over problems with the db that is using that table.

I have also tried to add another table to the query and seem to be running into referential integrity issues, where I can't edit any of the records.

Does anyone have any thoughts on how I might be able to grab the correct information through my combo box?

I know you don't want to add any more columns, but would a Yes/No Inactive field be useful to you. You could then have the combo box only display those records that are Inactive = False.

HTH.

I thought about adding a field like that, but I can't add any to the tables that I currently am using. (They are linked through an ODBC linkup and I am concerned that my external db might have issues if I add a record field)

You mentioned you do have a CustomerID field, but that this may be linked to more then one serial number. I assuming that this CustomerID is unique. Do you have a date when the serial number was entered? You could perhaps have access only display the most recent one. However, I'm going to bet that you already thought of this and it won't work.

I'm sorry, but I can't think of anything else.

Sorry.

This is like an onion, a lot of layers here. I did check the date/time stamps which was a field in the table, BUT, it puts the same date time stamp for the transaction. Not sure why.

Thanks for asking, I appreciate the dialog.

I am kind of at a loss here, and was trying to get some ideas going from people other then myself. Sometimes we miss an easy solution.

Reading your post is sounds like the customer names and serial numbers combination
is/are unique. if so could you set two text boxs in the form to recieve data on the click event
of the drop down box like this:

Me.Text2 = Me.Combo0.Column(0)
Me.Text4 = Me.Combo0.Column(1)

Then use these text boxes in your query for the correct data.

SELECT Main_Data.API_Number, Format([Spud_Date],'mmm yyyy') AS Monthiss
FROM Main_Data
WHERE (((Main_Data.API_Number)=[forms]![form1].[text2]) AND ((Format([Spud_Date],'mmm yyyy'))=[forms]![form1].[text4]));

I just used dummy data here, but it worked. of course your users would have to know the correct
name and number for an entity. this is the best I could come up with.
I may have missed something here, so if i'm stepping out of line SORRY.
DBear