Help on form posting to two tables

[mark]

I have two training tables. Is there a way to post information from one
textbox to the same field on both tables.

ei employee number on form would like it to go to both tables as this links
both of these tables to the employees table.

Some help would be greatly appreciated.

Mark

 

[Guest]

Hi Mark,

The easiest way to do this is by setting up certain relationships between
your tables. If the employee number is a Primary Key in the Emplyees Table,
you can drag the field from there to your training tables. When the
relationship wizard appears check Enforce Referential Integrity and Cascade
Update Related Fields (Cascade Delete Related Records is optional in this
case).

However, sometimes you can't create the relationship like this for various
reasons. In that case you can use either Append Queries or Update Queries.
You would use Append Queries if you are adding a new record (a new row in the
table). You would use an Update Queries if you are modifying an existing
record.

You would use VBA to run your queries like this to update your tables.
In the AfterUpdate event of your Text box or you can use a button. You can
use code like this:


DoCmd.OpenQuery "YourQueryName"

You can turn off the Access Warnings like this.

DoCmd.SetWarnings False ' Turn Access Warnings off
DoCmd.OpenQuery "YourQueryName"
DoCmd.SetWarnings True ' Turn Access Warnings back on

Or you can use the RunSQL method.

Dim strSQL As String
strSQL = "INSERT INTO tblYourTrainingTable.EmployeeNumber
([Forms]![YourEmployeeFormName]![YourTextBoxName])" & _
" SELECT tblYourEmployeeTable.EmployeeNumber,
tblYourEmplyeeTableName.YourPrimaryKeyFieldName" & _
" FROM tblYourEmployeeTable"
" WHERE ((tblYourEmployeeTable.YourPrimaryKeyFieldName) =
[Forms]![YourEmployeeFormName]![YourPrimaryKeyTextBox]);" ' This is an
Update Query
DoCmd.RunSQL strSQL

strSQL = "UPDATE tblYourTrainingTable.EmployeeNumber =
[Forms]![YourEmployeeFormName]![YourTextBoxName])" & _
" WHERE ((tblYourEmployeeTable.YourPrimaryKeyFieldName) =
[Forms]![YourEmployeeFormName]![YourPrimaryKeyTextBox]);" ' This is an
Update Query
DoCmd.RunSQL strSQL

Hunter57
Just huntin' for some data.
http://churchmanagementsoftware.googlepages.com

 

[Joseph Meehan]

I have two training tables. Is there a way to post information from
one textbox to the same field on both tables.

ei employee number on form would like it to go to both tables as this
links both of these tables to the employees table.

Some help would be greatly appreciated.

Mark

Why do you have two training tables? It sounds like you may have a
design error.

 

[John W. Vinson]

I have two training tables. Is there a way to post information from one
textbox to the same field on both tables.

ei employee number on form would like it to go to both tables as this links
both of these tables to the employees table.

As Joseph says, this is probably a design error: how do the two training
tables differ?

It's essentially NEVER necessary to create an empty "placeholder" record. You
can instead use a Form based on your employee table, with two (or, if you
redesign, one!) subform based on the training table, with the EmployeeID as
the master/child link field. When you have data to enter into the training
table, just enter it on the subform; the link will be filled in then. No need
to prefill it!

John W. Vinson [MVP]