change filter to a cascading combo box

[jaymin]

Hope someone can help, I have a form with 3 cascading combo boxes with a subform to enter data, the third combo will only ever have four options. what happens is you select an option from the third combo box and fill in the data for it, you can select the exit button to exit the form or add another record which requeries the third combo and you can select another option and enter data for it and so on.


What i want to happen is when an option from the third combo box is used and data entered for it, that option is unable to be used again, my prefered way would be to remove it from the selection criteria, so when you make a selection and enter data and want to add another record you will only have a choice of three, then two then one then none.
if that is not possible can you suggest another or better way.

Peter

[June7]

It can be done. Requires the RowSource to be a query that joins the 'lookup' table with the data table with filter criteria that will exclude values that are in the data table.

[jaymin]

June7,
Thanks for your reply, but i am fairly new to access, and your reply is a bit above me, is there an easier way to explain it, in steps.
thanks for you time and help.
I have included the data base if that will help.
Peter

[June7]

Try this SQL for the cboPPE RowSource:
SELECT tblPPE.IDPPE, tblPPE.ChipNumber, tblGarment.type, tblPPE.IDstaff
FROM tblStaff RIGHT JOIN ((tblGarment RIGHT JOIN tblPPE ON tblGarment.IDGarment = tblPPE.Garment_Type) LEFT JOIN tblMaintenance ON tblPPE.IDPPE = tblMaintenance.IDPPE) ON tblStaff.IDStaff = tblPPE.IDstaff
WHERE (((tblPPE.IDstaff)=[forms]![frmMain]![cboStaff]) AND ((tblMaintenance.IDPPE) Is Null));

Then will have to requery the combobox after record is committed to tblMaintenance or when the combobox gets focus might be good enough.

[jaymin]

June7

SELECT tblPPE.IDPPE, tblPPE.ChipNumber, tblGarment.type, tblPPE.IDstaff
FROM tblStaff RIGHT JOIN ((tblGarment RIGHT JOIN tblPPE ON tblGarment.IDGarment = tblPPE.Garment_Type) LEFT JOIN tblMaintenance ON tblPPE.IDPPE = tblMaintenance.IDPPE) ON tblStaff.IDStaff = tblPPE.IDstaff
WHERE (((tblPPE.IDstaff)=[forms]![frmMain]![cboStaff]) AND ((tblMaintenance.IDPPE) Is Null));

Then will have to requery the combobox after record is committed to tblMaintenance or when the combobox gets focus might be good enough.

At the moment i believe that this SQL is pointing to the garment type, eg coat or trousers, what i want it to point to is the Chip Number, so would the code change to this instead.

SELECT tblPPE.IDPPE, tblPPE.ChipNumber, tblGarment.type, tblPPE.IDstaff
FROM tblStaff RIGHT JOIN ((tblPPE RIGHT JOIN tblPPE ON tblPPE.IDPPE = tblPPE.ChipNumber) LEFT JOIN tblMaintenance ON tblPPE.IDPPE = tblMaintenance.IDPPE) ON tblStaff.IDStaff = tblPPE.IDstaff
WHERE (((tblPPE.IDstaff)=[forms]![frmMain]![cboStaff]) AND ((tblMaintenance.IDPPE) Is Null));

Peter

[jaymin]

I have almost got your code working, but when it loads in first time i loose one of my serial numbers, i see three when there should be four use name "Christiansen" on Gold Coast as test, when record is selected it is filtered out for the add record, which is good but when the form is closed down and reopened i need all serial numbers to be able to be viewed again.
The idea is that all staff are issued with two sets of PPE, 2 Coats and 2 Trousers, so there will only ever be 4 items available, they are washed, repaired etc, usally as a set, two items but not necessary, so they do not select the same item by mistake and rerecord it, i want that serial number of the PPE not to be able to be selected. but a couple days might go by and they need to wash them again i want all the serial numbers visible again, hope this helps

Peter

[June7]

Try this for the combobox RowSource:
SELECT DISTINCTROW tblPPE.IDPPE, tblPPE.ChipNumber, tblGarment.type
FROM tblGarment RIGHT JOIN (tblPPE LEFT JOIN tblMaintenance ON tblPPE.IDPPE = tblMaintenance.IDPPE) ON tblGarment.IDGarment = tblPPE.Garment_Type
WHERE (((tblPPE.IDstaff)=[forms]![frmMain]![cboStaff]) AND ((tblMaintenance.WashDate)<>Date() Or (tblMaintenance.WashDate) Is Null));

[jaymin]

June7,
Thanks for all your hard work, i have tried the SQL, most of it works fine except when you close the form down and reopen it the PPE are still not visible, but i have a thought on that, i am at work using a works computer and cannot change the date, so when i go home i will try it on my computer and change the date, i am betting that all will be ok, what do you think?
so i have to wait till i go home to try, i will let you know.

I want to thank you so very very much for your time, effort and knowledge in helping me with this little problem.

Peter

[June7]

I am having the opposite issue. All four options remain available. I got it so they would display and didn't test saving records. So I still don't have the query right. Turns out to be a lot more complicated. The following work sequence of queries worked. Remember, need code to requery the combobox. I used the GotFocus event of the combobox.

