How to prevent duplicate entries

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

Guest

I have a form, and due to a lack of planning, I have had to create a new
table to hold some of the data from that form in a different layout than what
was in the original table.

The problem I now have aftering getting my SQL insert working, is that it is
now possible for the user to save the data, make changes to the form, then
save it again. For the original table this is not an issue, but since my SQL
insert code is behind the SAVE command button, it will write another row in
the new table.

I should probably just re-do the entire database, but due to time
constraints, I would rather look for a work-around.

Does anyone have a suggestion on how to code my SQL insert so that if the
user has already saved the record once, that saving a second time would only
update the row and not write a second row?

Thanks in advance.....I'll start planning to rewrite the entire db now....
 
Paul

Define "duplicate"...

If you know which fields in your table cannot have "duplicate" data, index
your table on that/those field(s). Make it a unique index.

Then, no matter how hard your user tries to enter "duplicate" data, the
insert will fail because it would create a duplicate.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Paul B. said:
I have a form, and due to a lack of planning, I have had to create a new
table to hold some of the data from that form in a different layout than
what
was in the original table.

The problem I now have aftering getting my SQL insert working, is that it
is
now possible for the user to save the data, make changes to the form, then
save it again. For the original table this is not an issue, but since my
SQL
insert code is behind the SAVE command button, it will write another row
in
the new table.

I should probably just re-do the entire database, but due to time
constraints, I would rather look for a work-around.

Does anyone have a suggestion on how to code my SQL insert so that if the
user has already saved the record once, that saving a second time would
only
update the row and not write a second row?

Thanks in advance.....I'll start planning to rewrite the entire db now....
Click to expand...

Sounds like a recipe for disaster! Are you saying that, in your new table,
once the data is saved the first time it must then become read-only forever
after? If so then my workaround, under extreme duress, would be to have a
yes/no field poulated by your save button and then test this field in the
form's Current event.

Yuk!

Keith.
www.keithwilby.com
 
Hi Jeff,

Sorry, I should have been more specific. By duplicate, I mean that there are
two rows of data for the same Unit.

What I had to do was create a new table that has one column for each item
for all of the units. IE:

UnitNumber, Region, Station, Shift

Whereas before my table had:

Unit1Region, Unit1Station, Unit1Shift, Unit2Region, Unit2Station, etc...

I now have code that says : if Unit1=True then run my SQL insert line when
the user clicks on SAVE. Also the same for Unit2 through Unit4.

So the new table could have a row entered for up to 4 units for the same
entry in the main tblEpistry.

As in my previous post, I want to avoid a second row being entered, and if
the user has already saved the record once, then only update the row
previously saved instead of saving a new row.

Perhaps I am looking at this wrong, I don't have a lot of experience
designing / laying out databases so there is probably a better way of doing
this.
 
I have a form, and due to a lack of planning, I have had to create a new
table to hold some of the data from that form in a different layout than what
was in the original table.
Click to expand...

Whoa. If you're letting the FORM dictate the structure of the table,
you've got it backwards! Am I misunderstanding?
The problem I now have aftering getting my SQL insert working, is that it is
now possible for the user to save the data, make changes to the form, then
save it again. For the original table this is not an issue, but since my SQL
insert code is behind the SAVE command button, it will write another row in
the new table.

I should probably just re-do the entire database, but due to time
constraints, I would rather look for a work-around.

Does anyone have a suggestion on how to code my SQL insert so that if the
user has already saved the record once, that saving a second time would only
update the row and not write a second row?
Click to expand...

Since we have no way to know what your current code is doing, nor what
you are trying to save where, all I can say is that you need to either
run an Update query (rather than an Append query), or use a recordset
and use the Edit method rather than the AddNew method. Care to post
your code?
Thanks in advance.....I'll start planning to rewrite the entire db now....
Click to expand...

Do. And design the properly normalized tables FIRST, make sure you
have the fields and relationships right - and *then* start designing
the form.

John W. Vinson[MVP]
 
Hi John,

Here is the current code.

--------------------------
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim mRecordID As Long
mRecordID = DMax("RecordNumber", "tblEpistry")
MsgBox ("Record #" & mRecordID)

Dim strSQL As String
Dim mUnit As Long

If Unit1 = True Then
mUnit = 1
strSQL = "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber,_
Region, Station, Shift, ShiftNumber, Qualification, EventNumber,_
AttendantNumber, StudentNumber, Form2, CAMT, AEDDL) VALUES(""" &_
mRecordID & """,""" & mUnit & """,""" & [Unit1Region] & """,""" &
[Unit1Station]_
& """,""" & [Unit1Shift] & """,""" & [Unit1ShiftNumber] & """,""" &
[Unit1Qual]_
& """,""" & [Unit1EventNumber] & """,""" & [Unit1AttNumber] & """,""" &_
[Unit1StudentNumber] & """,""" & [Unit1Form2] & """,""" & [Unit1CAMT]_
& """,""" & [Unit1AEDDL] & """)"
DoCmd.RunSQL strSQL
End If

If Unit2 = True Then
mUnit = 2
strSQL = "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber,_
Region, Station, Shift, ShiftNumber, Qualification, EventNumber,_
AttendantNumber, StudentNumber, Form2, CAMT, AEDDL) VALUES(""" &_
mRecordID & """,""" & mUnit & """,""" & [Unit2Region] & """,""" &
[Unit2Station]_
& """,""" & [Unit2Shift] & """,""" & [Unit2ShiftNumber] & """,""" &
[Unit2Qual]_
& """,""" & [Unit2EventNumber] & """,""" & [Unit2AttNumber] & """,""" &_
[Unit2StudentNumber] & """,""" & [Unit2Form2] & """,""" & [Unit2CAMT] &_
""",""" & [Unit2AEDDL] & """)"
DoCmd.RunSQL strSQL
End If

If Unit3 = True Then
&
If Unit4 = True Then

as above with the obvious refernce to Unit3 and Unit 4
 
Back
Top