Updating two tables using a tabbed form.

[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

 

[tina]

well, to get you started...
add code to the form's Save Record button (though i'd give
some thought to the pitfalls of putting vital code on a
button of that sort), as

Private Sub SaveRecord_OnClick()

DoCmd.SetWarnings False
Docmd.RunSQL "UPDATE tblPermit SET Status = 'Taken' WHERE
PermitNumber = " & Me!PermitNumber, False
DoCmd.SetWarnings True

'whatever other code you want to run when record saves.

End Sub

in the SQL statement, Me!PermitNumber refers to the name
of the control in the open form where the new employee's
permit number was entered.

hth