Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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...


Reply With Quote
  #2 (permalink)  
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.


Reply With Quote
  #3 (permalink)  
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....

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 05:56 PM.

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