SteveS, Mr. Velcro: Sndx sharing issue HELP!

[Stephanie]

Hello!
I'm posting to you (or any other talented coder) since you were kind enough
to work on my previous Sndx issue- the one where if LastName was null
(rather, we were entering a Company), the Sndx code got confused.

The new issue- many users of the shared db are entering Members at the same
time and Sndx is getting confused. At the moment, the code is disallowing the
entry of any new Members. Yikes! I'm hoping for suggestions to make this all
work again.

In debug, the code craps out:
If Not IsNull(Me.LastName) And (DonorTypeID = "CU" Or
DonorTypeID = "IN"
Me.Sndx = Soundex(Me.LastName)

(which makes sense if it is confused as to which LastName it is trying to
verify when more than one person is entering a LastName).

Here's the code:

'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.DonorTypeID
Case "BU", "CI", "FO", "ME"

Case "CU", "IN"
'if more cases to run the soundex code are added, don't forget to
change the strSQL as noted.
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 Type 'Customer' and 'Individual', the Last
Name is 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)
' '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 (DonorTypeID = 'CU' OR DonorTypeID
= 'IN')"
'if more DonorTypeID are added in which we want to run
soundex, don't forget to add them here as well.
strSQL = strSQL & " AND Sndx = '" & SName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

' 'for debugging
' Debug.Print strSQL


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
Me.LastName.SetFocus

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

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

End Sub

Thanks for your time, if you have it!

 

[SteveS]

Hi Stephanie,

Do you have the app split into a front end (FE) and back end (BE) with
everyone having a *copy* of the FE on their computer or are they running it
by using a shortcut to a location on a server?

 

[Stephanie]

Geez! Everyone is on the db splitting kick ;-)

Thanks for the reply. I have not yet split the db, which from what I'm
hearing may be the problem. I'm trying to get with our technical resource guy
this weekend and split the db. I practiced splitting it on my laptop. It was
easy. But I'm a bit worried about doing it correctly on our server.

The other possible issue- I have a "find a member" lookup on the enter
member form. The user entering members would fill in all of the fields for
one member and then not navigate off the record (didn't even use save), and
then try to "find a member" on top of the record she was entering. No wonder
Sndx can get confused!

Sorry for the panic! I went to our non-profit office and turned the Sndx
code off last night until I can get the db split. At least the user will be
able to enter members. Then I'll reactivate the code and see if splitting
solves the problem.

Always nice to know you are velcro ;-)
Thanks,
Stephanie