Combo box issue

[DJRhino1175]

This is the SQL behind the Combo Box (Did it this way so you can see the names)

SELECT TblPNRate.PartID
  , TblPNRate.PartNo, tblRiskLevel.RiskLevel
  , TblPNRate.Rate, TblPNRate.EffectiveDate
  , TblPNRate.Obsolete
FROM tblRiskLevel 
  INNER JOIN TblPNRate 
    ON tblRiskLevel.RiskId = TblPNRate.RiskID
WHERE (((TblPNRate.Obsolete)=False))
ORDER BY TblPNRate.PartNo;

I had to have duplicated PartNo so we could keep the old data for the old rate when we make a rate change. So when I put in the PartNo with the new rate I put in a check in the check box to mark it as inactive. With the above SQL or Query filter, it removes it from the list, but the problem I have is when I go back and look at older records the PartNo box will be empty. If I reactivate, (Uncheck the box) it show s up again. Is there a better way of doing this so I don't lose the inactive PartNo/Rate combo and not have it listed in the combo box?

Thanks for your help and wisdom. I only hope one day I can be as helpful to someone on here as you guys have been for me.

[PhilOfWalton]

I think your problem is that yo have "hard coded" the WHERE clause.

It should read something like

WHERE (TblPNRate.Obsolete = Forms!MyForm!MyCheckBox)

I leave you to substitute the correct Form & Check Box Names

You may need in addition

Sub MyCheckBox_AfterUpgate
    MyComboBox.Requery
End Sub

Phil

[DJRhino1175]

Changed the where section per your suggestion

WHERE (((TblPNRate.Obsolete)=[Forms]![SubAsmParts]![Obsolete]))

Now when I run it everything is blank.

Maybe my approach to how I have this combobox set up.

[PhilOfWalton]

Is SubAsmParts is a subform?

Anyway, the WHERE Clause should refer to an Unbound Checkbox on your main form which you set to True or False to select the records you want to see in your Combo Box.

See my original post. Sorry if it wasn't clear.

Phil

[DJRhino1175]

Yes SubAsmParts is a subform.

So I'm going to Make an unbound Checkbox on my main form and add the above sql or make a query in the row source that's going to control what is visible only in the combobox...???

To be honest I'm still a little confused, but will give it a shot and see what happens.

[PhilOfWalton]

I may have misunderstood you, but my understanding is that you want to be able to show either obsolete parts or current parts in your Combo Box.

Therefor you need a method of selecting which type of box, and the simplest method is a Check Box ChkObsolete on your main form. If this is True (Ticked) and assuming TblPNRate.Obsolet is a Yes/No field, we compare that Yes/No field with the Check Box and get only records where TblPNRate.Obsolet is also Tue & vice versa

I should have asked- is the Combo box on the main form or subform?

Phil

[DJRhino1175]

Combo box is on the subform
I only want the combobox to show current(Yes box unchecked in the table)
But I also want it to show the Inactive ones that were selected before they went obsolete/Changed.
Really do not want the users to check or uncheck anything.
Just want the inactive(Ticked yes) to be hidden from the drop down list only.

I hope I made this mud a little clearer...

As always Phil thank you and you're the best for helping this thick headed American.

[PhilOfWalton]

Flattery will get you nowhere.

So in our original posing what exactly did you mean by "is when I go back and look at older records the PartNo box will be empty?

Phil

[DJRhino1175]

In the query I have a field name of Obsolete and in the criteria box I have false, which filters out all the obsolete part numbers, which this part of it is good.

Lets say I go to record 2 of 150, if I mark a part obsolete and go to this record the part number field for that record will be empty. I look in the table and its still there so I'm guessing it is hidden, but it needs to be visible.

[PhilOfWalton]

Exactly, what is the RowSource, ControlSource, Bound Column, No of Columns and Column Widths of your Combo Box?

Possibly two screen shots of the Data Tab & Format Tab in the "Property Box" for the Combo might help, but I also need to see the RowSource.

Phil

[DJRhino1175]

Ok, I'm from work now. I'll gather all of this on Monday

Thank you again and have a great weekend.

[DJRhino1175]

Row Source -

SELECT TblPNRate.PartID, TblPNRate.PartNo, tblRiskLevel.RiskLevel, TblPNRate.Rate, TblPNRate.EffectiveDate, TblPNRate.Obsolete FROM tblRiskLevel INNER JOIN TblPNRate ON tblRiskLevel.RiskId = TblPNRate.RiskID ORDER BY TblPNRate.PartNo;

Control Source - PartID
Bound Column - 1
Column Count - Currently 5 - 0", 1.35", 0", .65", 1"

[PhilOfWalton]

Well the Rowsource shown between the Code tabs

SELECT TblPNRate.PartID, TblPNRate.PartNo, 
tblRiskLevel.RiskLevel, TblPNRate.Rate, 
TblPNRate.EffectiveDate, 
TblPNRate.Obsolete FROM tblRiskLevel 
INNER JOIN TblPNRate 
ON tblRiskLevel.RiskId = TblPNRate.RiskID 
ORDER BY TblPNRate.PartNo;
 

Does not agree with the Row Source -Query.jpg.
There should be the Where Clause as in the QBE example

Phil

[DJRhino1175]

l, TblPNRate.Rate, TblPNRate.EffectiveDate, TblPNRate.Obsolete FROM tblRiskLevel INNER JOIN TblPNRate ON tblRiskLevel.RiskId = TblPNRate.RiskID WHERE (((TblPNRate.Obsolete)=False)) ORDER BY TblPNRate.PartNo; 

My apologies, I forgot I took it off for a temporary fix I put in place till we get a good permanent fix.

[DJRhino1175]

I Still need help with this...

Please and thank you.

[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]

[tblPartEffctvCost]
[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]

DJRhino1175:
Property Sheet
Data: Limit to list:= Yes
Data: Show Only Row Source Values:= No

[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.