Duplicate Not Allowed, clear field

[Pat Briggs]

I have an entry form that has a field called EmplID. I don't want to have
duplicates so I created a macro to give a message that says "You have entered
a duplicate, do you wish to see the record?". My question is how to I go
about getting that to happen. I created a command button where they can
enter the EmplID and bring up the record but I can't modify or save it
because the original entry form still exists with the duplicate number in it.
I need to know how to either clear that field or write code to automatically
bring in the entire record when a duplicate number is entered. Help, please!

 

[Jack Leach]

First off, this field should definately be unbound. EmplID should be indexed
with duplicates set to No (access will take care of the rest by itself). An
autonumber primary key automatically takes care of this for you in the table
design.

Usually what I do is provide an unbound field at the header of the form,
with a Go button next to it. OnClick, the Go button pulls the text in the
unbound field and searches the recordsetclone of the form for a matching
record.

This isn't tested but should give an idea (this assumes you have a text
field for an employee code, but I would still keep an autonumber ID under it).

Private Sub btnGo_Click()
Dim rs As DAO.Recordset 'the recordset clone of the form for searching
Dim strFind As String 'the value your user typed

'Set the focus on your Go control because
'it needs the focus to read the text value
Me.ctlGo.SetFocus
strFind = Nz(Me.ctlGo.Text, "")

'if the user entered something, find it with a bookmark
If strFind <> "" Then

Set rs = Me.RecordsetClone
rs.FindFirst "[EmplID] = """ & strFind & """"

'Check for a find
If rs.NoMatch Then
MsgBox "The record doesnt exist"
Else
Me.Bookmark = rs.Bookmark
End If

End If

Set rs = Nothing

End Sub




this link may help out some

http://www.blueclaw-db.com/access_findfirst.htm


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Pat Briggs]

Jack, I'm not sure I'm clear on this because it will only happen if the
record already exists. I am just trying to figure out the best way to
populate the form with the correct data when they do enter a duplicate EmplID
number.

 

[Jack Leach]

I may be a little ahead of myself... you need to get the data normalized
before you try and let users pick a record.

It is a much discussed topic whether you should keep an autonumber primary
key for all of your tables, or if you can use a text value set to the primary
key. Personally I prefer to keep an autonumber behind (almost) everything.
I have a similar case with companies. Theres the Code (ex TSM or NCWORKS)
and there's the autonumber (the user never sees). What I do there is keep
the autonumber for relationship purposes, and provide the user a field to
enter a code. Not wanting the code to have duplicate values (and because I
want it indexed for searching), I set the Index property of the field to Yes
- No Duplicates.

If you do allow duplicates (not many reasons why you would), you can verify
(using the recordsetclone example) that there are no existing records before
saving buy checking it in the control's BeforeUpdate event.

As a safeguard, I'll set the field in the form to be disabled if it's not on
a new record. You can use the OnCurrent event of the form to do this:

Private Sub Form_Current
Me.ctlCompCode.Enabled = Iif(Me.NewRecord, True, False)
End Sub


Anyway, make sure your data is well normlized before getting into the user
interface stuff. Google "allen browne tips" or "crystal's access basics" (or
even just "access data normalization") and you should have no problem finding
adequate learning material. This is the absolute most important step is
designing a database... unfortunately it took me a little while to realize
how much you can't do (and how unstable your data is) until you have this
down.

good luck!
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

First off, this field should definately be unbound. EmplID should be indexed
with duplicates set to No (access will take care of the rest by itself). An
autonumber primary key automatically takes care of this for you in the table
design.

Usually what I do is provide an unbound field at the header of the form,
with a Go button next to it. OnClick, the Go button pulls the text in the
unbound field and searches the recordsetclone of the form for a matching
record.

This isn't tested but should give an idea (this assumes you have a text
field for an employee code, but I would still keep an autonumber ID under it).

Private Sub btnGo_Click()
Dim rs As DAO.Recordset 'the recordset clone of the form for searching
Dim strFind As String 'the value your user typed

'Set the focus on your Go control because
'it needs the focus to read the text value
Me.ctlGo.SetFocus
strFind = Nz(Me.ctlGo.Text, "")

'if the user entered something, find it with a bookmark
If strFind <> "" Then

Set rs = Me.RecordsetClone
rs.FindFirst "[EmplID] = """ & strFind & """"

'Check for a find
If rs.NoMatch Then
MsgBox "The record doesnt exist"
Else
Me.Bookmark = rs.Bookmark
End If

End If

Set rs = Nothing

End Sub




this link may help out some

http://www.blueclaw-db.com/access_findfirst.htm


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I have an entry form that has a field called EmplID. I don't want to have
duplicates so I created a macro to give a message that says "You have entered
a duplicate, do you wish to see the record?". My question is how to I go
about getting that to happen. I created a command button where they can
enter the EmplID and bring up the record but I can't modify or save it
because the original entry form still exists with the duplicate number in it.
I need to know how to either clear that field or write code to automatically
bring in the entire record when a duplicate number is entered. Help, please!

 

[Jeff Boyce]

Pat

It all starts with the data. What data are you working with?

Rather than force your users to enter an ID, then tell them they did it
wrong (i.e., "a duplicate"), why not let them pick from a list of existing
IDs, and give them a way to add a new one, if appropriate?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jack Leach]

I am just trying to figure out the best way to
populate the form with the correct data when they do enter a duplicate EmplID
number.

I don't think you need to do this. A form should already be bound to a
table or query, and you dont want to be entering information into it if the
user types an ID that already exists. If I understand what you're trying to
do, this will only duplicate entire records (you'll have the original, plus
the one you just pulled data into).

This is why I use the unbound field... check if it exists and go to it
(instead of pulling data into this new record), or ask them to create one if
it doesn't exist.

Not 100% sure on what you're ultimate goal is hopefully something within
these posts shines some light though.
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Pat Briggs]

Jeff, I guess that is what I am trying to get straight. So if the EmplID ID
already exists maybe I need to start with a query that brings in all the
records searched by Empl ID. And then if it is already there fine, they can
modify it but if it isn't then I could just have a command button that opens
a new form. Hmmmm does that sound plausible?

 

[Jeff Boyce]

Pat

You can make it simpler (for the user) than that!

On your form, create an unbound combobox, which you fill using a query that
gets EmplID and other identifying information (e.g., name, ...). The form
itself you bind to the table that holds the employee info, via a query that
looks to that (new) combobox for a selection criterion (i.e., which
EmplID?).

When the form first opens, the combobox is empty, so the query fills the
form with ... nothing! When you select an Employee from the combobox, the
AfterUpdate event of that combobox fires (yes, you need to create an
AfterUpdate event for the combobox), requerying the form with something as
simple as:

Me!Requery

This causes the form to look to its source (the query with the selection
criterion that looks to the combobox) and load up the record for the
selected Employee.

One more step -- take a look in Access HELP for the NotInList event. You
will need to set the LimitToList property of your combobox to Yes and add a
NotInList event procedure, but HELP provides examples. This piece is the
way to add a new employee if the one you're looking for isn't in the list.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Briggs]

Jeff, I found some older code that worked but now I have one line that
doesn't work and I have no reference point to understand why it doesn't work
with the modifications I made to it for the correct field. It is these two
lines at the end that are the problem (but I don't know for sure because I
can't run it to check). Does this look right to you and why won't the
me.AUID work (doesn't show up on the list but I don't know what it should be
instead).l

Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.Empl_ID)

Private Sub Empl_ID_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim Empl_ID As Long
Dim rs As Object

If Me.NewRecord Then
Empl_ID = CLng(Me.Empl_ID.Text)
If Empl_ID = DLookup("Empl_ID", "Table1 ", "[Empl_ID] = " & Empl_ID) Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.Empl_ID.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[Empl_ID] = " & Empl_ID
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.Empl_ID)
End If
End If
End If
End Sub

End Sub

End Sub

 

[Pat Briggs]

Jack,

I found some code from 3 years ago that someone helped me with and modified
it for this database. However when I run it I get the message that says it
exists and do I want to open it but there is nothing there when I click ok.
It doesn't seem to like the Me.Au portion after the last else. This is
exactly what I want to do any idea why it isn't working?




"Private Sub Empl_ID_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim Empl_ID As Long
Dim rs As Object

If Me.NewRecord Then
Empl_ID = CLng(Me.Empl_ID.Text)
If Empl_ID = DLookup("Empl_ID", "Cognos_Main ", "[Empl_ID] = " & Empl_ID) Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.Empl_ID.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[Empl_ID] = " & Empl_ID
Me.Bookmark = rs.Bookmark

Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.Empl_ID)

End If
End If
End If
End Sub

 

[Jeff Boyce]

I don't understand. What do you mean by "I can't run it to check"?

You can add this function, add a breakpoint, and step through, line by line.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Briggs]

Thanks for the information. I am so out of my depth here that I have no idea
what I'm doing wrong I guess. This code words fine until I get to the
Me.AUID. At this point I'm just ready to let them know it is a duplicate by
what does work in the code so if they enter the dup then they can click on a
command button that opens the new entry form. I don't even know what an
unbound combo box is and how it would work. Thanks,

Hi Pat,

‘Jeff, I found some older code that worked but now I have one line that
’doesn't work and I have no reference point to understand why it doesn't work

’with the modifications I made to it for the correct field. It is these two
’lines at the end that are the problem (but I don't know for sure because I
’can't run it to check). Does this look right to you and why won't the
’me.AUID work (doesn't show up on the list but I don't know what it should be

’instead).l

‘ Me.AuID.SelStart = 0
‘ Me.AuID.SelLength = Len(Me.Empl_ID)

The above code will not work if the control is not in focus. Furthermore it
will not work at all with the current record as New. Even if it is an
existing record, how is it going to work with this “Me.AuID.SelLength = Len
(Me.Empl_ID)†when you are using the BeforeUpdate event of your control to
modify the existing Empl_ID? You can’t modify IDs PERIOD! (Unless the control
is unbound).

In your code, you can’t bookmark a record when you are editing an existing
record or at a new record. You’ll have to use the Form’s BeforeUpdate event
to inform the user to save the edited record or to save the new record or to
discard the new record (if the user had edited or insert new records in
another of your fields/controls.

I would suggest you follow Jeff's advise to use an unbound combo box to
search for an existing record and if a record is found, move this record to
the form. and if the not, copy ID to the control and continue.

’Private Sub Empl_ID_BeforeUpdate(Cancel As Integer)
’Dim Response As Integer
’Dim Empl_ID As Long
’Dim rs As Object ‘ isn’t it better with DAO.Recordset?

’If Me.NewRecord Then
’Empl_ID = CLng(Me.Empl_ID.Text)
’If Empl_ID = DLookup("Empl_ID", "Table1 ", "[Empl_ID] = " & Empl_ID) Then
’Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
’If Response = vbYes Then
’Me.Empl_ID.Undo
’DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
’Set rs = Me.Recordset.Clone
’rs.FindFirst "[Empl_ID] = " & Empl_ID
’Me.Bookmark = rs.Bookmark
’Else
’Cancel = True
’Me.AuID.SelStart = 0
’Me.AuID.SelLength = Len(Me.Empl_ID)
’End If
’End If
’End If
’End Sub

’End Sub ‘ is there a typo here?

’End Sub ‘ and is there a typo here?

Pat Briggs wrote:

 

[Jeff Boyce]

Pat

If your combobox is connected to an underlying field in a table (or a
query), it is "bound".

If your combobox is not connected to ..., it is "unbound".

You'd use an unbound combobox because you didn't want to SAVE the choice,
merely MAKE a choice.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the information. I am so out of my depth here that I have no
idea
what I'm doing wrong I guess. This code words fine until I get to the
Me.AUID. At this point I'm just ready to let them know it is a duplicate
by
what does work in the code so if they enter the dup then they can click on
a
command button that opens the new entry form. I don't even know what an
unbound combo box is and how it would work. Thanks,

Hi Pat,

'Jeff, I found some older code that worked but now I have one line that
'doesn't work and I have no reference point to understand why it doesn't
work

'with the modifications I made to it for the correct field. It is these
two
'lines at the end that are the problem (but I don't know for sure because
I
'can't run it to check). Does this look right to you and why won't the
'me.AUID work (doesn't show up on the list but I don't know what it
should be

'instead).l

' Me.AuID.SelStart = 0
' Me.AuID.SelLength = Len(Me.Empl_ID)

The above code will not work if the control is not in focus. Furthermore
it
will not work at all with the current record as New. Even if it is an
existing record, how is it going to work with this "Me.AuID.SelLength =
Len
(Me.Empl_ID)" when you are using the BeforeUpdate event of your control
to
modify the existing Empl_ID? You can't modify IDs PERIOD! (Unless the
control
is unbound).

In your code, you can't bookmark a record when you are editing an
existing
record or at a new record. You'll have to use the Form's BeforeUpdate
event
to inform the user to save the edited record or to save the new record or
to
discard the new record (if the user had edited or insert new records in
another of your fields/controls.

I would suggest you follow Jeff's advise to use an unbound combo box to
search for an existing record and if a record is found, move this record
to
the form. and if the not, copy ID to the control and continue.

'Private Sub Empl_ID_BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim Empl_ID As Long
'Dim rs As Object ' isn't it better with DAO.Recordset?

'If Me.NewRecord Then
'Empl_ID = CLng(Me.Empl_ID.Text)
'If Empl_ID = DLookup("Empl_ID", "Table1 ", "[Empl_ID] = " & Empl_ID)
Then
'Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
'If Response = vbYes Then
'Me.Empl_ID.Undo
'DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'Set rs = Me.Recordset.Clone
'rs.FindFirst "[Empl_ID] = " & Empl_ID
'Me.Bookmark = rs.Bookmark
'Else
'Cancel = True
'Me.AuID.SelStart = 0
'Me.AuID.SelLength = Len(Me.Empl_ID)
'End If
'End If
'End If
'End Sub

'End Sub ' is there a typo here?

'End Sub ' and is there a typo here?

Pat Briggs wrote: