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

Combo box issue

P: 81
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, TblPNRate.PartNo, tblRiskLevel.RiskLevel, TblPNRate.Rate, TblPNRate.EffectiveDate, TblPNRate.Obsolete
  2. FROM tblRiskLevel INNER JOIN TblPNRate ON tblRiskLevel.RiskId = TblPNRate.RiskID
  3. WHERE (((TblPNRate.Obsolete)=False))
  4. ORDER BY TblPNRate.PartNo;
  5.  
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.
4 Days Ago #1
Share this Question
Share on Google+
13 Replies


PhilOfWalton
Expert 100+
P: 1,257
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
4 Days Ago #2

P: 81
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, 18 views)
4 Days Ago #3

PhilOfWalton
Expert 100+
P: 1,257
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
4 Days Ago #4

P: 81
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.
4 Days Ago #5

PhilOfWalton
Expert 100+
P: 1,257
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
4 Days Ago #6

P: 81
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.
4 Days Ago #7

PhilOfWalton
Expert 100+
P: 1,257
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
4 Days Ago #8

P: 81
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.
4 Days Ago #9

PhilOfWalton
Expert 100+
P: 1,257
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
4 Days Ago #10

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

Thank you again and have a great weekend.
4 Days Ago #11

P: 81
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, 16 views)
File Type: jpg Format Tab.jpg (126.3 KB, 16 views)
File Type: jpg Row Source - Query.jpg (81.7 KB, 16 views)
1 Days ago #12

PhilOfWalton
Expert 100+
P: 1,257
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
1 Days ago #13

P: 81
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.
1 Days ago #14

Post your reply

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