rst.movenext

  • Thread starter Thread starter JK
  • Start date Start date
J

JK

Could someone take a look at this code and maybe locate why I'm getting error
on the rst.movenext command? This is from the Law Track example and it is
supposed to give the user an error if a duplicate contact is entered... I
just get an error message saying it's too complex to evaluate.

Thx!!!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, strNames As String
' If on a new row,
If (Me.NewRecord = True) Then
' Check for similar name
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for similar names
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"Employees WHERE Soundex([LastName]) = '" & _
Soundex(Me.LastName) & "'")
' If got some similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!LastName & ", " & rst!FirstName &
vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we got some similar names
If Len(strNames) > 0 Then
' Yup, issue warning
If vbNo = MsgBox("Service Department Database found contacts
with similar " & _
"last names already saved: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & "Are you sure this contact is not a
duplicate?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle)
Then
' Cancel the save
Cancel = True
End If
End If
End If
End If
End Sub
 
On Tue, 12 Feb 2008 07:54:01 -0800, JK <[email protected]>
wrote:

You get that "too complex" error message on the rst.MoveNext line?
That seems strange. If at all I would expect it on the OpenRecordset
line.
Temporarily take out the Soundex part, and see if the problem goes
away:
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"Employees WHERE LastName = '" & _
Me.LastName & "'")

Btw, Soundex is a really crude way to look for similars. Personally I
like the Ratcliff-Obershelp algorithm, although it too has its
limitations.

-Tom.
 
I commented out the section you reccomended I remove and now I'm getting an
error on - Do Until rst.EOF

I really like the idea of being able to check to see if a contact or
customer already exists when entering a new contact or customer. I just can't
seem to be able to get this to work.

I'm not a programmer; I do the best I can with the examples people provide
and usually I do ok. With this example, I'm stumped. I'd hate to remove this
code all together - but I guess I might have to huh?

Thx for your help.

Jason

Tom van Stiphout said:
You get that "too complex" error message on the rst.MoveNext line?
That seems strange. If at all I would expect it on the OpenRecordset
line.
Temporarily take out the Soundex part, and see if the problem goes
away:
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"Employees WHERE LastName = '" & _
Me.LastName & "'")

Btw, Soundex is a really crude way to look for similars. Personally I
like the Ratcliff-Obershelp algorithm, although it too has its
limitations.

-Tom.

Could someone take a look at this code and maybe locate why I'm getting error
on the rst.movenext command? This is from the Law Track example and it is
supposed to give the user an error if a duplicate contact is entered... I
just get an error message saying it's too complex to evaluate.

Thx!!!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, strNames As String
' If on a new row,
If (Me.NewRecord = True) Then
' Check for similar name
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for similar names
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"Employees WHERE Soundex([LastName]) = '" & _
Soundex(Me.LastName) & "'")
' If got some similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!LastName & ", " & rst!FirstName &
vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we got some similar names
If Len(strNames) > 0 Then
' Yup, issue warning
If vbNo = MsgBox("Service Department Database found contacts
with similar " & _
"last names already saved: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & "Are you sure this contact is not a
duplicate?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle)
Then
' Cancel the save
Cancel = True
End If
End If
End If
End If
End Sub
Click to expand...
Click to expand...
 
My crystal ball is in repairs. What error are you receiving now? Not
the same "too complex" I presume.

-Tom.

I commented out the section you reccomended I remove and now I'm getting an
error on - Do Until rst.EOF

I really like the idea of being able to check to see if a contact or
customer already exists when entering a new contact or customer. I just can't
seem to be able to get this to work.

I'm not a programmer; I do the best I can with the examples people provide
and usually I do ok. With this example, I'm stumped. I'd hate to remove this
code all together - but I guess I might have to huh?

Thx for your help.

Jason

Tom van Stiphout said:
You get that "too complex" error message on the rst.MoveNext line?
That seems strange. If at all I would expect it on the OpenRecordset
line.
Temporarily take out the Soundex part, and see if the problem goes
away:
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"Employees WHERE LastName = '" & _
Me.LastName & "'")

Btw, Soundex is a really crude way to look for similars. Personally I
like the Ratcliff-Obershelp algorithm, although it too has its
limitations.

-Tom.

Could someone take a look at this code and maybe locate why I'm getting error
on the rst.movenext command? This is from the Law Track example and it is
supposed to give the user an error if a duplicate contact is entered... I
just get an error message saying it's too complex to evaluate.

Thx!!!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, strNames As String
' If on a new row,
If (Me.NewRecord = True) Then
' Check for similar name
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for similar names
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"Employees WHERE Soundex([LastName]) = '" & _
Soundex(Me.LastName) & "'")
' If got some similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!LastName & ", " & rst!FirstName &
vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we got some similar names
If Len(strNames) > 0 Then
' Yup, issue warning
If vbNo = MsgBox("Service Department Database found contacts
with similar " & _
"last names already saved: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & "Are you sure this contact is not a
duplicate?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle)
Then
' Cancel the save
Cancel = True
End If
End If
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
I hear-ya, and I do appreciate your help.

