add new record to tables

[Deborah]

Ok, so here's what I want to do...

When the user adds a new record to a table (tblEmployees) via a datasheet form (frmEmployeeEnterGloria2), I would like to somehow automatically create a new record in two other tables (tblEmployeesRegions and tblPayroll) so that the EMPID number entered in the first table (well, form) is automatically entered into the EmployeeID and the PayrollID fields in the additional two tables respectively.

Is there a way to write code in the After Update Event (or wherever else would be more effective - I know NOTHING about VB code) in the EMPID field on my data entry form, (which, as I stated earlier, is based on tblEmployees)?

Did that make sense? Thanks for your help.

 

[Deborah]

Hi there. Thanks for the links. I DO realise that there are better ways to do this but this is a database that has been evolving for the last 5 years and because we are on a fiscal time limit, it is next to impossible to change it right now. I'm simply trying to "fix" it up with as little changes as possible as we need to work around the existing data.

If possible, would you have a tricky way around the rules of database design so that I can accomplish my goal? Thanks SO much for your help.

 

[Deborah]

Moving this up. Please, somebody? Help?

 

[AlanS]

In the form's BeforeUpdate event procedure save the form's NewRecord property to a MODULE-LEVEL Boolean variable, e.g.:

MyBooleanVariable = Me.NewRecord

In the form's AfterUpdate event procedure, place a conditional structure that will execute only if the last update was a new record (as opposed to an update of an existing record), e.g.:

If MyBooleanVariable Then
...
End If

Inside the conditional structure, put the code to create the new records in the other tables. In Access 97, use DAO recordsets; in later versions, you can also use ADO or RDO. For each new record in the other tables, simply assign the EMPID value from the tblEmployees record (or the appropriate form control) to the new record in the recordset for the other table, e.g.:

rstPayroll!PayrollID = Me!txtEMPID

or

rstPayroll!PayrollID = EMPID

 

[Deborah]

Wow, it looks ever so confusing (so beyond me). I'll give it a go and get back to you. Thanks so much for your help.

 

[jfgambit]

Here is some code you can add to the After Update event of the field, or to the On Click event of a command button (i.e. Save Record):

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEmployeesRegions", dbOpenDynaset)

With rst
.AddNew
![EMPID] = Me.EMPID
.Update
End With

Set rst2 = dbs.OpenRecordset("tblPayroll", dbOpenDynaset)
With rst
.AddNew
![EMPID] = Me.EMPID
.Update
End With

Make sure you have the Microsoft DAO 3.0 Object Library "checked" in under TOOLS > REFERENCES in your modules.


But, please keeo in mind what Pat had posted. You may be giving yourself more problems in the future by not Normalizing your database.

HTH