UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Loop To See If Same Data Is On Same Date, Access 2013    
 
   
soggycashew
post Jul 10 2019, 10:45 AM
Post#1



Posts: 325
Joined: 23-April 13
From: WV, USA


Hello, I have a main form (frm_ShiftDay) that has two data entries on it before moving onto its subfoms. On this main form I need to Loop through records and if I already have a [SupervisorID] in my cboSupervisorName for a date [ShiftDate] txtShiftDate then give a message "You cant have a supervisor work twice on the same date." The main forms pk is [ShiftDayID]

Thanks,
Go to the top of the page
 
theDBguy
post Jul 10 2019, 10:50 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,699
Joined: 19-June 07
From: SunnySandyEggo


Hi. I tend to use the DCount() function to test/check for existence of specific records. For example:
CODE
If DCount("*","TableName","ID=" & Me.ID)>0 Then

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jul 10 2019, 11:08 AM
Post#3


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


You can also apply a unique index on the two fields for SupervisorID and ShiftDate. This would be a Composite Index because it involves two (or more) fields. That allows the database engine to enforce your restriction directly in the table.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
soggycashew
post Jul 10 2019, 11:54 AM
Post#4



Posts: 325
Joined: 23-April 13
From: WV, USA


George, I tried the indexes earlier and I get a duplicate data error and it wont let me. I don't understand why I would get a duplicate data error when one is a number and the other a date as shown in the picture that shows what my table looks like. HERE is where I got the info on how...
This post has been edited by soggycashew: Jul 10 2019, 11:55 AM
Attached File(s)
Attached File  tblShiftDay.JPG ( 67.55K )Number of downloads: 3
Attached File  index.JPG ( 36.02K )Number of downloads: 1
Attached File  error1.JPG ( 33.22K )Number of downloads: 4
 
Go to the top of the page
 
soggycashew
post Jul 11 2019, 06:45 AM
Post#5



Posts: 325
Joined: 23-April 13
From: WV, USA


I still cant figure out why it wont let me index Unique = yes without the error message?


Attached File(s)
Attached File  fk.JPG ( 19.48K )Number of downloads: 1
 
Go to the top of the page
 
gemmathehusky
post Jul 11 2019, 06:51 AM
Post#6


UtterAccess VIP
Posts: 4,720
Joined: 5-June 07
From: UK


Another solution is to just limit the supervisors available for selection to those who haven't already been allocated.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Phil_cattivocara...
post Jul 11 2019, 07:02 AM
Post#7



Posts: 301
Joined: 2-April 18



QUOTE (soggycashew)
I still cant figure out why it wont let me index Unique = yes without the error message?

With this query you can see all records that prevent you to create the unique index.
SQL
SELECT Count(SupervisorID) as RecCount, SupervisorID, ShiftDate FROM tblShiftDay GROUP BY SupervisorID, ShiftDate HAVING Count(SupervisorID) > 1



--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
ADezii
post Jul 11 2019, 07:13 AM
Post#8



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


'From what I am seeing', I too cannot see any reasons why you should not be able to set a Unique Composite Key Index on [ShiftDate] and [SupervisorID]. Can you possibly upload a Database consisting of only the tblShiftDay Table without any sensitive Data?
This post has been edited by ADezii: Jul 11 2019, 07:19 AM
Go to the top of the page
 
soggycashew
post Jul 11 2019, 08:15 AM
Post#9



Posts: 325
Joined: 23-April 13
From: WV, USA


Thanks all for the help... Phil your query helped me find what the issues were!


Attached File(s)
Attached File  Capture.JPG ( 60.15K )Number of downloads: 0
 
Go to the top of the page
 
GroverParkGeorge
post Jul 11 2019, 08:39 AM
Post#10


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


Right, existing data in a table that would violate the restraint will prevent Access from applying that new restraint. So, that's an indication this problem has been occurring all along....

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 05:39 AM