By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

Duplicate the record in form and subform

P: n/a
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

Jan 28 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.