Duplicate record multiple times from within subform

[gijane]

Hi guys,

I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".

The code should copy the parent form fields and create x number of duplicate records according to a field on subform. It then runs an appendquery to add the information from the subform.

Can anyone take a look and see if I'm missing something?

Private Sub GuestsInParty_LostFocus()

Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer

If Me.GuestsInParty > 1 Then
partymsg = MsgBox("Is this a group booking?", vbYesNo + vbDefaultButton1, "Alert")

    If partymsg = vbYes Then
 
' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set rst = Me.Parent.RecordsetClone


' Tag property to be used later by the append query.
Me.Parent.Tag = Me.Parent![GuestID]

If Me.Parent.Dirty = True Then
    Me.Parent.Dirty = False
End If

intHowMany = Me.GuestsInParty

If intHowMany > 1 Then
    
    For intCounter = 1 To (intHowMany - 1)
    
    ' Add new record to end of Recordset object.
    With rst
        .AddNew
            Parent![LastName] = Me.Parent!LastNamebox
            Parent![FirstName] = Me.Parent!FirstNamebox
            Parent![PhoneNumber] = Me.Parent![PhoneNumber]
            Parent!Email = Me.Parent!Email
            Parent![CardType] = Me.Parent!CardType
            Parent![CardNumber] = Me.Parent!CardNumber
    
        .Update                     ' Save changes. [COLOR="Red"]Error occurs here[/COLOR]
        .Move 0, .LastModified
        
    End With
    
  
Me.Parent.Bookmark = rst.Bookmark

' Run the Duplicate Order Details append query which selects all
' detail records that have the OrderID stored in the form's
' Tag property and appends them back to the detail table with
' the OrderID of the duplicated main form record.

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "appendquery"
    DoCmd.SetWarnings True
    
   Next intCounter
   
End If
        Me.Parent.Parent!btn1.Visible = True
        Me.Parent.Parent!btn2.Visible = True
    Else
    Exit Sub
 End If
End If

End Sub

Thanks!

 

[Pat Hartman]

I didn't analyze the whole procedure but several things pop out. If you are running code in a subform, the parent CANNOT be dirty so get rid of that code. Access AUTOMATICALLY saves the current record when the current record pointer moves and that includes moving focus to a subform.

The LostFocus event is not the correct event to use. You would never want to run this code unless something was actually changed. Just tabbing through a control shouldn't trigger it so move the code to the control's AfterUpdate event.

.AddNew
            Parent![LastName] = Me.Parent!LastNamebox

Parent! is not part of the name of the field in the recordset. It should be just:
!LastName
The square brackets are redundant unless you have poorly formed names that include spaces or special characters.

Beyond that, I can't imagine why you want a subform to add rows to a parent's RecordSource.