Lookup Box Issues - Selecting one thing but displaying another

[manix]

Hi All,

I have a lookup list in a field of a table. It is used on a form and contains a list of two field from another table. It is simply a text field and a number field.

When the user clicks on the lookup list they see the test and associated number fields in the list. They click on the appropriate field and it will display the text, but record the number. Great, except some text may have the same numbers associated, and therfore if a text field has the same number associated, it displays the text with the same number higher up the alphabetical list. Hope I explained that OK!

Here is a screenshot if not.

Anyone advise how I can simply have the text selected displayed whilst still storing the number?

PS: this site is painfully slow now!

 

[RuralGuy]

Might I suggest you read the following link? The Evils of Lookup Fields in Tables

 

[manix]

Might I suggest you read the following link? The Evils of Lookup Fields in Tables

Noooooooooooooooooooooooooooooooooooooooooo!

Thanks, does this mean there is no specific to solution to this conundrum!?

Another way of looking at it is:

Is there a better way to do the same thing. I want a table of Issues with related Demerit Marks.

On a separate form I want the user to be able to select an specific issue and report the demerit value so I can use the demerit value at a later reporting stage. A supplier may have many deliveries and therefore multiple issues and I want to be able to SUM the demerit marks later on so as to grade that supplier.

 

[RuralGuy]

Putting a ComboBox on a form for this task is just fine. The point of the article is not to put the Lookup Field in the table. Users should not be looking directly at your tables anyway. As for your dilemma, for a lookup to work as you wish it needs a unique number to reference.

 

[Pat Hartman]

Both the description and the demerit value need to be stored in your table. You cannot do this at the table level. The combo should select both the text and the number. The text field should be the bound field since that seems to be the unique field. In the AfterUpdate event of the combo, you want to copy the demerit value to the demerit field in the table:

Me.Demerit = Me.cboDesc.Column(1) <---- (1) is the second column in the table since the columns collection is a zero-based array.

For those of you wondering why the demerit value should be stored it is for the same reason that unitPrice is stored in the OrderDetails table of an order entry application. You need the value that was applicable at the time the record was created.

 

[manix]

Thanks Pat,

Your solution didn't seem to help, I think I was overcomplicating things as I failed to mention the lookup list was derived from a query acting as a filter and this seemed to make things worse!

So I simply setup a new table, with the items and demerit marks, setup an unbound combo box to draw these values and then return the selected demerit mark in the demerit field of the table (thanks Rural Guy, no more lookup fields in the table). I now have the suppliers listed with relevant demerit marks that can be summed and reported!!! Excellent.

Thanks for your help guys, much appreciated.

 

[manix]

Thanks Pat,

Your solution didn't seem to help, I think I was overcomplicating things as I failed to mention the lookup list was derived from a query acting as a filter and this seemed to make things worse!

So I simply setup a new table, with the items and demerit marks, setup an unbound combo box to draw these values and then return the selected demerit mark in the demerit field of the table (thanks Rural Guy, no more lookup fields in the table). I now have the suppliers listed with relevant demerit marks that can be summed and reported!!! Excellent.

Thanks for your help guys, much appreciated.

***CAUTION*** RANT TO FOLLOW:

Spoke too soon. My above solution works up until the point that you save the form .

I may have to look at you solution again though Pat, but I don't know how you get a combo to select both the text and the number? What am I missing?

I have now set things up to use an unbound combo field in the form and hopefully want to record the demerit number in the table. So no combo in the table.

Oh and I also forgot to mention that the table that provides the text and number fileds for the lookup does have a Key autonumber field. This a unique reference for each item in the lookup. SO WHY IS THIS STILL HAPPENING!!!!!!

Help this is not a good vibe for a Friday afternoon!!!

 

[RuralGuy]

It would be very helpful if you could post enough of your db so we could look at the problem. Remove any sensitive data but leave enough sample data to demonstrate the problem.