Hi,

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...

Thanks

Hi,

   You don't need to base the form on a query. Instead, make a form based on the entire table, and then limit the selections in the combo box by including the date in and time in fields, and then putting "" as the criteria in each field. This way the form is based on the underlying table, and not a query, but you will only be able to select people with badges still out from the combo box.

   When you open this form, you might also open it with criteria, which is not the same as basing it on a query. On the button you use to open the form, put this criteria:

Dim sDoc As String
Dim sDate As String
Dim sLink As String

sDoc = "frmYourBadgeOutForm"
sDate = ""
sLink = "[DateIn] = " & sDate

'May have to be sLink = "[DateIn] = " & "'" & sDate & "'"

then use the blank value as your criteria for opening the form. This will filter the form for records missing DateIn values, and you won't have to filter the combo box as above. Just create a normal record selector.

Also, you should consider combining the date/time fields and using Now() as the default value, instead of a DateIn and TimeIn in seperate fields.

HTH



mmcdonal

Thanks for helping me mmcdonal...

Why didn't I think to use the filter on the form....