VBA Code in Before Update field

[Guest]

Hello,
I was supplied the following code which I am having trouble with:

Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable
Set rs = Me.RecordsetClone ' set it to this form's recordsource
rs.FindFirst "[SSN] = '" & Me!txtSSN & "'" ' is this a dup?
If Not rs.NoMatch Then ' yes it is
Cancel = True ' don't enter the SSN in the table
strMsg = "This SSN already exists!" & _
" Click Yes to go to it, Cancel to retry"
iAns = MsgBox(strMsg, vbYesCancel)
If iAns = vbYes Then
Me.Undo ' erase the data on the form
Me.Bookmark = rs.Bookmark ' move to the record
Else
Me.txtSSN.Undo ' just erase the SSN
End If
End If
End Sub


I am getting an error about like "Macro doesn't exist"
concerning:
Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable

I entered the code into the Before Update field of my SSN properties in form Employee Data Entry, which relates to table Employee Main.

The idea is to enter an SSN into my form, and if it is matches another SSN it will display the matching record (Hopefully in a dialog box?)

This is to prevent duplicates, since SSN's should be unique.

Please remember I'm a Newb.
Jason

 

[Ken Snell]

You don't enter the code in the box next to the BeforeUpdate event in the
Properties window for the textbox txtSSN....instead, in that combo box/text
box next to the event name, select [Event Procedure]. Then click on the
three dot button at far right. Visual Basis Editor will open and will show
you the first (Private Sub etc.) and last (End Sub) lines of the code, with
a blank line in between. Paste all of the code that you have (except for the
first and last lines, which are already in the editor) on that blank line.

Code goes in the module behind the form, not in the box next to the event in
Properties. That box is for the name of a macro or [Event Procedure] (the
latter tells ACCESS that you have code in the module for this event).

--

Ken Snell
<MS ACCESS MVP>

I got that kind of message when I started eliminating/deleting macros and

coverting over to VBA. The code does not refer to a macro, so maybe this
error message that refers to a missing macro is generated by some other
event that occurs before Before Update (maybe Got Focus?).

Try checking all of the events for the control and if you do not locate a

reference to the missing macro there, then re-create the error and post the
exact error message - there may be more clues to the problem there.

You might also try setting a breakpoint on the code so that you can step

through the code and see where the error is happening.

--
rpw

Hello,
I was supplied the following code which I am having trouble with:

Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable
Set rs = Me.RecordsetClone ' set it to this form's recordsource
rs.FindFirst "[SSN] = '" & Me!txtSSN & "'" ' is this a dup?
If Not rs.NoMatch Then ' yes it is
Cancel = True ' don't enter the SSN in the table
strMsg = "This SSN already exists!" & _
" Click Yes to go to it, Cancel to retry"
iAns = MsgBox(strMsg, vbYesCancel)
If iAns = vbYes Then
Me.Undo ' erase the data on the form
Me.Bookmark = rs.Bookmark ' move to the record
Else
Me.txtSSN.Undo ' just erase the SSN
End If
End If
End Sub


I am getting an error about like "Macro doesn't exist"
concerning:
Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable

I entered the code into the Before Update field of my SSN properties in form Employee Data Entry, which relates to table Employee Main.

The idea is to enter an SSN into my form, and if it is matches another SSN it will display the matching record (Hopefully in a dialog box?)

This is to prevent duplicates, since SSN's should be unique.

Please remember I'm a Newb.
Jason

 

[Ken Snell]

I'm not fully understanding?

"I want the dialog box to display only the data of the record with a
matching SSN and an OK button."

Which dialog box? The message box? When is it to be displayed? In place of
the one that now asks the user if he/she wants to go to the already existing
record? Or at some other point?

--

Ken Snell
<MS ACCESS MVP>

I have the following Code:

Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable
Set rs = Me.RecordsetClone ' set it to this form's recordsource
rs.FindFirst "[SSN] = '" & Me!txtSSN & "'" ' is this a dup?
If Not rs.NoMatch Then ' yes it is
Cancel = True ' don't enter the SSN in the table
strMsg = "This SSN already exists!" & _
" Click Yes to go to it, Cancel to retry"
iAns = MsgBox(strMsg, vbYesCancel)
If iAns = vbYes Then
Me.Undo ' erase the data on the form
Me.Bookmark = rs.Bookmark ' move to the record
Else
Me.txtSSN.Undo ' just erase the SSN
End If
End If
End Sub

I erased the "txt"s next to all "txtSSN" since the field is named SSN (It

didn't work at all before that) Now it's to the point where the dialog box
says "It's a duplicate" if it is one, but that's not good enough. I want
the dialog box to display only the data of the record with a matching SSN
and an OK button. I need this because I am using the SSN to prevent
duplicates, and if it has been previously entered I need to verify the
information and write the record number.

The fields which I would like displayed are named exactly: New ID, Last, First, SSN, DOB

As usual, please acknowledge that I am a retard and will need very

explicit instructions.

 

[Guest]

Your instructions were perfect, thanks