Insert SQL Statement If a New Record is being Created

[padlocked17]

I have a form where I am entering students and instructors profile information.

I need a way to detect if the record being created is a new entry(Me.NewRecord) and then also dependent on the value of a combo box (Me.cboType), and if so, execute a SQL statement to add one entry into a separate table.

The below code for some reason isn't producing the result I was looking for.

I also don't have a field called "MemberID" being used on the form, but the underlying query has that field in it. How do I use that value in the query without placing a field on the form to populate that data once data has begun to been entered?

The following code is attached a the onClick event of a button:

Thanks All!

    'Save the record
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    'If New Record, Excecute the SQL Statement
    If Me.NewRecord Then
        
        If Me.cboType.Value = "51" Then
            'Excute SQL Statement to Add
            Dim strSQL As String
    
            ' The following is the SQL statement
            strSQL = "INSERT INTO tblEnrollment ( ClassID, MemberID ) " _
                & "SELECT tblClasses.ClassID, tblMembers.MemberID " _
                & "FROM tblClasses, tblMembers " _
                & "WHERE (((tblClasses.ClassID)=1) " _
                & "AND ((tblMembers.MemberID)=11)) " _
                & ";"
            
            ' We will confirm the deletion with a VBA Message Box
            If MsgBox("Would you like to add this new member into a table?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
            With DoCmd
                .SetWarnings False
                .RunSQL strSQL
                DoCmd.SetWarnings True
            End With
        End If 'Closes If Me.cboType
    End If 'Closes If Me.New Record
    
    'Make the Close Button Visible
    Me.cmdClose.Visible = True
    
    'Show Success Message
    Me.lblMemberAdded.Visible = True
    
    Me.cmdSelectMember.Value = ""
    Me.cmdSelectMember.Requery

 

[Guus2005]

Have you tried this:

    'If New Record, Excecute the SQL Statement
    If Me.NewRecord Then
      [COLOR="red"]  
       'Save the record
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70[/COLOR]

        If Me.cboType.Value = "51" Then
            'Excute SQL Statement to Add
            Dim strSQL As String
    
            ' The following is the SQL statement
            strSQL = "INSERT INTO tblEnrollment ( ClassID, MemberID ) " _
                & "SELECT tblClasses.ClassID, tblMembers.MemberID " _
                & "FROM tblClasses, tblMembers " _
                & "WHERE (((tblClasses.ClassID)=1) " _
                & "AND ((tblMembers.MemberID)=11)) " _
                & ";"
            
            ' We will confirm the deletion with a VBA Message Box
            If MsgBox("Would you like to add this new member into a table?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
            With DoCmd
                .SetWarnings False
                .RunSQL strSQL
                DoCmd.SetWarnings True
            End With
        End If 'Closes If Me.cboType
[COLOR="Red"]    else 'Not a new record.

       'Save the record
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70[/COLOR]
    End If 'Closes If Me.New Record
    
    'Make the Close Button Visible
    Me.cmdClose.Visible = True
    
    'Show Success Message
    Me.lblMemberAdded.Visible = True
    
    Me.cmdSelectMember.Value = ""
    Me.cmdSelectMember.Requery

Not sure if this works. If it does, it is because when the new record is saved, Me.NewRecord is false.

If you're using unbound forms, you don't have this problem.