Updating two tables

[Digital Carnage]

Hi Folks,
I have a primary table (tblEmployees) which pulls a parking permit #
(text) from a linked Lookup Table called (tblPermit). The tblPermit has
three fields... Permit Number (Primary Key, no dupes), Permit Type and
Status. The three types of status a permit can have is "Available",
"Taken" and "Destroyed". I have a tabbed form that is set to add employees
to the database. I want it so that when I assign a new permit number to a
new employee, it also changes the default status of "Available" to "Taken".
Right now, the form I use is "frmEmployees" and is based directly on
tblEmployees and the combo box source pulls from tblPermit. Can anyone help
me with the kind of query that I need in order to make this happen? Also, I
imagine there's more than just a query involved here, what do I need to do
in the visual basic editor to the Save Record button that executes the
status change. Any help would be great. Thanks in advance. Rich

 

[HSalim]

Why does your tblPermit have predetermined permits?
It appears, from your posting that it could be one of two scenarios.
1. You have a limited number of active permits - say 50. When one gets
destroyed, a new one is created (automatically or manually)
If this is the case, it may make sense to have a predetermined list.

2. You generate and destroy parking permits IDs at will.
In this case, you should add an entry to tblPermits as a new permit is
issued, or an old one is reissued with a new number (invalidating an old
one)

Please post more details

HS