Avoid duplicate names/entries

[Chris K]

I want to avoid same name being entered twice into my database (sure it's
common request) but at the same time leaving the scope for two (or even
three, four) different individuals who just happen to have the same name

I have used last-name after update to check the first/lastnames entered in
new record

If they already exist produced a popup 'continuous' form with list of
existing clients with same name (along with birthdate to help verify)
Then a choice of close buttons
choose an existing entry, store that ID in the main form
tag property, close popup, delete current record and move to existing entry
or set main form tag as "", close the popup, and continue
with new record

It all works fine in debug mode but it errors on run and i might have a look

I could post code but I'm just think I'm making a meal out of something
simple, so my real questions is about process

Whats the 'normal' way I should tackle this issue?


In the cold light of day I could get the birthday to help verify, but this
info is taken down very quickly by staff in a phone call and this seem like
the simplest way to avoid errors

 

[Chris K]

The usual way is for the pop up form to be opened in Dialog
mode and to but the ID or ZLS in a hidden text box. Then
your "close" buttons just make the form invisible. Back in
your main form, the code simple retrieves the ID from the
hidden text box and really closes the dialog. The advantage
is that the dialog form does not have to know who called it.

Off the top of my head, I don't see anything else that might
be wrong with the way you are doing it. You never did say
what error you are getting or where or when the error
happens.

I'll take a wild shot in the dark that you are assigning the
Tag property value, which is string, to a number type
variable and getting some kind of data type mismatch error.
If so try using the CInt or CLng function on the Tag's value
to convert it back to a number.

--

Sounds like my method isn't too far out so can you see anything obviously
wrong with my code - it doesn't change record unless i run it in debug mode?

-------Main Form---------------

Sub Checkname
DoCmd.OpenForm ("Select Client")
If Me.ID.Tag = "" Then Exit Sub
Me.Undo
With Me.Recordset
.MoveFirst
.FindFirst "[ID] = " & Val(Me.ID.Tag)
End With
End Sub

-----Select Client continuous form----------

Private Sub Form_Current()
ID.Tag = Me.ID
End Sub

Private Sub GotoThisClient_Click()-----------------button 1
On Error GoTo Err_GotoThisClient_Click

Forms!jhpclients.ID.Tag = Me.ID
DoCmd.Close

Exit_GotoThisClient_Click:
Exit Sub

Err_GotoThisClient_Click:
MsgBox Err.Description
Resume Exit_GotoThisClient_Click

End Sub

Private Sub cmdCancel_Click()----------------button 2
On Error GoTo Err_cmdCancel_Click

Forms!jhpclients.ID.Tag = ""
DoCmd.Close

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub

 

[Chris K]

"Marshall Barton" wrote

Chris K wrote:

I want to avoid same name being entered twice into my database (sure
it's
common request) but at the same time leaving the scope for two (or even
three, four) different individuals who just happen to have the same name

I have used last-name after update to check the first/lastnames entered
in
new record

If they already exist produced a popup 'continuous' form with list of
existing clients with same name (along with birthdate to help verify)
Then a choice of close buttons
choose an existing entry, store that ID in the main
form
tag property, close popup, delete current record and move to existing
entry
or set main form tag as "", close the popup, and continue
with new record

It all works fine in debug mode but it errors on run and i might have a
look

I could post code but I'm just think I'm making a meal out of something
simple, so my real questions is about process

Whats the 'normal' way I should tackle this issue?


The usual way is for the pop up form to be opened in Dialog
mode and to but the ID or ZLS in a hidden text box. Then
your "close" buttons just make the form invisible. Back in
your main form, the code simple retrieves the ID from the
hidden text box and really closes the dialog. The advantage
is that the dialog form does not have to know who called it.

Off the top of my head, I don't see anything else that might
be wrong with the way you are doing it. You never did say
what error you are getting or where or when the error
happens.

I'll take a wild shot in the dark that you are assigning the
Tag property value, which is string, to a number type
variable and getting some kind of data type mismatch error.
If so try using the CInt or CLng function on the Tag's value
to convert it back to a number.

Sounds like my method isn't too far out so can you see anything obviously
wrong with my code - it doesn't change record unless i run it in debug
mode?

-------Main Form---------------

Sub Checkname
DoCmd.OpenForm ("Select Client")
If Me.ID.Tag = "" Then Exit Sub
Me.Undo
With Me.Recordset
.MoveFirst
.FindFirst "[ID] = " & Val(Me.ID.Tag)
End With
End Sub

The big problem is that your above code finishes running
long before the client is selected. To force your code to
pause until the Select Client form is closed (or made
invisible). you must open the form in dialog mode:
DoCmd.OpenForm "Select Client", WindowMode:=acDialog

--

Ah! - Dont know why I had it in my head it would wait anyway - thanks very
much i'll give that a whirl