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 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
<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.
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.
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.
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
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
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
<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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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
|
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...
|
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...
| |