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

Have you tried this:


'If New Record, Excecute the SQL Statement
If Me.NewRecord Then

'Save the record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

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
else 'Not a new record.

'Save the record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
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.