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 that can be put on a button, 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
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 that can be put on a button, 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