Auto add to table

[Guest]

I am a complete novice to access, so apologies for the simplicity of the
question.
I have 2 tables.
Table one contains a list of names etc.
Table 2 contains many columns but the second column contains numbers 1-31,
entries per name (table 1)
Is there a way that when I add a new entry to table 1, it will automatically
create a additional 31 records (numbered 1-31) in table 2?
i hope this makes sense

 

[Guest]

What you're asking for might be a little advanced. First of all, you can't
really automate anything if you directly enter records into a table. You'll
need to create a form based on your table and perform inserts there. The form
will allow you to automate things like table inserts.

To do the insert, I would recommend looking into Append queries. The hard
part for you will be to increment the 1-31 thingy, but I would take it one
step at a time.

Barry

 

[Pieter Wijnen]

In the AfterInsert Event in the form U can put
(select Properties /Event / After Update Event / "builder" / Event
Procedure)


'--------------------------------------------------

Sub Form_AfterInsert()
Dim Db AS DAO.Database
Dim QDef AS DAO.QueryDef
Dim i AS Long
Dim SQL AS String

Set Db = Access.CurrentDb ' Pointer to DB
SQL="PARAMETERS pNAME TEXT, pVal Long;" & VBA.VbCrlF & _
"INSERT INTO TABLE2 ([NAME],Field2) VALUES (pName,pVal)"
Set Qdef = Db.CreateQueryDef(VBA.vbNullString,SQL) ' Creates a temporary
Query to insert records

Qdef.Parameters("pName").Value = Me("Name").Value ' Retrieve the Value of
the Forms Control "Name" & Assign to Query Parm
For i = 1 To 31
Qdef.Parameters("pVal").Value = i
Qdef.Execute DAO.DbSeeChanges 'Insert one row of data
Next ' Loop 1..31
Set Qdef = Nothing
Set Db = Nothing ' Cleanup Pointers
End Sub

'-------------------------------------

PS Change Field & Control Names to whatever U Use
HTH

Pieter

 

[Joseph Meehan]

I am a complete novice to access, so apologies for the simplicity of
the question.
I have 2 tables.
Table one contains a list of names etc.
Table 2 contains many columns but the second column contains numbers
1-31, entries per name (table 1)
Is there a way that when I add a new entry to table 1, it will
automatically create a additional 31 records (numbered 1-31) in table
2?
i hope this makes sense

It would appear that properly have one table to the individual and then
as second related table for some sort of event(s) that take place on a daily
bases. Right now you appear to be recording the data on a monthly bases.
Maybe it might be better to record those events as the happen and record the
actual date they occur. That way you will not have blank records created,
and you will be able to query for a month, a week or any time period you
might need or want. No need to delete older data and you can have that for
reference.

Also I get suspicious when someone has "many columns" That may be a
candidate for additional normalization. Also remember that databases don't
have columns, they have fields that may be displayed in columns. Thinking
in terms of columns and not fields tends to make one not notice that the
data needs to be normalized.

Good Luck

 

[Guest]

Pieter,
Thank you

In the AfterInsert Event in the form U can put
(select Properties /Event / After Update Event / "builder" / Event
Procedure)


'--------------------------------------------------

Sub Form_AfterInsert()
Dim Db AS DAO.Database
Dim QDef AS DAO.QueryDef
Dim i AS Long
Dim SQL AS String

Set Db = Access.CurrentDb ' Pointer to DB
SQL="PARAMETERS pNAME TEXT, pVal Long;" & VBA.VbCrlF & _
"INSERT INTO TABLE2 ([NAME],Field2) VALUES (pName,pVal)"
Set Qdef = Db.CreateQueryDef(VBA.vbNullString,SQL) ' Creates a temporary
Query to insert records

Qdef.Parameters("pName").Value = Me("Name").Value ' Retrieve the Value of
the Forms Control "Name" & Assign to Query Parm
For i = 1 To 31
Qdef.Parameters("pVal").Value = i
Qdef.Execute DAO.DbSeeChanges 'Insert one row of data
Next ' Loop 1..31
Set Qdef = Nothing
Set Db = Nothing ' Cleanup Pointers
End Sub

'-------------------------------------

PS Change Field & Control Names to whatever U Use
HTH

Pieter

I am a complete novice to access, so apologies for the simplicity of the
question.
I have 2 tables.
Table one contains a list of names etc.
Table 2 contains many columns but the second column contains numbers 1-31,
entries per name (table 1)
Is there a way that when I add a new entry to table 1, it will
automatically
create a additional 31 records (numbered 1-31) in table 2?
i hope this makes sense