subform based on a query
I got another question. I want to be able to assign temporary badges to persons who lost or forgot their badge at home. I already created a table 'tblbadges' with the fields 'autonumber', 'persons name', 'badge number', 'date out', 'time out', 'date in' and 'time in'.
The 'persons name' field is linked to a table with all the staff's names. The 'badge number' field is linked to a table with all the existing temporary badge numbers. (just to make sure that only valid data can be entered)
A first form is made out of the table 'tblbadges' with only the fields 'persons name', 'badge number', 'date out' and 'time out'. Only the first 2 fields has to be filled in, the other 2 are hidden and filled in automatically.
Now my question is: I would like to create a second form based on the persons who did not return their badges yet. (so the 'date in' and 'time in' fields are still empty) I created a query who works fine, but when I make a form out of this query, nothing works the way I want it to work.
For the query I use all available fields from the table, but I only use some of them on the form.
The second form has the fields 'persons name', 'badge number' (to check if the correct badge is returned), 'date in' and 'time in' fields. On this form again, only the 2 first fields are shown (I don't want anyone the input incorrect data).
I want the form to act like this: I want a combo box (='persons name' field) to show me all the persons that still have a badge. (this data comes form the query) When I select a person in that list, the 'badge number' field has to show me the number of the badge that this persons received. If I then press a 'check out' command button access has to fill in the 'date in' and 'time in' fields with the current date and time for the persons selected only. The form then also automatically updates the data form the combo box (because this person returned his badge, so he does not have to be in the list anymore) (the 'date in' and 'time in' fields do not have the value 'is null' anymore when the query runs again).
The 2 tables will be shown as a subform on another form (just to have the assign/retreive badge on the same screen). So when a person returns his badge it is a subform that has to be updated.
The problem is that I now have to select the right person by using the record selectors and the combo box thing does not work (it changes the name instead of selecting the record). But I would like to hide the record selectors and 'select' the record by using a combo box like I explained before.
I don't know if what I just explained is clear for you and I don't know if it is even possible. If anyone has an idea on this one, please let me know...