Combo Box (Don't show value in selection if already in use)

[spartansFC]

Hi

I'm trying to add a parking permit form to my database, each person is given a unique parking permit number which can be handed back in and then handed out to another person.
If a parking permit is already in use i don't want the combo box to show it, i can't think of a way to do it.

The steps are:

[ul]
[li]Main form[/li]
[li]User clicks "Add New Parking Permit[/li]
[li]a form pops up[/li]
[/ul]

I know how to do the above steps, it's the combo box where i get stuck

I have currently.

tblDropDownParkingPermitNumbers
lngParkingPermitNoID - Autonumber
strParkingPermitDesc - Short Text

tblParkingPermits
lngParkingPermitID - Autonumber
lngPeopleID - Foreign Key
lngParkingPermitID - Number

tblParkingPermits is the record source for frmParkingPermits

I did think of having a status "Standard Use" or "Expired or Cancelled"

In the combox selection on the parking permit form, have something like:

SELECT tblDropdownParkingPermitNumbers.lngParkingPermitNoID, tblDropdownParkingPermitNumbers.strParkingPermitDesc
FROM tblDropdownParkingPermitNumbers
WHERE (((Status)="Expired or Cancelled"))

The problem really is how do i assign a status to each parking permit number initially so that there all accessible

Thanks

Michael

 

[Andrzejek]

Just guessing here, but you may want to have a Status table with something like:

[pre]
tblStatus
ID Description
0 Active
1 Available
2 Expired
3 Cancelled
4 ...[/pre]

And if you assign a Status to your PermitNo, it will be easy to retrieve what you want.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[spartansFC]

I think you're right that i do need to add a status to each PermitNo but i'm still not sure how that would work or where do i add the status.

When a user is entering a new parking permit, they select the permit no via a combo box, then i somehow have to attach the status there

I'm so confused right now but i think i've worked out a solution

 

[Andrzejek]

>When a user is entering a new parking permit, they select the permit no via a combo box

So, you do have a list of parking permits somewhere. And it looks to me that some would have a Status of 1 - Available, right? The ones not assigned to anybody. That would make sense since you probably have a limited number of parking permits that you can issue.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson