473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Preventing Duplicate Records

I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.

CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.

I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.

Hope I have explained my situation...I appreciate any help or
resources you can point me to.

Many thanks in advance - RJ Shrader
Jan 17 '08 #1
9 4120
Have you tried using DCount() of DLookup() before saving?
http://www.mvps.org/access/general/gen0018.htm

rj*******@gmail.com wrote:
>I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.

CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.

I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.

Hope I have explained my situation...I appreciate any help or
resources you can point me to.

Many thanks in advance - RJ Shrader
--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200801/1

Jan 17 '08 #2

<rj*******@gmail.comwrote in message
news:d1**********************************@c4g2000h sg.googlegroups.com...
I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.

CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.

I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.

Hope I have explained my situation...I appreciate any help or
resources you can point me to.

Many thanks in advance - RJ Shrader
Put a unique index on the CustomerID and StatusType fields in the table.
Jan 17 '08 #3
On Jan 16, 9:55*pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:
Have you tried using DCount() of DLookup() before saving?http://www.mvps.org/access/general/gen0018.htm

rjshra...@gmail.com wrote:
I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.
CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.
I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.
Hope I have explained my situation...I appreciate any help or
resources you can point me to.
Many thanks in advance - RJ Shrader

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2008...- Hide quoted text -

- Show quoted text -
I have played with both and understand how to use DCount to retrieve
the number of occurrences of a record and with DLookup to retrieve a
value. I added a MSGBOX (variable) to display the results so I could
see what was being retrieved or counted....but I do not understand how
to use them to check for the duplicate.
Jan 17 '08 #4
On Jan 17, 8:05*am, "paii, Ron" <n...@no.comwrote:
<rjshra...@gmail.comwrote in message

news:d1**********************************@c4g2000h sg.googlegroups.com...


I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.
CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.
I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.
Hope I have explained my situation...I appreciate any help or
resources you can point me to.
Many thanks in advance - RJ Shrader

Put a unique index on the CustomerID and StatusType fields in the table.- Hide quoted text -

- Show quoted text -
Unique indexes will not work because I may have the same CustomerID
being used on different days. I just want to prohibit duplicate
entries on the date. If I put indexed on the date, then I would only
be able to enter one entry per day...regardless of CustomerID.
Jan 17 '08 #5
In your SaveCommand button, check for duplicates before saving and don't save
if you find one.

rj*******@gmail.com wrote:
>On Jan 16, 9:55Â*pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:
>Have you tried using DCount() of DLookup() before saving?http://www.mvps.org/access/general/gen0018.htm
[quoted text clipped - 23 lines]
>>
- Show quoted text -

I have played with both and understand how to use DCount to retrieve
the number of occurrences of a record and with DLookup to retrieve a
value. I added a MSGBOX (variable) to display the results so I could
see what was being retrieved or counted....but I do not understand how
to use them to check for the duplicate.
--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

Jan 17 '08 #6
Please post your DCount() code so we can see it.

rj*******@gmail.com wrote:
>On Jan 16, 9:55Â*pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:
>Have you tried using DCount() of DLookup() before saving?http://www.mvps.org/access/general/gen0018.htm
[quoted text clipped - 23 lines]
>>
- Show quoted text -

I have played with both and understand how to use DCount to retrieve
the number of occurrences of a record and with DLookup to retrieve a
value. I added a MSGBOX (variable) to display the results so I could
see what was being retrieved or counted....but I do not understand how
to use them to check for the duplicate.
--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200801/1

Jan 17 '08 #7
Excellent! Have fun with the rest of the project.

rj*******@gmail.com wrote:
>On Jan 17, 10:10Â*am, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:
>Try:
ScanCount = DCount("StatusDate", "tblStatus", "CustomerID Â*= '" &
[quoted text clipped - 40 lines]
>>
- Show quoted text -

That seems to work. Looks like the inclusion of the following code at
the end resolve the problem. I still have so much to learn...I was not
aware that I could have AND other criteria.

Many thanks to you and to all for their suggestions.

AND [YourDateField] =#" & Date() & "#")
--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200801/1

Jan 17 '08 #8

<rj*******@gmail.comwrote in message
news:e3**********************************@s13g2000 prd.googlegroups.com...
On Jan 17, 8:05 am, "paii, Ron" <n...@no.comwrote:
<rjshra...@gmail.comwrote in message

news:d1**********************************@c4g2000h sg.googlegroups.com...


I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.
CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.
I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.
Hope I have explained my situation...I appreciate any help or
resources you can point me to.
Many thanks in advance - RJ Shrader

Put a unique index on the CustomerID and StatusType fields in the table.-
Hide quoted text -
>
- Show quoted text -
Unique indexes will not work because I may have the same CustomerID
being used on different days. I just want to prohibit duplicate
entries on the date. If I put indexed on the date, then I would only
be able to enter one entry per day...regardless of CustomerID.

You can create a combined index based on both fields and have 1 record per
day for each CustomerID
Jan 17 '08 #9
ruralguy via AccessMonster.com wrote:
Excellent! Have fun with the rest of the project.

rj*******@gmail.com wrote:
>On Jan 17, 10:10 am, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:
>>Try:
ScanCount = DCount("StatusDate", "tblStatus", "CustomerID = '" &
[quoted text clipped - 40 lines]
>>- Show quoted text -
That seems to work. Looks like the inclusion of the following code at
the end resolve the problem. I still have so much to learn...I was not
aware that I could have AND other criteria.

Many thanks to you and to all for their suggestions.

AND [YourDateField] =#" & Date() & "#")
You could also use the following psudo code to search duplicates
returned for a dynaset based on appropriate sql code, which would yield
more than one record if a duplicate record is found:

me.RecordSource = "Appropriate SQL Code here to return a dynaset"
'Determine if more than one record is returned
if me.RecordsetClone.RecordCount 1 then
'Do whatever if more than one record is returned
Else
'Do whatever if only one record (or no records) are returned.
End If
Feb 2 '08 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Drew | last post by:
I have a permission tracking app that I am working on, and I have made the insert page for it. I am having issues on how to prevent duplicates from getting entered. Currently the interface for...
3
by: ColinWard | last post by:
I am using the following code to validate that the person that is being entered into the database does not already exist. However wnem I test it by entering myself as a contact(I first checked that...
5
by: Darrel | last post by:
I have a web form that inserts a record into a DB. This function is called from the button_click subroutine. After the record is inserted, I show/hide some different panels and let the page...
1
by: Colin Spalding | last post by:
My problem is that the 'INSERT INTO' query that sends the records to the table is dynamically compiled in VBA and and the target table has a two column primary key. I have made a number of...
2
by: Allen Anderson | last post by:
Hi, I'm trying to design contact (names and addresses) tables in an Access database. Some of the contacts represent vendors, some are board members of the organization, some are donors, some...
1
by: pcnorb | last post by:
I have a form, pulling data from a Products table that has many fields that do lookups to other tables. These are in a one-to-many relationship to a pk in each of said tables. I'd like to be...
6
by: Oleg Konovalov | last post by:
Hi, I have a Java/JavaScript GUI application where I perform a lot of long DB operations , which takes 5-60 secs to perform. Sometimes user double-clicks the button or just gets impatient and...
2
by: nethravathy | last post by:
Hi, The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not. I tried with following query 1)SELECT...
2
by: nomvula | last post by:
hi guys i need some help to duplicate records on my form datasheet: here's the example of my form results: ClientLookup DateCaptured ForecastDate Description ForecastQuantity Forecast Actual UJ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.