Create/Delete Set of Records (1 Viewer)

[adi55]

Hi,

I have a main form "Invoices" and a subform "Appartments".
When I create a new record in "Invoices" I need to create a new set of 48 records in "Appartmentts". This is the simple part of the problem.
The main problem is that I don't know how to copy all fields values from the last set of 48 records in "Appartments", in the new set. An, of course, when I delete a record in "Invoices" to delete the subsequent set in "Appartments".

Thanks,

 

[jkokkelmans]

To copy the child records use 2 recordsets, rsOldChilds and rsNewChilds, loop through rsOldChilds while adding a record to rsNewChilds, set the fiels of rsNewChilds to rsOldChilds accept the MasterID, this has to be set to the new MasterID of your new Invoice.
After looping through rsIldChilds update the rsNewChilds with .batchUpdate

Set both recordsets to nothing


Deleting all childs can be done by activating the Cascading Delete in the relation between master and child table

 

[adi55]

Something is missing and i don't undrestand what is wrong ? Here is the code:

Private Sub SetNouApartamente()
On Error GoTo Err_SetNouApartamente
Dim gApartamentID, RegC As Long
Dim Count, gNr_apt, gNr_pers, gNr_prez As Integer
Dim gTitular As String
Dim gDebransat As Boolean
Dim rstOldSetAp As Recordset
Dim rstNewSetAp As Recordset

Set rstOldSetAp = CurrentDb.OpenRecordset("select * from tblApartamente;")
Set rstNewSetAp = CurrentDb.OpenRecordset("select * from tblApartamente;")
With rstOldSetAp
.MoveLast
RegC = .RecordCount
gApartamentID = !ApartamentID
gApartamentID = gApartamentID + 1
.Move RegC - 47
MsgBox "TEST POINT.My form contains " & RegC & " records. ", vbInformation, "Record Count"
Do While Not .EOF
gNr_apt = !Nr_apt
gTitular = !Titular
gNr_pers = !Nr_pers
gNr_prez = !Nr_prez
gDebransat = !Debransat
.MoveNext
rstNewSetAp.AddNew
MsgBox "TEST POINT. Inregistrarea " & (.AbsolutePosition + 1) & " Apartament " & qNr_apt & ". ", vbInformation, "Record Count"
Set rstNewSetAp = rstOldSetAp
rstNewSetAp!ApartamentID = gApartamentID
rstNewSetAp.Update
Loop

End With
Set rstNewSetAp = Nothing
Set rstOldSetAp = Nothing

Exit_SetNouApartamente:
Exit Sub

Err_SetNouApartamente:
MsgBox Err.Description
Resume Exit_SetNouApartamente

End Sub

The first message is OK, but no second message, and in the subform window is only one empty record.

 

[Rich]

I'm curious, why would you want to create 48 records for what appears to be one invoice?

 

[adi55]

Rich,

In each month (1 record) i have a number of invoices (gas, water, electricity, etc) which must be distibuted as common, but not equal, expences between 48 appartments in a block of flats. The expences are calculated and distributed regarding the number of persons, surface, aso. So each month (1 rec) are the same (48)apts. with same or/and different field content.

 

[adi55]

Something is wrong with the code. Maybe exist an easyer way to solve the problem Any ideeas...
Pls!

 

[FoFa]

Assuming there is a date in the Apt. rows, or something to identify the last set of rows, I would create a select query to select the last set of 48. Once that is working properly I would create an append query that pulled those last 48 and appended the rows to the table but filling out the new fields (like Date and Invoice number as an example). That's what I would try first.