Combo box issue

[zmbd]

DJ,
Let me digest what PhilOfWalton has suggested and read through your posts a second time. I thought PhilOfWalton had you the right track; however, maybe I can explain his idea from a different angle... :)

[Rabbit]

Turn off limit to list. That will at least show the bound value of the items not in the combo box. You can add an unbound textbox with a dlookup if you want to pull a different value to show the user a more descriptive value.

[zmbd]

You beat me to it Rabbit :)

[zmbd]

Thinking about this the root of your issue (IMHO) is that your information isn't normalized enough.

What I am reading is that you have (Simplified form!):
[PK][PartNum][Cost][Effective][...]
[1][abc123][$100][2018-01-01]
[2][abc123][$200][2018-02-02]
[3][abc123][$200][2018-03-03]

When what you may want to consider is:
[tblParts]
[PK][PrtNum][Description]
[1][abc123]
[2][hij456]
[3][xyz789]

[tblPartEffctvCo st]
[PK][FK_tblParts][Cost][Effective]
[1][1][$100][2018-01-01]
[2][1][$200][2018-02-02]
[3][2][$300][2018-03-03]
[4][2][$400][2018-01-01]
[5][2][$500][2018-02-02]
[6][3][$600][2018-03-03]
(once again, I'm only showing the pertinent information)

This should simplify your rowsource as you feed it the SQL for [tblParts] ; however, it may complicate some of your other work.

Otherwise there may be a kludge method by attempting a circular reference within the current record and the on_current event (!!!YUCK!!!) which will become slower and slower the larger your database becomes as the calculation and requery will happen for the entire form-recordset
OR
Do as Rabbit has suggested. In which case you can attempt the before_update event to limit any changes made to the recordset - less kludge but subject to some glitches.

[DJRhino1175]

Rabbit's suggestion didn't work for me, as I need [PK]PartID and a few other fields are used to control 2 of my control's on the form. Risk Level and Rate are dictated by PartID, which I have hidden with a 0" width. I can make all the others go away, but will still need PartID and PartNo in the format tab. With having these 2 it won't let me turn off limit to list.

[zmbd]

cbo

DJRhino1175:
Property Sheet
Data: Limit to list:= Yes
Data: Show Only Row Source Values:= No
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=96 60[/IMGnothumb]
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=96 61[/IMGnothumb]
[yes I'm actually playing with this form... working on an idea that Rabbit gave me in another thread :) ]

NB:
IF user selects new part number AND old part number isn't in list AND saves record
THEN
The user WILL not be able to undo the change and revert to the former value.

You may want to error proof your control in the Before_Update event such that you check the old value against the row-source values:

Logic before_update
Is the former value within the current rowsource
Yes the continue with update
No then prompt user to confirm change because it cannot be undone.

[DJRhino1175]

ZMBD,

This seems to be working, I figured it would be something simple. I will work on the before update code. Not 100% sure at this point, but I believe we will not "ever" update to the older version of that PartID/PartNo, once it changes, but you never know....

Thanks a million for your assistance.