Hi,
I am not a programmer but tries anyway to program a feature in a form
where I am able to copy entries for the form and a subform. I have
tried follow the guide here http://allenbrowne.com/ser-57.html, but my
problem is that I have more than one keyfield in the join between the
two tables: Hovedoplysninger and Itemoplysninger. The tree fields are:
"Medarbejder", "Sagsnummer" and "Mandag". I have succeded creating the
new entry in "Hovedoplysninger" but the problem is the append-part
which should copy the items.
My code so far (I am told there are problems in the "INTO-syntax"
Thanks in advance!
Clucking.
Private Sub Kopier_Click()
´On Error GoTo Err_Handler
´Purpose: Duplicate the main form record and related records in
the subform.
´Dim strSql As String ´SQL statement.
Dim lngMedarbejder As Long ´Primary key value of the new
record.
Dim lngSagsnummer As String
Dim lngDato As Date
´Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If
´Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Vælg en record at kopiere."
Else
´Duplicate the main record: add to form´s clone.
With Me.RecordsetClone
.AddNew
!Medarbejder = Me.KopiTil
!Sagsnummer = Me.Sagsnummer
!Mandag = Me.Mandag
!PeriodeID = Me.PeriodeID
´etc for other fields.
.Update
´Save the primary key value, to use as the foreign key for
the related records.
.Bookmark = .LastModified
lngMedarbejder = !Medarbejder
lngSagsnummer = !Sagsnummer
lngDato = !Mandag
If Me.[Itemoplysninger
Underformular].Form.RecordsetClone.RecordCount 0 Then
strSql = "INSERT INTO [Itemoplysninger] ( Medarbejder,
Sagsnummer, Mandag, Indtastningsdato, Jobnummer, Linienummer,
Kundenummer, ManAntal, TirAntal, OnsAntal, TorAntal, FreAntal,
LørAntal, SønAntal, Udføring, Årsag, Antal, Enhed, Ekstraarb,
Godkendelsesflag, Aftaleseddel, AKK, Rekvirent ) " & _
"SELECT " & lngMedarbejder & " AND " & lngSagsnummer & "
AND " & lngDato & " As NwMedarbejder, As NwSagsnummer, As NwMandag,
Indtastningsdato, Jobnummer, Linienummer, Kundenummer, ManAntal,
TirAntal, OnsAntal, TorAntal, FreAntal, LørAntal, SønAntal, Udføring,
Årsag, Antal, Enhed, Ekstraarb, Godkendelsesflag, Aftaleseddel, AKK,
Rekvirent " & _
"FROM [Itemoplysninger] WHERE Medarbejder = " &
Me.Medarbejder & " AND Sagsnummer = " & Me.Sagsnummer & " AND Mandag =
" & Me.Mandag & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no
related records."
End If
´Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub