retrieving old records & saving them as new ones

[hikaru]

hi all,

I need to create a program in access database, the main form contains 3
subforms. for each month 'Monthly Progress Report for:', the user enters some
details in the main form and the subs. these details should be retrieved
(when the user tries to add a new record and enters a new 'Monthly Progress
Report for:') and stored as new record with the new month stored also in the
subforms.

i hope i explained it well,..

your help is appreciated, Thanks.

 

[BruceM]

Data are stored in tables, not in forms or subforms. If you are talking
about storing the month in the subform record source tables, you should not
try to do that. Establish a relationship between the main form's table and
the tables for each of the subforms. Use the linking field to make the
connection between the main form and the subforms.

You have provided few details, but the general idea is something like this:

tblMain
MainID (primary key)
MainDate
MainText

tblSub
SubID (primary key)
MainID

To create a relationship between the two tables, go to Tools >
Relationships. Add both tables, drag MainID from one table to another, and
click Enforce Referential Integrity when prompted. Use MainID as the Link
Child and Link Master properties of the subform control. (The subform
control is the "container" on the main form that holds the subform.)

Now when you add a record by way of the subform it will be linked to the
main form's record, which includes the date.

 

[hikaru]

thank you for your help, but i think i need the month field in the sub forms
to show the details only for that month. the thing is that i have a many
levels of tables.

here are my tables' relationships:
http://img87.imageshack.us/img87/2991/relationshipsge7.jpg

and my form:
http://img142.imageshack.us/my.php?image=formzb6.jpg

and what i exactly need to do is to allow the user to enter the main
contract details along with the 'Progress Report Month' (eg: August), then
the system should automatically store the same month in tables (tbl_vo,
tbl_obstructios, tbl_letters). You can see in the form that there are 5 VOs
entered, 6 Obs, and 6 Ltrs. if the user clicked "Add New" of the main form,
the details of the previous records should be retrieved along with the VOs,
Obs and Ltrs and stored as new records in their tables but with the month of
(September). Also, if he wants to make any changes in the second record (all
details can be changed, but not the 'Contract No.') , the first record
details should not be affected.. that is why i want to store moth's details
in every table.

I hope I managed to explain it well.

 

[BruceM]

OK, I have a better idea now of what you are doing. You are storing a
specific date in the subform's record source table, not just the month. All
you need to do is display that date as the month and year. For instance,
this expression in an unbound text box will show you the month and year:
=Format([Ltr_Date],"mmm, yyyy")
I can only repeat that since the main form tables seem to be properly
related to the subform tables, all of the information in the main form is
available in the subform.
More to the point, there is no need to store the month in the main form. It
seems you are re-creating the main record each month. Storing the same data
over and over is not good design.
Instead, you could have an unbound text box (txtMonth) on the main form in
which you enter a date such as Aug. 1, 2007. A command button click event
could be:

Dim strMonthLtr as String, strMonthVO as String

strMonthLtr = "SELECT * FROM tblLetters WHERE " & _
"Month(LtrDate) = Month(txtMonth) AND " & _
"Year(LtrDate) = Year(txtMonth)"
strMonthVO = "SELECT * FROM tblVO WHERE " & _
"Month(VODate) = Month(txtMonth) AND " & _
"Year(VODate) = Year(txtMonth)"

Me.fsubLtr.Form.RecordSource = strMonthLtr
Me.fsubVO.Form.RecordSource = strMonthVO

To see all records, another command button could be:

Dim strAllLtr as String, strAllVO as String

strAllLtr = "SELECT * FROM tblLetters"
strAllVO = "SELECT * FROM tblVO"

You could add an ORDER BY clause if you like. There is probably a better
way to put this together, such as allowing the user to select month and year
from two text boxes, or something like that, but this is the general idea.

If you are saying you want to copy the main form and its related records,
there is a method here:
http://allenbrowne.com/ser-57.html

However, I can't understand why you would need to do that. Are letters,
VOs, and OBs the same from month to month? What about the date?

Now for the short answer to your original question (I think). You can add
code to the subform so that if it is a new record you copy the month from
the main form:
If Me.NewRecord Then
Me.LtrMonth = Me.Parent.ProjMonth
End If

I may not have the field names quite right, but that is how it would work.
It is a bad idea, but there it is.

 

[hikaru]

thanks a lot, the link you gave me was very helpful.