Check for Repeated entries in a table for a particular field condition

[varunvithalani]

Hello,

I have a table called 'DateID' which has 2 important fields 'Date' and 'Duty'.
The date can be set to any value thereby having multiple instances of the same date.
For instance, the entries are,


Date | Duty | Dept
1 jan-10| Person1 | A
2 jan-10| Person2 | B
2 jan-10| Person3 | C
3 jan-10| Person2 | B
3 jan-10| Person2 | A

Assume, that I have made the above entries using a form.

My Task: In the After Update event of the 'Duty' field, I need to pop a message box IF, For the same dates the same person has been allotted in the duty field.

Thus, in this case, My message box should alarm me that I am allotting the same person for same day for 2 different departments which will create a problem.

I know this has something to do with queries, but I do not have any strategies to go about this.

Kindly help me with this issue if possible.
Thanks.

 

[vbaInet]

Have a look at the DLookup() function:

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[varunvithalani]

Hello,

Can you help me more on how to use it in the after update event?

Thanks.

 

[vbaInet]

Might be best to use the DCount() function instead.

http://www.techonthenet.com/access/functions/domain/dcount.php

Have a read. You simply count how many records match the criteria given in the arguments. If it is greater than 0 (which means the person has been given a duty for that day) then you prompt. I think you should reconsider your approach because if a message always pops-up it might be too intrusive. Maybe consider changing the backcolor of the text box to red?

Something along the lines of:

if dcount() > 0 then
    txtbox1.value = vbnullstring
    txtbox1.backcolor = vbred
    txtbox1.controltiptext = "This person has already been allocated to a duty today"
else
    txtbox1.backcolor = vbwhite
end if

 

[varunvithalani]

Hello,

Thank you for your prompt reply. But What i dont get is what should be the 3 arguments inside DCount( , , , ) function which enables it to give the count of values for which there are repeated entries of the Person for the same date?

I am really confused hw to write this statement?
About the colour change of the box, is a good idea, ill implement it.

Thanks
​

 

[vbaInet]

Calling a table DateID is a bit odd and might confuse you in the future. The other thing is your column "Date" should be changed to something else. Date is an Access reserved name. Before you go renaming it, think about the repercussions. If it's linked to another table, used in a form etc...

Start off with testing using static values (as shown below). Notice the use of the surrounding hash characters. You need those for dates. For string values you wrap it in single quotes (notice how Person2 is wrapped).

Dim allocCount as integer
allocCount = DCount("Duty", "DateID", "Date = #2 jan-10# AND Duty = 'Person2'")
Msgbox allocCount

 

[varunvithalani]

Hello vbaInet,

Thank you for the above syntax and its arguments. I tried in as a test and it worked appropriately for a particular date.
Now,
For my case,
I need to replace the Condition of "Date = #2 jan-10" with something like "Dates in the field are equal to each other".

I want to have a MsgBox when for the same dates, there is one same person alloted.

Kindly help me on this. Ill try on my own until your reply, May be i can figure it out.
Thanks.

 

[vbaInet]

I hope you took note of changing the field name "Date" to something else? You would have problems if you don't.

For my case,
I need to replace the Condition of "Date = #2 jan-10" with something like "Dates in the field are equal to each other".

I want to have a MsgBox when for the same dates, there is one same person alloted.

I don't understand what you mean?

 

[varunvithalani]

Hello,

Yes I changed the names of the fields from Date to Date1.
What I mean is,
i have an additional control source on the form called 'DateText' and when the user inputs a particular value using date time picker. I want the DCount command to check for that dates repeating values.

Your syntax did the work for me. I managed to do what I want. for others references I am just pasting the code here.

Dim allocCount As Integer
allocCount = DCount("<Field Name>", "<Table_Name which includes the field>", "(Date1 = Date1Text) AND (<Field Name> = 'Person2')")
If (allocCount > 0) Then
MsgBox "Repeated Entry!!", vbCritical
End If

Thank you for your help.

 

[vbaInet]

Great. Glad to have helped.