RE: Recordset looping (and debug looping!)

Hi Stephanie,

This is a good one. It took a while to find (what I think is) the problem
and come up with a work around.

Issue 1:

I don'tknow what other checks you are doing, but until the record is saved
or canceled, it is still a new record. I would want the Before update code to
run after editing a control to check if a previous validated control was
changed to an invalid entry.


Issue 2:

The problem seems to be when you create a soundex code 'on-the-fly' in a
query and the last name is null, the soundex value is "#Error".

To see this, create a new query using the following SQL:

SELECT Contacts.DonorType, Contacts.LastName, Contacts.FirstName,
Soundex([LastName]) AS Expr1
FROM Contacts
ORDER BY Contacts.LastName;


If there are null last names, you should see the error.


The only way I could get your code to work was to add another field to table
Contacts. I named it "Sndx" with a datatype of String.

Then I ran an update query to add the soundex values.
If your form uses a query for the record source, add the new field to the
query.

I modified the "Form_BeforeUpdate" code you posted. (see below)

** WATCH for line wrap!!
'------------- beg code--------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.NewRecord = True) Then
Dim varID As Variant
Dim rst As DAO.Recordset, strNames As String
Dim gstrAppTitle As String
Dim strSQL As String
Dim sName As String

gstrAppTitle = "Name Check"
' If on a new row,
If (DonorType) = "CU" Or (DonorType) = "IN" Then
If IsNull(Me.LastName) Then
'for CU & IN donor types
MsgBox "For Donor Types 'CU' and 'IN', the Last name
REQUIRED!!", vbExclamation + vbOKOnly
Cancel = True
Else ' Check for similar name

' ******** LName is the name of the control on the form
' that is bound to the LastName field
sName = SOUNDEX(Me.LName)

strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where DonorType <> 'BU' AND Sndx = '" &
sName & "'"
strSQL = strSQL & " ORDER BY LastName;"

' Open a recordset to look for similar names
Set rst = CurrentDb.OpenRecordset(strSQL)

'check for records in recordset
If Not (rst.BOF And rst.EOF) Then
rst.MoveLast
rst.MoveFirst
' If got some similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!LastName & ", " &
rst!FirstName & vbCrLf
rst.MoveNext
Loop
' See if we got some similar names
If Len(strNames) > 0 Then
' Yup, issue warning
If vbNo = MsgBox(" There are members with similar "
& "last names already saved in the database: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & "Are you sure
this member is not a duplicate?", _
vbQuestion + vbYesNo +
vbDefaultButton2, gstrAppTitle) Then
' Cancel the save
Cancel = True
Else
' good name - add soundex code and save record
Me.SndX = sName
End If 'If vbNo
End If 'If Len(strNames) > 0
End If 'If rst.RecordCount > 0
' Done with the recordset
rst.Close
Set rst = Nothing
End If 'If Not IsNull(Me.LastName)
End If 'If (DonorType)
Else
' saves soundex code when editing current record

' ******** LName is the name of the control on the form
' that is bound to the LastName field
Me.SndX = SOUNDEX(Me.LName)
End If 'If (Me.NewRecord = True)

End Sub
'----------end code ----------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



Help, Please!
I have two issues with exisiting code and I can't figure out how to make the
pain end. I've included the NameCheck code snippet below and would really
appreciate some advice.

If I enter a DonorType of "CU" or "IN", I want to run some checks on the
FirstName and LastName. If the DonorType is "BU", I have a company name, but
may not have a FirstName and LastName so I don't want to run NameCheck

1) MINOR: if the person is DonorType "CU" or "IN" and I trigger other
message boxes in the code (such as you forgot to enter the MemberType), the
NameCheck code runs again, I suppose because it's still considered a new
record. Is there anyway to say just run the NameCheck code once?





2) WAY MAJOR: if the person is DonorType "BU", I don't have to enter the
FirstName and LastName. All seems well until I try to enter the next record
as, say, "IN". Then I land in a horrible debug loop where it seems the code
is trying to check the non-existent name in the "BU" record even though I'm
in the "IN" record:

run time error '3464:
Date type mismatch in criteria expression
And when I click on debug, it takes me to this line:
Set rst = DBEngine(0)(0).OpenRecordset("SELECT LastName,
FirstName FROM " & _
"Contacts WHERE Soundex([LastName]) = '" & _
Soundex(Me.LastName) & "'")


---------<snip>----------------------

.

Loading