[AccessD] Create subform record when form record is created - SOLVED

Many thanks to all that responded. Since the error was only occurring when
the user clicked on the Print button, I used the code below:

Best regards,
Bob


==== START OF CODE SNIPPET ====

Private Sub cmdPrintReport_Click()
On Error GoTo Err_cmdPrintReport_Click

If DCount("EventSectionLeftID", "tblEventSectionLeft", "EventLeftID =" &
EventID) = 0 Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Insert Into tblEventSectionLeft (EventLeftID) VALUES (" &
Me!EventID & ")"
    DoCmd.SetWarnings True
End If
If DCount("EventSectionMiddleID", "tblEventSectionMiddle", "EventMiddleID ="
& EventID) = 0 Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Insert Into tblEventSectionMiddle (EventMiddleID) VALUES
(" & Me!EventID & ")"
    DoCmd.SetWarnings True
End If
If DCount("EventSectionRightID", "tblEventSectionRight", "EventRightID =" &
EventID) = 0 Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Insert Into tblEventSectionRight (EventRightID) VALUES ("
& Me!EventID & ")"
    DoCmd.SetWarnings True
End If

' Set strReportName for Report Type
    Dim strReportName As String
        strReportName = "rptSeatingChart"

' Set strSortOrder for Sort Order
    Dim strSortOrder As String
    
' Set strWhere for Select Records
    Dim strWhere As String, strReportCaption As String
        strWhere = "[EventID] = " & Me!EventID
        strReportCaption = "Seating Chart and Record of Sales"

' Print report
    Dim varOpenArgs As String
        varOpenArgs = "varCaption=" & strReportCaption
    Dim rpt As Report
    DoCmd.OpenReport strReportName, acViewPreview, , strWhere, , varOpenArgs
    Set rpt = Reports(strReportName)
        rpt.OrderByOn = True
        rpt.OrderBy = strSortOrder

Exit_cmdPrintReport_Click:
    Exit Sub

Err_cmdPrintReport_Click:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_cmdPrintReport_Click

End Sub

==== END OF CODE SNIPPET ====
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Gajewski
Sent: Sunday, January 25, 2009 18:38 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Create subform record when form record is created

This question is for an event ticket sales application (with assigned
seating).
 
I have one main table (tblEvents) for the header information (name, date,
time, etc.).
 
I have three section tables (tblEventSectionLeft, tblEventSectionMiddle, and
tblEventSectionRight) for the ticket sales information. This was necessary
due to the number of fields required. This contains the status of each
auditorium seat (A=Available, R=Reserved, S=Sold). Each of the 3 section
tables is related to the main table, with referential integrity for cascade
update and cascade delete enforced.
 
Normal data entry using a form with three subforms works as expected.
 
I also have a report that includes main table information and 3 subreports.
 
My problem is that when I try to print a report but have not yet entered any
data in one or more of the section tables, I get error 2455. This is because
only main table (header) information has been entered; no section table
records exist. But, since all fields in each of the 3 related tables have
default values (they all default to "A"), my question is this -
 
How do I automatically create the 3 related section table records as soon as
a main table record exists?
 
As always, I appreciate any suggestions.
 
Best regards,
Bob Gajewski
 
P Please consider the environment before printing this e-mail 
 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com