Duplicate Entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a tblemployees with a field clock_no (which is the primary key) and
new employees are added using a form created from the table.

There seems to be so many answers to this problem. I have tried and tried to
adapt solutions provided to fit my example and attach to the BeforeUpdate (or
is it the AfterUpdate?) but I still cannot get it to work.

Can anyone please write the code I need specifically as I don't reaslly know
much about VB. Thanks.
 
I think I forgot to mention that my problem is not allowing duplicate clock
numbers for new records on the form. Sorry
 
Any number of people could write the code if you are looking to hire a
consultant. However, if you post a clearly stated question here you may get
the answer, or maybe a link to an existing solution, for free.
What is the problem? You are looking for a solution to "this problem"
without stating the problem. I assume clock_no is something like an
Employee ID number, used for punching in. Is your form bound to
tblEmployees (i.e. is tblEmployees the Record Source for the form)?
 
I realised I had forgotten to say exactly what my problem was other than in
the heading of my original message, and I apologised for that. I thought this
site was aimed at helping people like me with very little knowledge of
something like VB and hopefully providing us with assistance to solve our
problems. I don't think there was any need to be quite so sarcastic. We all
have to start somewhere.
 
If field clock_no has been defined as a primary key, then duplicate records
cannot be added.

I think you need to explain your requiements a bit better.
 
This site is indeed for helping people, not just beginners but people of all
levels, with various problems. My comment was directed at your request that
somebody write the code for you rather than that somebody point you in the
right direction, especially considering that the problem (which I did not at
the time) could be unrelated to code. I posted my reply before your
follow-up appeared. No sarcasm was intended.
To the problem at hand, if clock_id is the primary key (PK) you are limited
to one record for each PK value. There can be only one employee with the
number 1234. If you go to table design view and look at the PK field you
will see that the Indexed property is "Yes (No Duplicates)".
In a relational database a table contains information about a single entity
such as Employee. The employee table should be for storing information that
is specific to each employee: Clock_ID, FirstName, LastName, Address, City,
etc. Department may be in there, if each employee belongs to a single
department. If you are trying to add something like payroll records, you
need a separate table for that, related one-to-many from the Employee table.

tblEmployee
Clock_ID (PK)
FirstName
etc.

tblTimeWorked
TimeWorkedID (PK)
Clock_ID (foreign key, or FK)
WorkDate
TimeIn
TimeOut

Since I don't know if this is the situation I won't go into a lot of detail,
but the outline of the process is that you first click Tools >
Relationships. Add both tables, then drag Clock_ID from one table to the
other. Click Enforce Referential Integrity when the dialog appears. Now
each Employee record can be associated with any number of TimeWorked
records. One way of handling the TimeWorked records is that each day can be
a separate record, but there are other options. In any case, you would
create a form based on tblEmployee and a subform (continuous view may be
best) based on tblTimeWorked. You can use the subform wizard in the toolbox
to add the subform to the main form.
 
Hey, Megan! The error generated by attempting to duplicate a Primary Key is
3022. The code below provides a custom error message (instead of Access'
rather arcane one) and sets the focus back on the text box so that a new
Clock Number can be typed in. You'll have to replace

EmpCN

in the code with the actual name of your text box that holds clock_no.

Good luck!

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then ' User tried to duplicate the Clock Number
Response = MsgBox("This Employee Clock Number Already Exists!",
vbExclamation, "Duplicate Employee Clock Number!") ' Pops up warning
Response = acDataErrContinue
EmpCN.SetFocus ' This line set focus back on the Clock Number text
box
EmpCN.SelStart = 0 ' These lines hilites the text ready to have new
number typed in
EmpCN.SelLength = Len(EmpCN)
End If
End Sub

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
Make sure that

Response = MsgBox("This Employee Clock Number Already Exists!",
vbExclamation, "Duplicate Employee Clock Number!") ' Pops up warning

is all on one line when you paste it into yourcode editor

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
Back
Top