Prevent for duplicate data (VBA MS Access)

[ASK]

Dear All Seniors and Masters,



I've a Sub form as datasheet view/grid.

Could you please kindly see my Capture1 & Capture2.
I'd like to prevent for duplicate time/date for same GuideCode.
How can i prevent for those during the entry time or save time ?
Is it possible ?
Thank you so much for your kindly help.

Capture1.

Capture2.

1. AUNKZ(GuideCode) is already assigned in other file number(12345) for 03-Aug-14 (as 02-Aug-14 to 04-Aug-14).
2. TOD(GuideCode) is already assigned in same file number(23456) for 10-Aug-14, 12:00 PM (as 10-Aug-14 to 10-Aug-14, 8:00AM to 1:00PM). I'd like to prevent if the file number are same or not same.

Thank you so much again for any supporting.

Best regards,
ASK

 

[GinaWhipp]

Welcome to the Forum!

Nope, no screen shot showing on mine either, please repost.

 

[ASK]

Dear All,
I'd like to say Thank you for you kind attentions.
Print screen is not ok. So, I attached for Capture1 & 2 instead of print screen.
Could you please kindly see the attached ?

Capture1.


Capture2.



Thank you & Best regards,
ASK

 

[jdraw]

Can you tell us more about this application? Have you designed and Normalized your table(s)? It is not clear to me why you should have duplicates in your current set up.
What is the Primary Key? Do you have a "unique index" on any field or combination of fields to explicitly prevent duplicates?
Need more info.

 

[ASK]

Dears,
Thanks for quick help & attention. I'm sorry for complex my logic and words.
The primary key is Guide ID, did hide on the form(capture), a table named as tbl_gui.
Guide code is lookup from another table, named as tbl_m_gui.
And File number is added from another table and locked on form, just can find(Ctrl+F) on form, named as tbl_filenumber.
I cannot define as "unique index" in the guide code. As per our logic, the same guide may assign again in same file number, if date and time differences.
The intention of my logic is "prevent data entry for the same date/time for the same Guide". Because I worry that someone will be assigned to the same date/time, same guide, the file number is same or not same.

Info_I
***(1)I would prevent if the guide code is already assigned at any file number.
for example>>>>
let me say, this is previous..
previous data>>>'TOD[GuideCode], 02-Aug-14[Date/From], 04-Aug-14[Date/To], 08:00AM[Tim/From], 05:00PM[Time/To]
It seems, The Guide, TOD is already assigned at any file number. So I'd like to prevent data entry for that guide, TOD, date from 02-Aug-14 to 04-Aug.
for example>>>
let me say, this is future, come prevent data
future data(1)>>>'TOD[GuideCode], 03-Aug-14[Date/From], 03-Aug-14[Date/To], 08:00AM[Tim/From], 05:00PM[Time/To]
or
future data(2)>>>'TOD[GuideCode], 04-Aug-14[Date/From], 04-Aug-14[Date/To], 02:00PM[Tim/From], 05:00PM[Time/To]

Info_II
***(1)I would allow, For the same guide, same date, If the time is different.
for example>>>>
let me say, this is previous data
previous data>>>'AUNKZ[GuideCode], 02-Aug-14[Date/From], 02-Aug-14[Date/To], 08:00AM[Tim/From], 12:00PM[Time/To]
It seems, The Guide, AUNKZ has a tour on 02-Aug-14 at 8:00AM to 12:00PM. But he is free, we can assign for 12:01PM to 5:00PM, something like that.
So, I would allow to input Guide, AUNKZ for 02-Aug-14 except 08:00AM to 12:00PM, as per previous entry.
for example, let me say this is future
furture data(1)>>>'AUNKZ[GuideCode], 02-Aug-14[Date/From], 02-Aug-14[Date/To], 12:01PM[Tim/From], 03:00PM[Time/To]
furture data(2)>>>'AUNKZ[GuideCode], 02-Aug-14[Date/From], 02-Aug-14[Date/To], 05:00AM[Tim/From], 07:59AM[Time/To]

Actually I have already designed, But If I should change, I will.

Thank you & Best regards,
ASK

 

[GinaWhipp]

