Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 8th, 2006, 05:52 AM
Friend of Wrox
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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...


Old May 9th, 2006, 06:40 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts


   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.


Old May 10th, 2006, 04:38 AM
Friend of Wrox
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks for helping me mmcdonal...

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
populating table from a subform based on a query bjcountry Access 1 April 11th, 2008 06:35 AM
Subform Filter based on Multiselect Combo boxes natwong Access 2 November 16th, 2006 10:05 AM
Filter a subform based on value entered mackoy Access 1 August 25th, 2006 12:27 PM
Urgent!! code for no match in Subform based query smercer Access 3 September 10th, 2004 11:15 PM
Filter Subform B Based on Field in Subform A SerranoG Access VBA 3 June 18th, 2004 12:23 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.