change filter to a cascading combo box

[jaymin]

recyan,


"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 ?"

This option would be prefered.
thanks
Peter

[recyan]

I thought, it would be the 2nd option.

Just hold on till some one comes along. Think it needs a bit of VBA for this (1st) option.
Basically, what we want is :

When the Form is freshly opened Record source for cboPPE

cboPPE = "SELECT qryPPE.IDPPE, qryPPE.ChipNumber, qryPPE.type FROM qryPPE WHERE (((qryPPE.IDstaff)=forms!frmMain!cboStaff ) ) GROUP BY qryPPE.IDPPE, qryPPE.ChipNumber, qryPPE.type"

else when it is already open & is getting refreshed

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
WHERE (((tblMaintenance.WashDate)=Date()))
))) GROUP BY qryPPE.IDPPE, qryPPE.ChipNumber, qryPPE.type"

End if

I have modified the Record source from what I had posted earlier ( see 2nd code above). Have brought in the Date Factor.
Would suggest you incorporate this in your cboPPE Record Source & upload the db in its present state. Some one should definitely be able to get you on the way.

Thanks

[June7]

In my suggestion the first 2 queries would be saved Access query objects. Or they could be nested into the third query. Building nested queries can be tricky, especially for novice. I tried to do a NOT IN syntax (which is essentially a nested structure) but couldn't get it right. I like what Recyan has presented.

I, too, was thinking the items should not be available more than once in a day. Will need VBA code to dynamically set the combobox RowSource when record is added. Set the RowSource property to the first sql recyan shows and save the form. Then code in some event will have to reset the combobox RowSource property to the other sql statement. This is the tricky part, figuring out which event. The GotFocus event of combobox won't work. I think the AfterUpdate event of the subform might be best. This worked:

Private Sub Form_AfterUpdate()
Forms!frmMain.cboPPE.RowSource = "SELECT IDPPE, ChipNumber, Type FROM qryPPE " & _
            "WHERE IDstaff=" & Forms!frmMain!cboStaff & " AND IDPPE NOT IN " & _
            "(SELECT IDPPE FROM tblMaintenance WHERE WashDate=Date()) GROUP BY IDPPE, ChipNumber, Type;"
End Sub

[jaymin]

June7, Recyan,
If it will make things alot easier once a day can do, will you still be able to help out on this.

Peter

[recyan]

Have you replaced the Record source for cboPPE with the 2nd code given in post 17 above. If done, I think it should work as per 2nd option.
Note :
It is not question of ease of solving a problem. You need to look how you want things functionally at your end. If it is Option 1, you want, then we should try to achieve that.
The way I looked at it, A garment would not be washed twice on the same day (unless I have not washed it properly the first time) & again, if we allow all PPEID / SerialNumbers to be visible on the same day, then there is a chance of duplicate entry in the tblMaintenance, which we will have to take care of.

Thanks

[jaymin]

Recyan, June7
Yes i have just done it and changed my computer date, and yes it works like a dream, i think that will do, you guys are great to put in the time and effort in helping me do this.
once again thank you very very much have a great day/night.
thanks
Peter

[recyan]

Most
From June and me
Thanks