Adding Records by Code

[spokane29]

I have a database that I have 2 tables using a One-Many relationship. I have
these in a form and subform, linked by the Primary Key.
What I need to be able to do is on the first of every month is to
automatically create a record for the 1st day of the previous month for each
of the related records. This can be done with either code/query that will
update ALL linked records (preferable), or each individually.
The reason that I need to do this is that for a report that I have I need to
show totals for every record, even if the total is 0. With no record present,
the report generates an error since it is trying to pull information via a
Crosstab Query but the requested data does not exist.
My primary Keys and references are:
tbl1 = RecordID
tbl2 = RecordRef and PostageID

RecordID and RecordRef are linked via the One-Many relationship. PostageID
is the unique record for each of the records in tbl2.
Doing the basics of this is fairly straight forward. The problem that I am
having is that I have a table with subrecords. I need one date appended to
the primary records.
For example:
Table1 | Table2
Record1 create 1 date under this record, and
Record2 create 1 date under this record, and
Record3 create 1 date under this record.

Is this a little more clear? I have been trying several variation, and have
been coming up blank. I had some code suggested to me, but I am not getting
it to work and haven't been able to contact the person that suggested it.
The code is:

If IsNull(Me.txtDate) Then Exit Sub
Dim mDay As Integer, mMonth As Integer, mYear As Integer, s As String

mDay = Day(Me.txtDate)
mMonth = Month(Me.txtDate)
mYear = Year(Me.txtDate)

For mDay = 1 To Day(DateSerial(mYear, mMonth + 1, 0))
s = "INSERT INTO [tblMiscPostage](PostageDate) SELECT #" _
& DateSerial(mYear, mMonth, mDay) & "#;"
Debug.Print s
CurrentDb.Execute s
Next mDay

CurrentDb.TableDefs.Refresh
DoEvents

Me.Requery


Any input or suggestions would be appreciated.

 

[TC]

Not to answer your question, but: this is overkill:
CurrentDb.TableDefs.Refresh

The CurrentDb call automatically refreshes /all/ collections. So you
could actually just say: CurrentDb

In fact, I doubt that you need it at all. You do not need to refresh
the Tables collection, to show new records in the curent form. The
Me.Requery should be enough. Try commenting it out, I doubt that it
will make any difference.

You'd only need to refresh the Tables collection if, for example, you
had added a new /table/, and you wanted to continue using the TableDefs
variable that you had done that through.

HTH,
TC