Requerying A Combo Box

BDW Jr

Registered User.
Local time
Yesterday, 17:29
Joined
May 10, 2009
Messages
18
I need help for a project at work. I'm tired of doing it manually and can't take my co-workers acting as if the world was going to end because I wasn't in to get them a card. I now have some help. A co-worker will be helping me with this but I need some help getting it started.

I created a form (frmParkingPasses) to sign out special assignment parking passes. On the form I have the following fields: Parking Pass, Personnel Name, Date Out and Expected Back. The Parking Pass field is a combo box with values of Park Pass 1 thru 10. All this information is set to be input to a table - tblCards.

What I'm trying to do is have the Parking Pass combo box requery against tblCards (the table the form is attached to). Example: if I sign out Parking Pass 1 to John Doe from January 1, 2009 to January 30, 2009, I shouldn't be able to sign this card out from January 2, 2009 to January 31, 2009. I would like the combo box to show what cards are available. I would also like the form to reject the entry if it is entered manually knowing full well the card is out. Not that I'd ever do it but when you're working with other people, you never know what they'll do. I'm still new to Access so I may ask some silly questions.

Any and all help is appreciated.
 
Maybe if you add one field 'DateIn' you can use the presence, or absence of a value in that field to determine whether or not to allow a check-in or check-out

You would use 'ExpectedBack' simply for informational purposes
Also note the absence of spaces or special characters in table and field names.

tblParkingPasses
- ParkingPass (Integer {1 through 10 or whatever})
- PersonnelName (Text)
- DateOut (Date)
- ExpectedBack (Date)
- DateIn (Date)

I'm guessing you also want to preserve historical data for reporting purposes, correct?
I can post an example if you need, but try to get started with this. Hope it helps.
 
Thanks for your help but I'm not sure I understand your reply. Why would I need a field named DateIn? In my mind DateOut and ExpectedBack should be able to cover it. No? But everything else you have posted is pretty much how I have it set up with the exception of ParkingPass. I have it as a text field and not a number. So the combo box would reading Parking Pass 1, Parking Pass 2 and so on.

Yes, I'm looking preserve information for reporting purposes.

If it's not too much of a hassle could you post an example. I'm one of those people who have to see it in order to understand it. Thanks.
 
The field type of ParkingPass can be whatever you like;
I decided it would be best to make it an Integer because when it comes to building queries and reports, you can use an expression to add "Parking Pass " to the integer, making it display like "Parking Pass 1".

The main advantage of using Integer, is it takes less space to store an integer than it does to store a 13+ digit string.

The way I understood the meaning of ExpectedBack, is:
When a person is checking out a pass, You would ask them "When do you expect to return this pass?"
When that person actually returns the pass, you would enter the date/time in DateIN.

This setup would allow you to run reports on deviation...
"Hey, boss. So and so is always late returning their pass..."
or "Hey, boss. So and so said they were going to return their pass last week and they still haven't checked it back in."
 

Attachments

Last edited:
In the combo box query you can add a NOT IN statment:

Something like:
Select parkingpass
From yourtable
WHERE NOT IN(select parkingpass from this other table)

I know syntax is not correct here but this should keep that parking pass that is checked out out of the combox query.

Larry
 
RossWindows, this isn't really what I was thinking of but it looks great! I need a day or so to look it over before I start asking question. But I'm definitely going to try to make it fit into my database. Your help is REALLY appreciated.
 
RossWindows, I had a few days to look it over and I think I understand what you did. I've figured out how incorporate your sample into my database. Only question I have, and I have a feeling it's going to be something simple, but how did you get the form to keep the focus? Meaning I put it into my database which has other buttons you can click on but when I call up the parkingpasses switchboard I cannot access anything else unless I close the parkingpasses switchboard. How is that done?

Thank you so much for you help.
 
That can be toggled by the form's Modal property.
The form's pop-up property may also be of use to you.
When a form or report opens as a modal window, you must close the window before you can move the focus to another object.
When a form or report opens as a pop-up window it remains on top of all other Microsoft Access windows even it loses focus.
 

Users who are viewing this thread

Back
Top Bottom