Hi,
I moved with my tables to linked tables and everything is working pretty awesome but only one thing.
The duplicate function.
I hope someone can help me.
Set up:
Set new_tbl_main = db.OpenRecordset("factuur") -> main form
Set new_tbl_sub_B = db.OpenRecordset("factuur_1e_regel") -> linked to table "factuur"
Set new_tbl_subsub_BB = db.OpenRecordset("factuur_meerdere_regels") -> linked to factuur_1e_regel
Primarykey on "factuur" = factuurid
Primarykey on "factuur_1e_regel" = Accesskey
Primarykey on "factuur_meerdere_regels" = FRMid
I have a button that copy all related records in "factuur_1e_regel" (related to "factuur")
Then copy all related records in "factuur_meerdere_regels". Those are linked to "factuur_1e_regel"
Before i moved to linked table, everything was fine.
Problem when you push the button
Access copies all the "factuur_1e_regel" -> pretty good
Access copies only the "factuur_meerdere_regels" records and linked them to the copied "factuur_1e_Regel".
So, if there is more records in "factuur_1e_regel" then access only copies the first one and linked those records to the others.
I hope its clear (its diffecult to explain).
Here is my code, i hope someone can help (and understand).
!!linked tables to MySQL!!
I moved with my tables to linked tables and everything is working pretty awesome but only one thing.
The duplicate function.
I hope someone can help me.
Set up:
Set new_tbl_main = db.OpenRecordset("factuur") -> main form
Set new_tbl_sub_B = db.OpenRecordset("factuur_1e_regel") -> linked to table "factuur"
Set new_tbl_subsub_BB = db.OpenRecordset("factuur_meerdere_regels") -> linked to factuur_1e_regel
Primarykey on "factuur" = factuurid
Primarykey on "factuur_1e_regel" = Accesskey
Primarykey on "factuur_meerdere_regels" = FRMid
I have a button that copy all related records in "factuur_1e_regel" (related to "factuur")
Then copy all related records in "factuur_meerdere_regels". Those are linked to "factuur_1e_regel"
Before i moved to linked table, everything was fine.
Problem when you push the button
Access copies all the "factuur_1e_regel" -> pretty good
Access copies only the "factuur_meerdere_regels" records and linked them to the copied "factuur_1e_Regel".
So, if there is more records in "factuur_1e_regel" then access only copies the first one and linked those records to the others.
I hope its clear (its diffecult to explain).
Here is my code, i hope someone can help (and understand).
!!linked tables to MySQL!!
Code:
Private Sub Knop_duplicate_Click()
' New Line
Dim NL As String * 2
' String for display in MsgBox
Dim smsg As String
'**********************************************************
'Declare variables.
Dim db As Database
Dim rec As Recordset
Dim rec1 As Recordset
Dim rec2 As Recordset
Dim new_tbl_main As Recordset
Dim new_tbl_sub_A As Recordset
Dim new_tbl_sub_B As Recordset
Dim new_tbl_subsub_BB As Recordset
Dim new_tbl_subsub_BB2 As Recordset
Set db = CurrentDb()
Set new_tbl_main = db.OpenRecordset("factuur")
Set new_tbl_sub_B = db.OpenRecordset("factuur_1e_regel")
Set new_tbl_subsub_BB = db.OpenRecordset("factuur_meerdere_regels")
'Locate Main record to be copied.
Set rec = db.OpenRecordset("select * from factuur where factuurid=" & Me.AccessID)
'Insert record into Main table.
With new_tbl_main
.AddNew
!FactuurFactuurdatum = Date
!FactuurBedrijfIntern = rec!FactuurBedrijfIntern
!FactuurRelatiecode_klant = rec!FactuurRelatiecode_klant
!FactuurRelatiecode_FN = rec!FactuurRelatiecode_FN
!FactuurRecruiter = rec!FactuurRecruiter
!FactuurBronkandidaat = rec!FactuurBronkandidaat
!FactuurAangemaakt_door = TempVars("gebruikersnaam")
!FactuurBijgewerktOp = Now
!FactuurCreditNota = rec!FactuurCreditNota
!FactuurFactuurOmschrijving = rec!FactuurFactuurOmschrijving
.Update
.Move 0, .LastModified
End With
rec.Close
Dim nieuwfactuur As String
nieuwfactuur = DMax("factuurid", "factuur")
'Insert records into Sub Table B.
With new_tbl_sub_B
'Locate Sub Table B records to be copied.
Set rec = db.OpenRecordset("select * from factuur_1e_regel where Gelinkt_aan_factuurnr=" & Me.AccessID)
If rec.BOF = False Or rec.EOF = False Then
rec.MoveFirst
Do While Not rec.BOF And Not rec.EOF
.AddNew
![Bedrag] = rec![Bedrag]
![Factuurregel] = rec![Factuurregel]
![BTW] = rec![BTW]
![Gelinkt_aan_factuurnr] = nieuwfactuur
.Update
Dim nieuwlink As String
nieuwlink = DMax("Accesskey", "factuur_1e_regel")
MsgBox nieuwlink
'Locate Sub-Sub Table BB records to be copied.
Set rec1 = db.OpenRecordset("select * from factuur_meerdere_regels where FMRGelinkt_aan_factuur_Regel=" & Me!Factuur_ee_regel.Form.AccessKey)
If rec1.BOF = False Or rec1.EOF = False Then
rec1.MoveFirst
Do While Not rec1.BOF And Not rec1.EOF
'Insert records into Sub-Sub BB table.
new_tbl_subsub_BB.AddNew
new_tbl_subsub_BB![FMRFactuurregel] = rec1![FMRFactuurregel]
new_tbl_subsub_BB![FMRGelinkt_aan_factuur_Regel] = nieuwlink
new_tbl_subsub_BB.Update
rec1.MoveNext
Loop
End If
nieuwlink = "0"
rec.MoveNext
Loop
End If
End With
rec.Close
rec1.Close
new_tbl_main.Close
new_tbl_sub_B.Close
new_tbl_subsub_BB.Close
Me.FilterOn = False
'Refreshes the data on the form.
MsgBox ("Kopieëren is geslaagd. De factuur staat in de afvinklijst!")
globals.logging "[form]factuur maken[actie]duplicate[resultaat]succesvol[AccessID]" & Me.FactuurID & "[factuurtitel]" & Me.FactuurFactuurOmschrijving
'**Inserted for Error Handling*****************************
Exit_cmd_Duplicate_All_Click:
Exit Sub
ErrorHandler:
smsg = "An unexpected situation arose in your program."
MsgBox smsg, 16, "LogError()"
Resume Exit_cmd_Duplicate_All_Click
'**********************************************************
End Sub
Last edited by a moderator: