Recordset looping (and debug looping!)

[Guest]

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) & "'")


Private Sub Form_BeforeUpdate(Cancel As Integer)
If (DonorType) = "CU" Or (DonorType) = "IN" Then
Dim varID As Variant
Dim rst As DAO.Recordset, strNames As String
Dim gstrAppTitle As String
gstrAppTitle = "Name Check"
' 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 = DBEngine(0)(0).OpenRecordset("SELECT LastName,
FirstName FROM " & _
"Contacts 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(" 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
End If
End If
End If
End If
End If

 

[Stefan Hoffmann]

hi Stephanie,

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?

You need a control flag when showing the other message boxes. Set this
flag to True when all your tests pass.

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:

This sounds weird. In your code you are checking for "CU", not for "BU".
Is this correct?

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) & "'")

Soundex is not part of any default library which you can use in a SQL
statement. Where is it defined?

When i reformat your code it looks like this, maybe this makes it easier
to debug for you:

--
Option Compare Database
Option Explicit

Private m_AllowSave As Boolean

Private Sub Form_Current()

m_AllowSave = False

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Local Error GoTo LocalError

Dim rst As DAO.Recordset

Dim varID As Variant
Dim strMessage As String
Dim strNames As String

If Not m_AllowSave Then
Cancel = True
Exit Function
End If

If Me.NewRecord And _
((Me![DonorType] = "CU") Or (Me![DonorType] = "IN")) And _
Not IsNothing(Me![LastName]) Then

strSQL = "SELECT LastName, FirstName " & _
"FROM Contacts " & _
"WHERE Soundex([LastName]) = '" & _
Soundex(Me![LastName]) & "'"
Set rst = DBEngine(0)(0).OpenRecordset(strSQL)
strNames = ""
Do While Not rst.EOF
strNames = strNames & rst!LastName & ", " & _
rst!FirstName & vbCrLf
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

If Len(strNames) > 0 Then
strMessage = "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?"
Cancel = (MsgBox(strMessage, vbQuestion + vbYesNo, _
"Name Check") = vbNo)
End If

End If

Exit Function

LocalError:
Cancel = True
MsgBox Err.Description

End Sub

 

[Guest]

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>----------------------

 

[Guest]

Wow! Thanks for taking the time to look at this- don't lose me please, I'm a
conference next week but will try to work on this as well. And you are
absolutely correct- the sql query brought back #Error on those contacts
without a last name. Are you psychic?!

As you've probably guessed, another fabulous discussion group person wrote
the code and I have very limited coding experience.

Although DonorType is the first field on the form, I don't enforce that the
user complete this field until they try to move off the record. I suppose it
would easier if I knew up front what type of donor they are. If they are a
"CU" (customer) or an "IN" (individual) I would expect them to have a contact
name. If they are anything else (BU, CI, FO, ME) they may not have a contact
name. Any thoughts on whether or not I should require the DonorType field is
completed before anything else? How would I do that? You mentioned: "You need
a control flag when showing the other message boxes. Set this flag to True
when all your tests pass. " How would I do this?

I am gathering the message boxes (with code from yet another fabulous
discussion grouper) along these lines:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'On Error GoTo Err_Form_BeforeUpdate

Dim bWarn As Boolean 'Flag to warn user.
Dim strMsg As String 'MsgBox message.

If IsNull(Me!MemberDateName) And ((Me!MemberOption) = 1 Or
(MemberOption) = 2 Or (MemberOption) = 3) Then
Cancel = True
strMsg = strMsg & "Please enter a 'Member Date'." & vbCrLf
End If
....
Call CancelOrWarn(Cancel, bWarn, strMsg)

So that all of my messages appear in one box, rather than triggering the
same messages over and over if the user doesn't fix it the first time. I
thought by having the "Soundex" code in with the other message boxes that it
would also return the messages once, but it runs over and over if the user
doesn't fix the issue immediately.

You indicate: "Then I ran an update query to add the soundex values." What
would this query look like? This is to populate the new field with what
exactly?

I haven't tried your code yet, but I'm very excited to do so. You mention:
"LName is the name of the control on the form." On the form, my field is
called LastName and the control source is listed as LastName. But this has
fooled me before. What are you calling LName?

Sorry for so many questions. But always eagar to learn! Cheers and thanks!

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>----------------------

 

[Guest]

Stefan,
Thanks for taking the time to reply and think about this issue.
I've responded in SteveS's reply. I hope you'll keep following along...
Cheers!

hi Stephanie,

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?

You need a control flag when showing the other message boxes. Set this
flag to True when all your tests pass.

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:

This sounds weird. In your code you are checking for "CU", not for "BU".
Is this correct?

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) & "'")

Soundex is not part of any default library which you can use in a SQL
statement. Where is it defined?

When i reformat your code it looks like this, maybe this makes it easier
to debug for you:

--
Option Compare Database
Option Explicit

Private m_AllowSave As Boolean

Private Sub Form_Current()

m_AllowSave = False

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Local Error GoTo LocalError

Dim rst As DAO.Recordset

Dim varID As Variant
Dim strMessage As String
Dim strNames As String

If Not m_AllowSave Then
Cancel = True
Exit Function
End If

If Me.NewRecord And _
((Me![DonorType] = "CU") Or (Me![DonorType] = "IN")) And _
Not IsNothing(Me![LastName]) Then

strSQL = "SELECT LastName, FirstName " & _
"FROM Contacts " & _
"WHERE Soundex([LastName]) = '" & _
Soundex(Me![LastName]) & "'"
Set rst = DBEngine(0)(0).OpenRecordset(strSQL)
strNames = ""
Do While Not rst.EOF
strNames = strNames & rst!LastName & ", " & _
rst!FirstName & vbCrLf
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

If Len(strNames) > 0 Then
strMessage = "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?"
Cancel = (MsgBox(strMessage, vbQuestion + vbYesNo, _
"Name Check") = vbNo)
End If

End If

Exit Function

LocalError:
Cancel = True
MsgBox Err.Description

End Sub

 

[Guest]

Comments inline:

Wow! Thanks for taking the time to look at this- don't lose me please, I'm a
conference next week but will try to work on this as well. And you are
absolutely correct- the sql query brought back #Error on those contacts
without a last name. Are you psychic?!

No, but I've been told I'm a little psycho.... Actually it was just a lot
of testing.

As you've probably guessed, another fabulous discussion group person wrote
the code and I have very limited coding experience.

Although DonorType is the first field on the form, I don't enforce that the
user complete this field until they try to move off the record. I suppose it
would easier if I knew up front what type of donor they are. If they are a
"CU" (customer) or an "IN" (individual) I would expect them to have a contact
name. If they are anything else (BU, CI, FO, ME) they may not have a contact
name. Any thoughts on whether or not I should require the DonorType field is

More Donor types!!! I changed the code .... (see below)

I don't know what you are trying to do, what your form looks like or what
the rules are. If the form was in single form view, if you selected teh Donor
type first, you could make the background color of the required field change
to yellow, then back to white (or whatever color) after data was entered. A
visual clue to what entries are required.

completed before anything else? How would I do that? You mentioned: "You need
a control flag when showing the other message boxes. Set this flag to True
when all your tests pass. " How would I do this?

Stefan suggested this. Maybe an unbound hidden control on the form. After
the soundex check set the control to YES. After all the checks are completed,
set the control to NO.

I would have the NameCheck code run every time I try to save the new record.
What should happen if the the lastname is changed after the namecheck runs
but before the record is saved?

I am gathering the message boxes (with code from yet another fabulous
discussion grouper) along these lines:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'On Error GoTo Err_Form_BeforeUpdate

Dim bWarn As Boolean 'Flag to warn user.
Dim strMsg As String 'MsgBox message.

If IsNull(Me!MemberDateName) And ((Me!MemberOption) = 1 Or
(MemberOption) = 2 Or (MemberOption) = 3) Then
Cancel = True
strMsg = strMsg & "Please enter a 'Member Date'." & vbCrLf
End If
...
Call CancelOrWarn(Cancel, bWarn, strMsg)

So that all of my messages appear in one box, rather than triggering the
same messages over and over if the user doesn't fix it the first time. I
thought by having the "Soundex" code in with the other message boxes that it
would also return the messages once, but it runs over and over if the user
doesn't fix the issue immediately.

I don't see this as bad.

You indicate: "Then I ran an update query to add the soundex values." What
would this query look like? This is to populate the new field with what
exactly?

UPDATE Contacts SET Contacts.SndX = soundex([LastName])
WHERE ((Not (Contacts.LastName) Is Null));

I haven't tried your code yet, but I'm very excited to do so. You mention:
"LName is the name of the control on the form." On the form, my field is
called LastName and the control source is listed as LastName. But this has
fooled me before. What are you calling LName?

Access has a nasty habit of naming a bound control the same name as the
field name. I was getting confused which was which so I shortened the control
(textbox) name (yep I was lazy again) instead of use a proper nameing
convention.

<BEGIN SOAPBOX>

For a naming convention (one of many), check out this page:
(And bookmark it.)


http://www.mvps.org/access/general/gen0012.htm


For naming controls on a form, look at this table in the page:

Tags for Control Objects


For naming fields in a table, some of the prefixes I use are:

Date/Time - dte (dteHireDate)
String - str (strJobDesc)
Integer - int (intNumberOfEmployees)
Long integer - lng (lngJobNumberPK) (lngJobNumber_FK)
Yes/No - yn (ynTerminated)
etc


Don't use special chars (!@#$%^&*, etc) or spaces in object names. You will
give yourself major headaches if you do.

Develop a naming convention you and *use it*.

Sorry for so many questions. But always eagar to learn! Cheers and thanks!

Below is the modified code. Since you provided more donor types, I modified
the code. I cnamged one if the IF()'s to "Select Case... End Select" . It is
easier to add additional options.


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


*** watch for line wrap***

'---beg code ----
Private Sub Form_BeforeUpdate(Cancel As Integer)

'is this a new entry?
If (Me.NewRecord = True) Then

'DONOR TYPES
' CU - Customer, IN - Individual
' BU - Business, CI - ???
' FO - ??? , ME - ???

Select Case Me.DonorType
Case "BU", "CI", "FO", "ME"

Case "CU", "IN"
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 IsNull(Me.LName) 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)

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType =
'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

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
Me.Undo
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.LName)
End Select

Else 'not a new entry
' saves soundex code when editing current record

' ******** LName is the name of the control on the form
' that is bound to the LastName field
If Not IsNull(Me.LName) And (DonorType = "CU" Or DonorType = "IN")
Then
Me.SndX = SOUNDEX(Me.LName)
End If
End If 'If (Me.NewRecord = True)

End Sub

 

[Guest]

Hi Stephanie,

Wow! Thanks for taking the time to look at this- don't lose me please, I'm a
conference next week but will try to work on this as well.

So how was the conference ?


Did you get the code working?? Questions? Just checking....



Steve S

 

[Guest]

Steve,
Thanks for your patience! You really did stick around! Which is nice, since
you have helped me so often with this database for the non-profit on which I
serve as a board member. And by the way, they decided to go live on the thing
without telling me. Yikes. I went to the office and commented out the code,
but would like to get it working.
I seem to be an ungrateful wretch, but truly appreciate the help. I was in
New York over the big snow and got snowed in. My brain has turned to mush
since. 2 hour time difference seems to affect me badly.
I'm still working through your post and your code.
I'll post soon. I'm excited to learn more access!
Cheers!

 

[Guest]

Hi there!

First two questions, 2 bits of info and then on to the modified code, if
that suits you. I hope you still have time to play!
You indicated "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."
I added Sndx to my table, but "String" is not an available Data Type. I
selected "Text". OK? (question 1)

I ran
UPDATE Contacts SET Contacts.SndX = soundex([LastName])
WHERE ((Not (Contacts.LastName) Is Null));

then I ran a query:
SELECT Contacts.DonorTypeID, Contacts.LastName, Contacts.FirstName,
Contacts.Sndx
FROM Contacts
ORDER BY Contacts.LastName;

Here is a sampling (LastName, FirstName, Sndx):
Abbate, Susan, A130
Abbott, John, A130
Abell, Shellie, A165
Achey, Lorraine, A200
Ack, Jim A200
Adams, Elese, A352
Adams, Ivan, A352

Is that what you expected to see and why? (question 2)

Info bits 1 and 2:
I use LastName in every report. I didn't want to mess anything up by
changing the control name different than the field name. I still don't
understand this very well...
I did use sName for Sndx.

Now,
'NEW SOUNDEX CODE FROM STEVES
'is this a new entry?
If (Me.NewRecord = True) Then

'DONOR TYPES
' CU - Customer, IN - Individual
' BU - Business, CI - ???
' FO - ??? , ME - ???

Select Case Me.DonorType
Case "BU", "CI", "FO", "ME"

Case "CU", "IN"
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 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

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

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = "
'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

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
Me.Undo
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 Select

Else 'not a new entry
' saves soundex code when editing current record

' ******** LastName is the name of the control on the form
' that is bound to the LastName field
If Not IsNull(Me.LastName) And (DonorType = "CU" Or DonorType =
"IN") Then
Me.Sndx = Soundex(Me.LastName)
End If
End If 'If (Me.NewRecord = True)


I have an existing record: Kunz, Sarah, K152
I'm entering a new record: Kunz, Sally to see if "There are members with
similar " & "last names already saved in the database" will trigger.

When I move off the record, I get Run-time error '3061'
Too few parameters. Expected 1.

When I click on Debug, it brings me to this line:
Set rst = CurrentDb.OpenRecordset(strSQL)

What is the parameter it is expecting and why is it not getting what it wants?

Thanks for working on modifying this code. The user at our non-profit asked
me where it went. I thought commenting it out until it was working was a good
thing.
Cheers!

 

[Guest]

Hello! I can upon ocassion figure things out- especially when you modify the
code ;-)
Thanks for the help- you are fabulous! (I hope this is not a duplicate post
from me. But if it is, you'll get to read that you are fabulous twice).

The culprit- DonorType. IN and CU are DonorTypeID. From early on in the db.
So, the soundex code works- thanks to you!

Here is a question:
When the msgbox pops-
....
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
Me.Undo
Else
' good name - add soundex code and save record
....

if I select "No", my entry is wiped out and then a msgbox pops:
Invalid data: correct the entry, or press <Esc> to undo.

What I think I want- if the user says "No" I'd like them to have a chance to
look at the entry again (not just have it wiped out).

I tried another version of a msgbox but couldn't get it to work properly.
Could you offer a suggestion?

I have to tell you the most important thing I learned from you is this:
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorTypeID = 'CU' OR DonorTypeID = 'IN')"
strSQL = strSQL & " AND Sndx = '" & SName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

This is BRILLIANT! I always struggle with sql and vb alone, but trying to
create a sql statement (with line wrap) in vb is horrible. strSQL is a
fantastic method! I think I need a moment.

I appreciate all the help!

 

[Guest]

Here is a question:

When the msgbox pops-
...
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
Me.Undo
Else
' good name - add soundex code and save record
...

if I select "No", my entry is wiped out and then a msgbox pops:
Invalid data: correct the entry, or press <Esc> to undo.

What I think I want- if the user says "No" I'd like them to have a chance to
look at the entry again (not just have it wiped out).

Comment out or delete the line

Me.Undo


If you want to set the focus to "Lastname", add

' Cancel the save
Cancel = True

Me.LastName.SetFocus


HTH

 

[Guest]

Hi there!

First two questions, 2 bits of info and then on to the modified code, if
that suits you. I hope you still have time to play!
You indicated "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."
I added Sndx to my table, but "String" is not an available Data Type. I
selected "Text". OK? (question 1)

Yes, the datatype should have been "TEXT"

I ran
UPDATE Contacts SET Contacts.SndX = soundex([LastName])
WHERE ((Not (Contacts.LastName) Is Null));

then I ran a query:
SELECT Contacts.DonorTypeID, Contacts.LastName, Contacts.FirstName,
Contacts.Sndx
FROM Contacts
ORDER BY Contacts.LastName;

Here is a sampling (LastName, FirstName, Sndx):
Abbate, Susan, A130
Abbott, John, A130
Abell, Shellie, A165
Achey, Lorraine, A200
Ack, Jim A200
Adams, Elese, A352
Adams, Ivan, A352

Is that what you expected to see and why? (question 2)

Yes, but I get a different value for Abell, Shellie. Instead of "A165", I
get "A140". My Soundex() function might be a little different than yours.

And I don't see a value for "DonorTypeID"

Info bits 1 and 2:
I use LastName in every report. I didn't want to mess anything up by
changing the control name different than the field name. I still don't
understand this very well...

It is confusing at times to some one reading the code which is the field and
which is the control. (Access can also get confused). By Default, Access
names a bound control the same name as the field. Good programming practice
is to rename the control. There are many naming conventions.

See:

http://mvps.org/access/tencommandments.htm

and

http://mvps.org/access/general/gen0012.htm

I did use sName for Sndx.

I see it in the code. What name did you use in the table?

Now,
'NEW SOUNDEX CODE FROM STEVES
'is this a new entry?
If (Me.NewRecord = True) Then

'DONOR TYPES
' CU - Customer, IN - Individual
' BU - Business, CI - ???
' FO - ??? , ME - ???

Select Case Me.DonorType
Case "BU", "CI", "FO", "ME"

Case "CU", "IN"
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 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

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

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = "
'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

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
Me.Undo
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 Select

Else 'not a new entry
' saves soundex code when editing current record

' ******** LastName is the name of the control on the form
' that is bound to the LastName field
If Not IsNull(Me.LastName) And (DonorType = "CU" Or DonorType =
"IN") Then
Me.Sndx = Soundex(Me.LastName)
End If
End If 'If (Me.NewRecord = True)


I have an existing record: Kunz, Sarah, K152
I'm entering a new record: Kunz, Sally to see if "There are members with
similar " & "last names already saved in the database" will trigger.

When I move off the record, I get Run-time error '3061'
Too few parameters. Expected 1.

When I click on Debug, it brings me to this line:
Set rst = CurrentDb.OpenRecordset(strSQL)

What is the parameter it is expecting and why is it not getting what it wants?

sName = Soundex(Me.LastName)

'for debugging
Debug.Print sName

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = " 'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

'for debugging
Debug.Print strSQL

Set rst = CurrentDb.OpenRecordset(strSQL)

Set a break point on the "Set rst = CurrentDb.OpenRecordset(strSQL)
line.

Add a duplicate last name. When the code stops, open the Immediate Window
(press Ctl-G or on the menu VIEW/Immediate Window)

The first line in the immediate window should be the Soundex code and the
second line should be a line of SQL. Is the SQL what you expect (- the Where
clause)?

Post back with the name you tried to enter and the SQL line form the
Immediate window.




I had to make a new table, form and query. I pasted in the code above and it
ran without errors.......

If you want, delete any sensetive info from your database, do a compact and
repair, then WinZip it and email it to me. I'll take a look at it.


HTH

 

[Guest]

Perfect! All is good. Thanks for the help- I appreciate all of your efforts!

 

[Guest]

Steve,
This email flapping was prior to the "you're fabulous email" ;-)
But just to follow up, the DonorTypeID is pulling in the query (I just
didn't post them). I have IN, CU (customer), FO (foundation). All looks good
and the code activates correctly, thanks to your select case set up which
gives us room to grow.

I appreciate the link to naming conventions. Sadly, many fields are named
badly but I fear changing the names in case I break something.

I named the soundex field in the table: Sndx. At the time of the email, I
had the control source as Sndx and had tried to enter the land of proper
naming convention by changing the text box name to sName but then the code
hung up on that line and I whimped out and changed the text box name back to
Sndx.

I solved the run-time error of too few parameters by changing DonorType to
DonorTypeID. Apparently that was one of the parameters ;-)

The step-by-step debugging walkthrough you described was excellent! Thanks
for those tips. I did see what I expected and the debug print for the sql
gave a great opportunity to see the entire sql string. I like that.

I'm sorry you went to so much effort. I appreciate all the help and hope you
had a fun coding challenge along the way. It's good for now but with the
non-profit already live on the db, there will be more challenges! I
appreciate the offer to look at the db but then you'd discover that its held
together with spit and coding glue. Thanks for the time you donate to the
discussion group!
Cheers,
Stephanie

Hi there!

First two questions, 2 bits of info and then on to the modified code, if
that suits you. I hope you still have time to play!
You indicated "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."
I added Sndx to my table, but "String" is not an available Data Type. I
selected "Text". OK? (question 1)

Yes, the datatype should have been "TEXT"

I ran
UPDATE Contacts SET Contacts.SndX = soundex([LastName])
WHERE ((Not (Contacts.LastName) Is Null));

then I ran a query:
SELECT Contacts.DonorTypeID, Contacts.LastName, Contacts.FirstName,
Contacts.Sndx
FROM Contacts
ORDER BY Contacts.LastName;

Here is a sampling (LastName, FirstName, Sndx):
Abbate, Susan, A130
Abbott, John, A130
Abell, Shellie, A165
Achey, Lorraine, A200
Ack, Jim A200
Adams, Elese, A352
Adams, Ivan, A352

Is that what you expected to see and why? (question 2)

Yes, but I get a different value for Abell, Shellie. Instead of "A165", I
get "A140". My Soundex() function might be a little different than yours.

And I don't see a value for "DonorTypeID"

Info bits 1 and 2:
I use LastName in every report. I didn't want to mess anything up by
changing the control name different than the field name. I still don't
understand this very well...

It is confusing at times to some one reading the code which is the field and
which is the control. (Access can also get confused). By Default, Access
names a bound control the same name as the field. Good programming practice
is to rename the control. There are many naming conventions.

See:

http://mvps.org/access/tencommandments.htm

and

http://mvps.org/access/general/gen0012.htm

I did use sName for Sndx.

I see it in the code. What name did you use in the table?

Now,
'NEW SOUNDEX CODE FROM STEVES
'is this a new entry?
If (Me.NewRecord = True) Then

'DONOR TYPES
' CU - Customer, IN - Individual
' BU - Business, CI - ???
' FO - ??? , ME - ???

Select Case Me.DonorType
Case "BU", "CI", "FO", "ME"

Case "CU", "IN"
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 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

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

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = "
'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

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
Me.Undo
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 Select

Else 'not a new entry
' saves soundex code when editing current record

' ******** LastName is the name of the control on the form
' that is bound to the LastName field
If Not IsNull(Me.LastName) And (DonorType = "CU" Or DonorType =
"IN") Then
Me.Sndx = Soundex(Me.LastName)
End If
End If 'If (Me.NewRecord = True)


I have an existing record: Kunz, Sarah, K152
I'm entering a new record: Kunz, Sally to see if "There are members with
similar " & "last names already saved in the database" will trigger.

When I move off the record, I get Run-time error '3061'
Too few parameters. Expected 1.

When I click on Debug, it brings me to this line:
Set rst = CurrentDb.OpenRecordset(strSQL)

What is the parameter it is expecting and why is it not getting what it wants?

sName = Soundex(Me.LastName)

'for debugging
Debug.Print sName

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = " 'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

'for debugging
Debug.Print strSQL

Set rst = CurrentDb.OpenRecordset(strSQL)

Set a break point on the "Set rst = CurrentDb.OpenRecordset(strSQL)
line.

Add a duplicate last name. When the code stops, open the Immediate Window
(press Ctl-G or on the menu VIEW/Immediate Window)

The first line in the immediate window should be the Soundex code and the
second line should be a line of SQL. Is the SQL what you expect (- the Where
clause)?

Post back with the name you tried to enter and the SQL line form the
Immediate window.




I had to make a new table, form and query. I pasted in the code above and it
ran without errors.......

If you want, delete any sensetive info from your database, do a compact and
repair, then WinZip it and email it to me. I'll take a look at it.


HTH

 

[Guest]

Stephanie,

Thank you for the kind words.

When I first started with Access, I didn't have anyone to help me with my
questions. I just found this forum a couple of years ago. Since I have
learned a lot here, I try to give back what help I can.


One other point about the code. If you add another option to the case

Case "CU", "IN"

you might add a comment to also add the option to the line

strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = 'IN')"

(add *lots* of comments to your code)


Since I didn't have your mdb to work with, my code is a guide to try and
give you ideas to help you move forward with your project. (and it was a fun
problem..)


Good luck

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

Steve,
This email flapping was prior to the "you're fabulous email" ;-)
But just to follow up, the DonorTypeID is pulling in the query (I just
didn't post them). I have IN, CU (customer), FO (foundation). All looks good
and the code activates correctly, thanks to your select case set up which
gives us room to grow.

I appreciate the link to naming conventions. Sadly, many fields are named
badly but I fear changing the names in case I break something.

I named the soundex field in the table: Sndx. At the time of the email, I
had the control source as Sndx and had tried to enter the land of proper
naming convention by changing the text box name to sName but then the code
hung up on that line and I whimped out and changed the text box name back to
Sndx.

I solved the run-time error of too few parameters by changing DonorType to
DonorTypeID. Apparently that was one of the parameters ;-)

The step-by-step debugging walkthrough you described was excellent! Thanks
for those tips. I did see what I expected and the debug print for the sql
gave a great opportunity to see the entire sql string. I like that.

I'm sorry you went to so much effort. I appreciate all the help and hope you
had a fun coding challenge along the way. It's good for now but with the
non-profit already live on the db, there will be more challenges! I
appreciate the offer to look at the db but then you'd discover that its held
together with spit and coding glue. Thanks for the time you donate to the
discussion group!
Cheers,
Stephanie

Hi there!

First two questions, 2 bits of info and then on to the modified code, if
that suits you. I hope you still have time to play!
You indicated "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."
I added Sndx to my table, but "String" is not an available Data Type. I
selected "Text". OK? (question 1)

Yes, the datatype should have been "TEXT"

I ran
UPDATE Contacts SET Contacts.SndX = soundex([LastName])
WHERE ((Not (Contacts.LastName) Is Null));

then I ran a query:
SELECT Contacts.DonorTypeID, Contacts.LastName, Contacts.FirstName,
Contacts.Sndx
FROM Contacts
ORDER BY Contacts.LastName;

Here is a sampling (LastName, FirstName, Sndx):
Abbate, Susan, A130
Abbott, John, A130
Abell, Shellie, A165
Achey, Lorraine, A200
Ack, Jim A200
Adams, Elese, A352
Adams, Ivan, A352

Is that what you expected to see and why? (question 2)

Yes, but I get a different value for Abell, Shellie. Instead of "A165", I
get "A140". My Soundex() function might be a little different than yours.

And I don't see a value for "DonorTypeID"

Info bits 1 and 2:
I use LastName in every report. I didn't want to mess anything up by
changing the control name different than the field name. I still don't
understand this very well...

It is confusing at times to some one reading the code which is the field and
which is the control. (Access can also get confused). By Default, Access
names a bound control the same name as the field. Good programming practice
is to rename the control. There are many naming conventions.

See:

http://mvps.org/access/tencommandments.htm

and

http://mvps.org/access/general/gen0012.htm

I did use sName for Sndx.

I see it in the code. What name did you use in the table?

Now,
'NEW SOUNDEX CODE FROM STEVES
'is this a new entry?
If (Me.NewRecord = True) Then

'DONOR TYPES
' CU - Customer, IN - Individual
' BU - Business, CI - ???
' FO - ??? , ME - ???

Select Case Me.DonorType
Case "BU", "CI", "FO", "ME"

Case "CU", "IN"
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 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

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

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = "
'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

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
Me.Undo
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 Select

Else 'not a new entry
' saves soundex code when editing current record

' ******** LastName is the name of the control on the form
' that is bound to the LastName field
If Not IsNull(Me.LastName) And (DonorType = "CU" Or DonorType =
"IN") Then
Me.Sndx = Soundex(Me.LastName)
End If
End If 'If (Me.NewRecord = True)


I have an existing record: Kunz, Sarah, K152
I'm entering a new record: Kunz, Sally to see if "There are members with
similar " & "last names already saved in the database" will trigger.

When I move off the record, I get Run-time error '3061'
Too few parameters. Expected 1.

When I click on Debug, it brings me to this line:
Set rst = CurrentDb.OpenRecordset(strSQL)

What is the parameter it is expecting and why is it not getting what it wants?

sName = Soundex(Me.LastName)

'for debugging
Debug.Print sName

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = " 'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

'for debugging
Debug.Print strSQL

Set rst = CurrentDb.OpenRecordset(strSQL)

Set a break point on the "Set rst = CurrentDb.OpenRecordset(strSQL)
line.

Add a duplicate last name. When the code stops, open the Immediate Window
(press Ctl-G or on the menu VIEW/Immediate Window)

The first line in the immediate window should be the Soundex code and the
second line should be a line of SQL. Is the SQL what you expect (- the Where
clause)?

Post back with the name you tried to enter and the SQL line form the
Immediate window.




I had to make a new table, form and query. I pasted in the code above and it
ran without errors.......

If you want, delete any sensetive info from your database, do a compact and
repair, then WinZip it and email it to me. I'll take a look at it.


HTH

 

[Guest]

Thanks for the tip!- I've updated the code so I remember to change it in both
places if need be.
Cheers!

