Help with message box code

[Eleanor of Aquitaine]

I have a table of students and I use a form to add new students. The
drawback to this is that I keep entering new records, not realizing that the
student is already in the table. So I want the form control [Last Name] to
check for a matching record before allowing me to move to the next control.

Table Name: Participants
Table Field: [Last Name]
Table Field: [First Name]

Form Name: ParticipantsForm
Form Control Name; [Last Name]
Form Control Name: [First Name]

So, what I'm looking for is when I enter the last name, and then the first
name in the form, the control:

checks [Last Name] and [First Name] fields in the Participants Table. If
the Last name and first name already have a record in the table, displays a
message box: "There is already a record for this name."

I've figured out how to do similar code when the fields don't match, but I'm
having trouble with this one.

Any help would be appreciated.

 

[John Vinson]

So, what I'm looking for is when I enter the last name, and then the first
name in the form, the control:

checks [Last Name] and [First Name] fields in the Participants Table. If
the Last name and first name already have a record in the table, displays a
message box: "There is already a record for this name."

I'd suggest using the BeforeUpdate event of the Form (you can also use
each textbox's BeforeUpdate event, but that's possibly a hassle since
you'll need to check both textboxes for NULL). Try

Private Sub txtLastName_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone ' get the form's recordsource
rs.FindFirst "[Last Name] = """ & Me.txtLastName & """" _
& " AND [FirstName] = """ & Me.txtFirstName & """"
If Not rs.NoMatch Then
' record found, find out what the user wants to do
iAns = MsgBox("This name already exists. Add it anyway?" _
& vbCrLf & "Select Yes to add the name, " _
& vbCrLf & "No to jump to the existing record, " _
& vbCrLf & "Cancel to go back to editing", _
vbYesNoCancel
Select Case iAns
Case vbYes
Exit Sub
Case vbNo
Cancel = True
Me.Undo ' erase all entries
Me.Bookmark = rs.Bookmark ' jump to the record
Case vbCancel
Cancel = True
End Select
End If
End Sub

John W. Vinson[MVP]

 

[Eleanor of Aquitaine]

John, I can't get it to work. Do I have to modify any of the text in the
code? I copied your code, pasted it in the code for "Before Update", but it
lets me add the record.

So, what I'm looking for is when I enter the last name, and then the first
name in the form, the control:

checks [Last Name] and [First Name] fields in the Participants Table. If
the Last name and first name already have a record in the table, displays a
message box: "There is already a record for this name."

I'd suggest using the BeforeUpdate event of the Form (you can also use
each textbox's BeforeUpdate event, but that's possibly a hassle since
you'll need to check both textboxes for NULL). Try

Private Sub txtLastName_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone ' get the form's recordsource
rs.FindFirst "[Last Name] = """ & Me.txtLastName & """" _
& " AND [FirstName] = """ & Me.txtFirstName & """"
If Not rs.NoMatch Then
' record found, find out what the user wants to do
iAns = MsgBox("This name already exists. Add it anyway?" _
& vbCrLf & "Select Yes to add the name, " _
& vbCrLf & "No to jump to the existing record, " _
& vbCrLf & "Cancel to go back to editing", _
vbYesNoCancel
Select Case iAns
Case vbYes
Exit Sub
Case vbNo
Cancel = True
Me.Undo ' erase all entries
Me.Bookmark = rs.Bookmark ' jump to the record
Case vbCancel
Cancel = True
End Select
End If
End Sub

John W. Vinson[MVP]

 

[John Vinson]

John, I can't get it to work. Do I have to modify any of the text in the
code? I copied your code, pasted it in the code for "Before Update", but it
lets me add the record.

Probably you do. I do not know your fieldnames or tablenames or
control names; I don't even know what "doesn't work".

Please post the field and control names, your actual code, and
describe what happens when you enter a duplicate name.

One thing you can do to test is to set a Breakpoint in the code -
mouseclick in the vertical bar to the left of the code window (it
looks like a scrollbar but isn't); you'll see a brown or red dot, and
the code will stop running at that point. You can then use the Debug
menu (or its keyboard shortcuts, visible when you select the menu) to
step through the code, and you can hover the mouse over variable names
to see what their value is.

John W. Vinson[MVP]