Creating a record in every table with the job number entered into

[Guest]

Hi

What i want is when a record is created in the sales table a record is
created in every other table with the same job no. The databse i am doing
isn't really a relational databse because the information is different for
every job and will only be used once, e.g. there won't be a material that is
used for more than one job.

Can any1 help me, i think it is a really simple problem but i can't remember
how to do it.

Cheers

Danny

 

[Guest]

Hi Danny,

You could try something like the following:

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()
On Error GoTo ProcError

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()

strSQL = "INSERT INTO tblTemp ( CustomerID ) VALUES (" & Me.EmpID & ")"
db.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO tblTemp2 ( CustomerID ) VALUES (" & Me.EmpID & ")"
db.Execute strSQL, dbFailOnError

' Add additional repeating groups of strSQL =
' and db.Execute strSQL, dbFailOnError for additional tables.

ExitProc:
On Error Resume Next
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_AfterInsert event procedure..."
Resume ExitProc

End Sub

********************

This will insert a value in the form (EmpID) into the CustomerID field of
two tables, tblTemp and tblTemp2.

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi

What i want is when a record is created in the sales table a record is
created in every other table with the same job no. The databse i am doing
isn't really a relational databse because the information is different for
every job and will only be used once, e.g. there won't be a material that is
used for more than one job.

Can any1 help me, i think it is a really simple problem but i can't remember
how to do it.

Cheers

Danny

 

[Guest]

PS. The code I gave you requires that a reference be set to the "Microsoft
DAO 3.6 Object Library" for Access 2000/2002/2003 (use the 3.5 Object Library
if you are using Access 97).

Tom
__________________________________________

:

Hi Danny,

You could try something like the following:

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()
On Error GoTo ProcError

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()

strSQL = "INSERT INTO tblTemp ( CustomerID ) VALUES (" & Me.EmpID & ")"
db.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO tblTemp2 ( CustomerID ) VALUES (" & Me.EmpID & ")"
db.Execute strSQL, dbFailOnError

' Add additional repeating groups of strSQL =
' and db.Execute strSQL, dbFailOnError for additional tables.

ExitProc:
On Error Resume Next
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_AfterInsert event procedure..."
Resume ExitProc

End Sub

********************

This will insert a value in the form (EmpID) into the CustomerID field of
two tables, tblTemp and tblTemp2.

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi

What i want is when a record is created in the sales table a record is
created in every other table with the same job no. The databse i am doing
isn't really a relational databse because the information is different for
every job and will only be used once, e.g. there won't be a material that is
used for more than one job.

Can any1 help me, i think it is a really simple problem but i can't remember
how to do it.

Cheers

Danny

 

[John Vinson]

Hi

What i want is when a record is created in the sales table a record is
created in every other table with the same job no. The databse i am doing
isn't really a relational databse because the information is different for
every job and will only be used once, e.g. there won't be a material that is
used for more than one job.

Can any1 help me, i think it is a really simple problem but i can't remember
how to do it.

I must ask:

What is the benefit of having hundreds(?) of records in a table
containing a job number AND NO OTHER INFORMATION?

In my experience, such blank "placeholder" records are more hassle
than benefit. You will find that they very often fail to get filled
in.

Could you amplify on the reason for this? What other tables will be
populated, and what will be done with the (nonexistant!) data in these
tables?

John W. Vinson[MVP]

 

[Guest]

Hi

Well what happens is when we get a new job a sales person will go onto the
sales section and create a new job giving it a job number, a blank record
with this job number will then be created in every other department so the
appropriate ppl can fill them in there isn't always necessarily all the
information for a job but a blank record is needed for the future. As long as
there aren't any blank or wrong records in the sales section it will be ok.
To ensure they dont stay blank when a user from a specific department logs on
all the blanks created will come up allowing them to enter there information.
Make sense?

Danny