Stephanie,

Thank you for the kind words.

When I first started with Access, I didn't have anyone to help me with my
questions. I just found this forum a couple of years ago. Since I have
learned a lot here, I try to give back what help I can.


One other point about the code. If you add another option to the case

Case "CU", "IN"

you might add a comment to also add the option to the line

strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = 'IN')"

(add *lots* of comments to your code)


Since I didn't have your mdb to work with, my code is a guide to try and
give you ideas to help you move forward with your project. (and it was a fun
problem..)


Good luck

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

Steve,
This email flapping was prior to the "you're fabulous email" ;-)
But just to follow up, the DonorTypeID is pulling in the query (I just
didn't post them). I have IN, CU (customer), FO (foundation). All looks good
and the code activates correctly, thanks to your select case set up which
gives us room to grow.

I appreciate the link to naming conventions. Sadly, many fields are named
badly but I fear changing the names in case I break something.

I named the soundex field in the table: Sndx. At the time of the email, I
had the control source as Sndx and had tried to enter the land of proper
naming convention by changing the text box name to sName but then the code
hung up on that line and I whimped out and changed the text box name back to
Sndx.

I solved the run-time error of too few parameters by changing DonorType to
DonorTypeID. Apparently that was one of the parameters ;-)

The step-by-step debugging walkthrough you described was excellent! Thanks
for those tips. I did see what I expected and the debug print for the sql
gave a great opportunity to see the entire sql string. I like that.

I'm sorry you went to so much effort. I appreciate all the help and hope you
had a fun coding challenge along the way. It's good for now but with the
non-profit already live on the db, there will be more challenges! I
appreciate the offer to look at the db but then you'd discover that its held
together with spit and coding glue. Thanks for the time you donate to the
discussion group!
Cheers,
Stephanie

:

Hi there!

First two questions, 2 bits of info and then on to the modified code, if
that suits you. I hope you still have time to play!
You indicated "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."
I added Sndx to my table, but "String" is not an available Data Type. I
selected "Text". OK? (question 1)


Yes, the datatype should have been "TEXT"



I ran
UPDATE Contacts SET Contacts.SndX = soundex([LastName])
WHERE ((Not (Contacts.LastName) Is Null));

then I ran a query:
SELECT Contacts.DonorTypeID, Contacts.LastName, Contacts.FirstName,
Contacts.Sndx
FROM Contacts
ORDER BY Contacts.LastName;

Here is a sampling (LastName, FirstName, Sndx):
Abbate, Susan, A130
Abbott, John, A130
Abell, Shellie, A165
Achey, Lorraine, A200
Ack, Jim A200
Adams, Elese, A352
Adams, Ivan, A352

Is that what you expected to see and why? (question 2)

Yes, but I get a different value for Abell, Shellie. Instead of "A165", I
get "A140". My Soundex() function might be a little different than yours.

And I don't see a value for "DonorTypeID"






Info bits 1 and 2:
I use LastName in every report. I didn't want to mess anything up by
changing the control name different than the field name. I still don't
understand this very well...

It is confusing at times to some one reading the code which is the field and
which is the control. (Access can also get confused). By Default, Access
names a bound control the same name as the field. Good programming practice
is to rename the control. There are many naming conventions.

See:

http://mvps.org/access/tencommandments.htm

and

http://mvps.org/access/general/gen0012.htm



I did use sName for Sndx.


I see it in the code. What name did you use in the table?



Now,
'NEW SOUNDEX CODE FROM STEVES
'is this a new entry?
If (Me.NewRecord = True) Then

'DONOR TYPES
' CU - Customer, IN - Individual
' BU - Business, CI - ???
' FO - ??? , ME - ???

Select Case Me.DonorType
Case "BU", "CI", "FO", "ME"

Case "CU", "IN"
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 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

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

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = "
'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

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
Me.Undo
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 Select

Else 'not a new entry
' saves soundex code when editing current record

' ******** LastName is the name of the control on the form
' that is bound to the LastName field
If Not IsNull(Me.LastName) And (DonorType = "CU" Or DonorType =
"IN") Then
Me.Sndx = Soundex(Me.LastName)
End If
End If 'If (Me.NewRecord = True)


I have an existing record: Kunz, Sarah, K152
I'm entering a new record: Kunz, Sally to see if "There are members with
similar " & "last names already saved in the database" will trigger.

When I move off the record, I get Run-time error '3061'
Too few parameters. Expected 1.

When I click on Debug, it brings me to this line:
Set rst = CurrentDb.OpenRecordset(strSQL)

What is the parameter it is expecting and why is it not getting what it wants?


sName = Soundex(Me.LastName)

'for debugging
Debug.Print sName

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = " 'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

'for debugging
Debug.Print strSQL

Set rst = CurrentDb.OpenRecordset(strSQL)

Set a break point on the "Set rst = CurrentDb.OpenRecordset(strSQL)
line.

Add a duplicate last name. When the code stops, open the Immediate Window
(press Ctl-G or on the menu VIEW/Immediate Window)