If I'm understanding correctly it looks like you are trying to prevent Date Range overlap. If that is true have a look at...
http://answers.microsoft.com/en-us/...-overlap/438dd3ad-0b6b-462d-adb7-e85bdba36138

(Sample download available)

 

[ASK]

Dear Mr. GinaWhipp,

Thank you so much. let me try it.

Thank you & Best regards,
ASK

 

[GinaWhipp]

Okay and remember we are here to help if you need it!

 

[ASK]

Testing is ok that from your shared link,
Finally, I got it. Thanks again for your help, Mr.
But I have a another problem. Could you teach & share me how can i do it ?
It is working on form(named as frmGui). But when I add in Navigation form for frmGui, It main query and another query in the lstResults are not working. I think it is not working on navigation form, or Do i need some coding in that. ?
I have no idea, Actually I was a very beginner for VBA, I am sorry for that.

Thank you & Best regards,
ASK

 

[GinaWhipp]

I'm not sure I understand your request. If you look in qryTest you will see why it doesn't work when you place on the Navigation Form. To get it to work you would need to change the path shown on the Criteria line. Or are you changed the name of the Controls the code References?

 

[ASK]

I'm not sure I understand your request. If you look in qryTest you will see why it doesn't work when you place on the Navigation Form. To get it to work you would need to change the path shown on the Criteria line. Or are you changed the name of the Controls the code References?

No, Sir.
I didn't change controls the code ref.
Could you please check me for wrong criteria ?
And let me know how to change after placed on the Navigation Form.
1
<[Forms]![frmGui]![txtEndResult2]
2
>[Forms]![frmGui]![txtStartResult2]

Thank you & Best regards,
ASK

 

[GinaWhipp]

I cannot see your Navigation Form and don't know it's name or if you've placed the Subform within a Subform. In order to make the correction I would need to know those names first.

 

[ASK]

I cannot see your Navigation Form and don't know it's name or if you've placed the Subform within a Subform. In order to make the correction I would need to know those names first.

Navigation form name = [Navigation Form]
MainFormname,frmGui = [frmGui] / navigationbutton name = [subnav.frmGui]
text on frmGui = [txtStartResult2] & [txtEndResult2]
checking query/[dupResults] query is on [frmGui] form
main query is checking from outside query namd as [qryTest]

when i check button[cmdchk], overlap data is on [frmGui] form
checking code is

Private Sub cmdchk_Click()
Dim intRecordCount As Integer
Dim GC As Variant

intRecordCount = DCount("*", "qryTest")
GC = DLookup("[GuideCode]", "tbl_gui", "[GuideCode]='" & Me!VGui.Form!GuideCode & "'")
If GC = Me.txtGuResult And intRecordCount > 0 Then
MsgBox "There is already a range which overlaps the times you ave input. See list for details", , "Overlap detected"
Else
MsgBox "There are no records detected within the range you have selected", , "No Overlap detected"
End If

Me.dupResults.Requery

End Sub

And I have a subform, named as [VGui] it is on [frmGui] form

Thanks for your kind answer and reply,
If i need to more info, please let me know.

Thank you & Best regards,
ASK

 

[GinaWhipp]

Wait, I'm a little confused did you put frmGui on [Navigation Form]?

 

[ASK]

Yes, I did

Thank you & Best regards,
ASK

 

[GinaWhipp]

Try replacing the criteria in the query with...

Forms![Navigation Form]![frmGUI].Form![txtStartResult2] 
 
Forms![Navigation Form]![frmGUI].Form![txtEndResult2]

 

[ASK]

Thanks for your help.
But It's not working, then
It's created auto bracket in Forms , After I replaced that in query.

<[Forms]![Navigation Form]![frmGui].[Form]![txtEndResult2]
>[Forms]![Navigation Form]![frmGui].[Form]![txtStartResult2]

Thank you & Best regards,
ASK

 

[GinaWhipp]

What is the error message you are getting?

 

[ASK]

It ask enter parameter value.
Could you please kindly see the attached files?


Thank you & Best regards,
ASK

 

[GinaWhipp]

Hmm, is the Navigation Form a Subform?