Query1
SELECT tblPPE.IDPPE, tblPPE.ChipNumber, tblGarment.type, tblPPE.IDstaff
FROM tblGarment INNER JOIN tblPPE ON tblGarment.IDGarment = tblPPE.Garment_Type
WHERE (((tblPPE.IDstaff)=[forms]![frmMain]![cboStaff]));

Query2
SELECT tblPPE.ChipNumber, tblMaintenance.WashDate, tblPPE.IDstaff
FROM (tblGarment INNER JOIN tblPPE ON tblGarment.IDGarment = tblPPE.Garment_Type) INNER JOIN tblMaintenance ON tblPPE.IDPPE = tblMaintenance.IDPPE
WHERE (((tblMaintenance.WashDate)=Date()) AND ((tblPPE.IDstaff)=[forms]![frmMain]![cboStaff]));

Query3 (RowSource for combobox)
SELECT Query1.IDPPE, Query1.ChipNumber, Query1.type, Query1.IDstaff
FROM Query2 RIGHT JOIN Query1 ON Query2.ChipNumber = Query1.ChipNumber
WHERE (((Query2.ChipNumber) Is Null));

[jaymin]

June7

I am having the opposite issue. All four options remain available. I got it so they would display and didn't test saving records. So I still don't have the query right. Turns out to be a lot more complicated. The following work sequence of queries worked. Remember, need code to requery the combobox. I used the GotFocus event of the combobox.

Query1
SELECT tblPPE.IDPPE, tblPPE.ChipNumber, tblGarment.type, tblPPE.IDstaff
FROM tblGarment INNER JOIN tblPPE ON tblGarment.IDGarment = tblPPE.Garment_Type
WHERE (((tblPPE.IDstaff)=[forms]![frmMain]![cboStaff]));

Query2
SELECT tblPPE.ChipNumber, tblMaintenance.WashDate, tblPPE.IDstaff
FROM (tblGarment INNER JOIN tblPPE ON tblGarment.IDGarment = tblPPE.Garment_Type) INNER JOIN tblMaintenance ON tblPPE.IDPPE = tblMaintenance.IDPPE
WHERE (((tblMaintenance.WashDate)=Date()) AND ((tblPPE.IDstaff)=[forms]![frmMain]![cboStaff]));

Query3 (RowSource for combobox)
SELECT Query1.IDPPE, Query1.ChipNumber, Query1.type, Query1.IDstaff
FROM Query2 RIGHT JOIN Query1 ON Query2.ChipNumber = Query1.ChipNumber
WHERE (((Query2.ChipNumber) Is Null));

Not sure where to put all this code, q3 in row source of combo box but where to put q1 and q2.
Peter

[recyan]

Till June's back, Not sure if this is what you are looking for,
Just check out if below gives some guidelines:


Add below code to the Row Source of cboPPE

SELECT qryPPE.IDPPE, qryPPE.ChipNumber, qryPPE.type FROM qryPPE WHERE (((qryPPE.IDstaff)=forms!frmMain!cboStaff ) AND (qryPPE.IDPPE NOT IN (SELECT TblMaintenance.IDPPE FROM tblMaintenance))) GROUP BY qryPPE.IDPPE, qryPPE.ChipNumber, qryPPE.type;

AND

Refresh to

Private Sub add_Click()
On Error GoTo Err_add_Click
Refresh
If Me.cboPPE.Enabled = yes Then
Me.cboStaff.Enabled = False
Me.cboStation.SetFocus

Else
    Me.cboPPE = Null
    Me.cboPPE.SetFocus
Exit_add_Click:
   End If
   Exit Sub

Err_add_Click:
    MsgBox Err.Description
    Resume Exit_add_Click
  
End Sub

Thanks

[jaymin]

Recyan,
Thank you for the code, it works a dream except that after you exit the form and reopen it again it should be reset so that all serial numbers can be viewed, at the moment after you have selected the serial numbers they stay hidden, is there a way to reset the SQL when the form reopens.
thank you for you help
Peter

[recyan]

Hi,
The logic I have used is, that if IDPPE exists in tblMaintenance, then it should not be available again for that particular Staff.
If the logic is wrong, then we will have to re-look at what I have suggested.
By reset, if you mean all the Serial Numbers should be available once again, then are you indicating that it is a 2nd round of washing for all the staff garments or ????
Sorry, first need to understand the functionality of what is happening before any suggestions, if I can come up with.

Thanks

[jaymin]

recyan,
Thank you for you quick reply, yes I mean "all the Serial Numbers should be available once again, then are you indicating that it is a 2nd round of washing for all the staff garments."
I am a fire fighter, when our PPE (personal protective equipment) gets dirty we have to wash it and record what we did to it, wash, repairs etc, i am trying to make the data base as idot proof as i can, usally they are washed in pairs, coat and trousers but not always, so if the PPE that has been recorded and is not visible they are unable to reinter that PPE. but if after a couple of days he washes the same PPE it needs to be visible.
I have tryed your code it seems to work fine, except that when you close down the form the filter still hides the PPE, if this could reset to show all PPE when the form opens again it would be what i am after.
Hope i have made it a bit clearer
Peter

[recyan]

Caught me on the way out, All the same,

I now understand the need for all the PPE being available on re-opening the Form.
However,
Suppose, we enter a PPE for a particular staff in the morning & then close the Form.
After, say 2 hours or at end of the day, we open the Form, should All the PPE's be available again for that Staff ?
Or
Should All the PPE's become available the Next day ?

Thanks