423,851 Members | 1,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

Combo box issue

100+
P: 100
This is the SQL behind the Combo Box (Did it this way so you can see the names)
Expand|Select|Wrap|Line Numbers
  1. SELECT TblPNRate.PartID
  2.   , TblPNRate.PartNo, tblRiskLevel.RiskLevel
  3.   , TblPNRate.Rate, TblPNRate.EffectiveDate
  4.   , TblPNRate.Obsolete
  5. FROM tblRiskLevel 
  6.   INNER JOIN TblPNRate 
  7.     ON tblRiskLevel.RiskId = TblPNRate.RiskID
  8. WHERE (((TblPNRate.Obsolete)=False))
  9. 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.
Aug 10 '18 #1
Share this Question
Share on Google+
21 Replies


PhilOfWalton
Expert 100+
P: 1,376
I think your problem is that yo have "hard coded" the WHERE clause.

It should read something like
Expand|Select|Wrap|Line Numbers
  1. WHERE (TblPNRate.Obsolete = Forms!MyForm!MyCheckBox)
I leave you to substitute the correct Form & Check Box Names

You may need in addition
Expand|Select|Wrap|Line Numbers
  1. Sub MyCheckBox_AfterUpgate
  2.     MyComboBox.Requery
  3. End Sub
Phil
Aug 10 '18 #2

100+
P: 100
Changed the where section per your suggestion
Expand|Select|Wrap|Line Numbers
  1. 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.

Attached Images
File Type: jpg QRY-Screenshot.jpg (84.0 KB, 59 views)
Aug 10 '18 #3

PhilOfWalton
Expert 100+
P: 1,376
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
Aug 10 '18 #4

100+
P: 100
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.
Aug 10 '18 #5

PhilOfWalton
Expert 100+
P: 1,376
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
Aug 10 '18 #6

100+
P: 100
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.
Aug 10 '18 #7

PhilOfWalton
Expert 100+
P: 1,376
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
Aug 10 '18 #8

100+
P: 100
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.
Aug 10 '18 #9

PhilOfWalton
Expert 100+
P: 1,376
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
Aug 10 '18 #10

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

Thank you again and have a great weekend.
Aug 10 '18 #11

100+
P: 100
Row Source -
Expand|Select|Wrap|Line Numbers
  1. 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"



Attached Images
File Type: jpg Data Tab.jpg (104.7 KB, 54 views)
File Type: jpg Format Tab.jpg (126.3 KB, 52 views)
File Type: jpg Row Source - Query.jpg (81.7 KB, 60 views)
Aug 13 '18 #12

PhilOfWalton
Expert 100+
P: 1,376
Well the Rowsource shown between the Code tabs
Expand|Select|Wrap|Line Numbers
  1. SELECT TblPNRate.PartID, TblPNRate.PartNo, 
  2. tblRiskLevel.RiskLevel, TblPNRate.Rate, 
  3. TblPNRate.EffectiveDate, 
  4. TblPNRate.Obsolete FROM tblRiskLevel 
  5. INNER JOIN TblPNRate 
  6. ON tblRiskLevel.RiskId = TblPNRate.RiskID 
  7. ORDER BY TblPNRate.PartNo;
  8.  
Does not agree with the Row Source -Query.jpg.
There should be the Where Clause as in the QBE example

Phil
Aug 13 '18 #13

100+
P: 100
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 13 '18 #14

100+
P: 100
I Still need help with this...

Please and thank you.
Aug 15 '18 #15

zmbd
Expert Mod 5K+
P: 5,285
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... :)
Aug 15 '18 #16

Rabbit
Expert Mod 10K+
P: 12,279
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.
Aug 15 '18 #17

zmbd
Expert Mod 5K+
P: 5,285
You beat me to it Rabbit :)
Aug 15 '18 #18

zmbd
Expert Mod 5K+
P: 5,285
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.
Aug 16 '18 #19

100+
P: 100
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.
Aug 17 '18 #20

zmbd
Expert Mod 5K+
P: 5,285
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:
Expand|Select|Wrap|Line Numbers
  1. Logic before_update
  2. Is the former value within the current rowsource
  3. Yes the continue with update
  4. No then prompt user to confirm change because it cannot be undone.
Attached Images
File Type: jpg CBOSettings.JPG (35.5 KB, 29 views)
File Type: jpg ShowRowSrc.JPG (138.8 KB, 31 views)
Aug 17 '18 #21

100+
P: 100
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.
Aug 17 '18 #22

Post your reply

Sign in to post your reply or Sign up for a free account.