When I commented out the section you originally suggested, I received and
error on "Do Until rst.EOR." Does this mean anything to you?

I had to comment out this entire piece of code. Dup-contacts & customers can
be added to my application. This is not a huge deal but something that I
would like to be able to prevent.

Are you aware of any examples online that demo how to do this?

Thx, again - Jason


Tom van Stiphout said:
My crystal ball is in repairs. What error are you receiving now? Not
the same "too complex" I presume.

-Tom.

I commented out the section you reccomended I remove and now I'm getting an
error on - Do Until rst.EOF

I really like the idea of being able to check to see if a contact or
customer already exists when entering a new contact or customer. I just can't
seem to be able to get this to work.

I'm not a programmer; I do the best I can with the examples people provide
and usually I do ok. With this example, I'm stumped. I'd hate to remove this
code all together - but I guess I might have to huh?

Thx for your help.

Jason

Tom van Stiphout said:
You get that "too complex" error message on the rst.MoveNext line?
That seems strange. If at all I would expect it on the OpenRecordset
line.
Temporarily take out the Soundex part, and see if the problem goes
away:
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"Employees WHERE LastName = '" & _
Me.LastName & "'")

Btw, Soundex is a really crude way to look for similars. Personally I
like the Ratcliff-Obershelp algorithm, although it too has its
limitations.

-Tom.


Could someone take a look at this code and maybe locate why I'm getting error
on the rst.movenext command? This is from the Law Track example and it is
supposed to give the user an error if a duplicate contact is entered... I
just get an error message saying it's too complex to evaluate.

Thx!!!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, strNames As String
' If on a new row,
If (Me.NewRecord = True) Then
' Check for similar name
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for similar names
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"Employees WHERE Soundex([LastName]) = '" & _
Soundex(Me.LastName) & "'")
' If got some similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!LastName & ", " & rst!FirstName &
vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we got some similar names
If Len(strNames) > 0 Then
' Yup, issue warning
If vbNo = MsgBox("Service Department Database found contacts
with similar " & _
"last names already saved: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & "Are you sure this contact is not a
duplicate?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle)
Then
' Cancel the save
Cancel = True
End If
End If
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
JK said:
When I commented out the section you originally suggested, I received and
error on "Do Until rst.EOR." Does this mean anything to you?
Click to expand...

First, I'm assuming that was supposed to be "EOF", not "EOR"...if it IS
"EOR", then that's your problem right there.

But assuming that was just a typo on your part, what's the error you're getting?


Rob
 
Yes, the error is on EOF not EOR - sorry.

The message I receive is: The expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric expression may contain too
many complicated elements...... etc........

When I click Debug, it highlights "rst.MoveNext" in the Before Update Event
Code (see below)

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rst As DAO.Recordset, strNames As String
' If on a new row,
If (Me.NewRecord = True) Then
' Check for similar name
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for similar names
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"[Employees] WHERE Soundex([LastName]) = '" & _
Soundex(Me.LastName) & "'")
' If got some similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!LastName & ", " & rst!FirstName &
vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we got some similar names
If Len(strNames) > 0 Then
' Yup, issue warning
If vbNo = MsgBox("Service Department Database found contacts
with similar " & _
"last names already saved: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & "Are you sure this contact is not a
duplicate?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle)
Then
' Cancel the save
Cancel = True
End If
End If
End If
End If
End Sub
 
Hmmm...I don't SEE anything wrong with the query or code off-hand. Since
the only unusual thing here is the addition of the Soundex function, I'm
thinking that perhaps it's having difficulty evaluating that somewhere along
the way. Just as a test, manually evaluate Soundex(Me.LastName) and
hard-code it into the SQL temporarily and see where that gets you.

If that doesn't work, try also eliminating the WHERE clause and adding
Soundex([LastName]) to your SELECT clause. See if there are any unusual
results there that might be causing problems. (I'm thinking particularly of
NULL values or a possible bug in your Soundex function.)


Rob
Yes, the error is on EOF not EOR - sorry.

The message I receive is: The expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric expression may contain too
many complicated elements...... etc........

When I click Debug, it highlights "rst.MoveNext" in the Before Update Event
Code (see below)

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rst As DAO.Recordset, strNames As String
' If on a new row,
If (Me.NewRecord = True) Then
' Check for similar name
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for similar names
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM " & _
"[Employees] WHERE Soundex([LastName]) = '" & _
Soundex(Me.LastName) & "'")
' If got some similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!LastName & ", " & rst!FirstName &
vbCrLf
rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we got some similar names
If Len(strNames) > 0 Then
' Yup, issue warning
If vbNo = MsgBox("Service Department Database found contacts
with similar " & _
"last names already saved: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & "Are you sure this contact is not a
duplicate?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle)
Then
' Cancel the save
Cancel = True
End If
End If
End If
End If
End Sub



Robert Morley said:
First, I'm assuming that was supposed to be "EOF", not "EOR"...if it IS
"EOR", then that's your problem right there.

But assuming that was just a typo on your part, what's the error you're getting?


Rob
Click to expand...
Click to expand...
 
Back
Top