The first line in the immediate window should be the Soundex code and the
second line should be a line of SQL. Is the SQL what you expect (- the Where
clause)?

Post back with the name you tried to enter and the SQL line form the
Immediate window.




I had to make a new table, form and query. I pasted in the code above and it
ran without errors.......

If you want, delete any sensetive info from your database, do a compact and
repair, then WinZip it and email it to me. I'll take a look at it.


HTH

 

[Guest]

Steve,
I hope this doesn't give you a brain cramp trying to cast your mind so far
back! Hope you'll have time to help.

We have the db on a server (I haven't split it yet- I'm scared). So multiple
people are working on entering members and there is a sharing issue, it
seems. There is an error message (sorry I can't recreate it since I'm using
the db solo), but debugger takes them to the line:

If Not IsNull(Me.LastName) And (DonorTypeID = "CU" Or DonorTypeID = "IN"

Me.Sndx = Soundex(Me.LastName)

which makes sense considerating the other issues I've had (that you fixed!)
where the db didn't know what record it was on when there was no last name
(when the record was for a company, for instance). So I imagine the db is
confused with more than one last name (or no last name) being entered.

Is there any way to address this Sndx issue for multi-users?

Thanks!

Hi there!

First two questions, 2 bits of info and then on to the modified code, if
that suits you. I hope you still have time to play!
You indicated "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."
I added Sndx to my table, but "String" is not an available Data Type. I
selected "Text". OK? (question 1)

Yes, the datatype should have been "TEXT"

I ran
UPDATE Contacts SET Contacts.SndX = soundex([LastName])
WHERE ((Not (Contacts.LastName) Is Null));

then I ran a query:
SELECT Contacts.DonorTypeID, Contacts.LastName, Contacts.FirstName,
Contacts.Sndx
FROM Contacts
ORDER BY Contacts.LastName;

Here is a sampling (LastName, FirstName, Sndx):
Abbate, Susan, A130
Abbott, John, A130
Abell, Shellie, A165
Achey, Lorraine, A200
Ack, Jim A200
Adams, Elese, A352
Adams, Ivan, A352

Is that what you expected to see and why? (question 2)

Yes, but I get a different value for Abell, Shellie. Instead of "A165", I
get "A140". My Soundex() function might be a little different than yours.

And I don't see a value for "DonorTypeID"

Info bits 1 and 2:
I use LastName in every report. I didn't want to mess anything up by
changing the control name different than the field name. I still don't
understand this very well...

It is confusing at times to some one reading the code which is the field and
which is the control. (Access can also get confused). By Default, Access
names a bound control the same name as the field. Good programming practice
is to rename the control. There are many naming conventions.

See:

http://mvps.org/access/tencommandments.htm

and

http://mvps.org/access/general/gen0012.htm

I did use sName for Sndx.

I see it in the code. What name did you use in the table?

Now,
'NEW SOUNDEX CODE FROM STEVES
'is this a new entry?
If (Me.NewRecord = True) Then

'DONOR TYPES
' CU - Customer, IN - Individual
' BU - Business, CI - ???
' FO - ??? , ME - ???

Select Case Me.DonorType
Case "BU", "CI", "FO", "ME"

Case "CU", "IN"
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 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

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

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = "
'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

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
Me.Undo
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 Select

Else 'not a new entry
' saves soundex code when editing current record

' ******** LastName is the name of the control on the form
' that is bound to the LastName field
If Not IsNull(Me.LastName) And (DonorType = "CU" Or DonorType =
"IN") Then
Me.Sndx = Soundex(Me.LastName)
End If
End If 'If (Me.NewRecord = True)


I have an existing record: Kunz, Sarah, K152
I'm entering a new record: Kunz, Sally to see if "There are members with
similar " & "last names already saved in the database" will trigger.

When I move off the record, I get Run-time error '3061'
Too few parameters. Expected 1.

When I click on Debug, it brings me to this line:
Set rst = CurrentDb.OpenRecordset(strSQL)

What is the parameter it is expecting and why is it not getting what it wants?

sName = Soundex(Me.LastName)

'for debugging
Debug.Print sName

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorType = 'CU' OR DonorType = " 'IN')"
strSQL = strSQL & " AND Sndx = '" & sName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

'for debugging
Debug.Print strSQL

Set rst = CurrentDb.OpenRecordset(strSQL)

Set a break point on the "Set rst = CurrentDb.OpenRecordset(strSQL)
line.

Add a duplicate last name. When the code stops, open the Immediate Window
(press Ctl-G or on the menu VIEW/Immediate Window)

The first line in the immediate window should be the Soundex code and the
second line should be a line of SQL. Is the SQL what you expect (- the Where
clause)?

Post back with the name you tried to enter and the SQL line form the
Immediate window.




I had to make a new table, form and query. I pasted in the code above and it
ran without errors.......

If you want, delete any sensetive info from your database, do a compact and
repair, then WinZip it and email it to me. I'll take a look at it.


HTH