Prevent duplicate code is not working as well (1 Viewer)

ASK · Aug 22, 2014

Dear All Seniors & Masters,

I'm a beginner at VBA.
This current code was prevented/checked when updated on any data even data entry has no duplicates.
If data entry is put continuous/new entry, it's ok, code is working.
If I was updated on any old data, Msgbox shown duplicate found. So I can not put second time data entry.
Could you please check and guide me that how can i change my code ?

Table name = tbl_m_gui/Form name = VGAssign
field name1 = GuideCode/txtGuideCode
field name2 = DateFrom/txtDateFrom
field name3 = DateFrom/txtDateTo
field name4 = Time(lookup list)/txtTime
field name5,6,7,8.., = ........

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
strSQL = "SELECT tbl_gui.GuideCode, tbl_gui.Time, tbl_gui.DateFrom, tbl_gui.DateTo from tbl_gui WHERE GuideCode = '" & Me.txtGuideCode & "' AND Time = '" & Me.txtTime & "' order by DateFrom "
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If Not rs.EOF Then
While Not rs.EOF
If [COLOR=Blue](rs("DateFrom").Value <= Me.txtDateFrom And rs("DateTo").Value >= Me.txtDateFrom) Or _
(rs("DateFrom").Value <= Me.txtDateTo And rs("DateTo").Value >= Me.txtDateTo)[/COLOR] Then
[COLOR=Blue]MsgBox "Overlapping Date Found, please check", , "Duplicate Guide detected"
[COLOR=Purple]Me.Undo[/COLOR][/COLOR]
Exit Sub
End If
rs.MoveNext
Wend
Else
MsgBox "Data Submitted", , "No Duplicate Guide detected"
End If
rs.Close
db.Close
End Sub

Thanks & Best regards,
ASK

spikepl · Aug 23, 2014

Edit your post and wrap you code in CODE tags

Code:

 your code

(Go advanced->select the code ->press #)

Also restate your question because what you want is not clear. Read my signature. "Not working" does not hold any clues. What do you want to happen, what does the code do in which cirumstances, and what should it be doing in stead.

vbaInet · Aug 23, 2014

ASK said:
If data entry is put continuous/new entry, it's ok, code is working.
If I was updated on any old data, Msgbox shown duplicate found. So I can not put second time data entry.

I think I kind of understand what ASK is asking about. Pun intended

You want to avoid checking for duplicates when editing existing records.
What you need to do is exclude the current record from the check. You do this in the WHERE clause.

ASK · Aug 23, 2014

Thanks for reply on my post, spikepl & vbaInet.

vbaInet said:
I think I kind of understand what ASK is asking about. Pun intended

You want to avoid checking for duplicates when editing existing records.
What you need to do is exclude the current record from the check. You do this in the WHERE clause.

Thanks for your kind understanding on me & attention on my posts, VbaInet.
I am apologize, If my English grammar usage was wrong.
Actually, I really don't know exactly where I change and update in my code with WHERE caluse.

But, Let me try & test it. If you wish, you can guide me. he he

Thanks & Best regards,

vbaInet · Aug 23, 2014

The best thing for you to understand what's going on is to try it in a query first.
Enter some criteria, see the results, if it meets your needs look at the SQL.

ASK · Aug 25, 2014

vbaInet said:
The best thing for you to understand what's going on is to try it in a query first.
Enter some criteria, see the results, if it meets your needs look at the SQL.

Well noted and Thanks for your advise. I will test it in the query first.

Thanks & Best regards,

vbaInet · Aug 25, 2014

Ok good.

You could use a query that is linked to the fields/textboxes and does a Count on the result then use your recordset to return the Counted value, or a DLookup().

ASK · Aug 26, 2014

Hi VbaInet,
I test it on frmGAssign form, it is ok. but when I test on navigation form, It's not working,

VGAssign = Subform (child2)
frmGAssgin = Navigation Subform (child1)
Navigation form = Welcome Form (parent)
query name = qrygui

query is following.

[Forms]![frmGAssign]![VGAssign]![txtGuideCode]
[Forms]![frmGAssign]![VGAssign]![txtTime]
<=[Forms]![frmGAssign]![VGAssign]![txtDateTo]
>=[Forms]![frmGAssign]![VGAssign]![txtDateFrom]

SQL view for above query

Code:

SELECT tbl_gui.GuideID, tbl_gui.GuideCode, tbl_gui.Time, tbl_gui.DateFrom, tbl_gui.DateTo, tbl_gui.DateTo
FROM tbl_gui
WHERE (((tbl_gui.GuideCode)=[Forms]![frmGAssign]![VGAssign]![txtGuideCode]) AND ((tbl_gui.Time)=[Forms]![frmGAssign]![VGAssign]![txtTime]) AND ((tbl_gui.DateFrom)<=[Forms]![frmGAssign]![VGAssign]![txtDateTo]) AND ((tbl_gui.DateTo)>=[Forms]![frmGAssign]![VGAssign]![txtDateFrom]))
ORDER BY tbl_gui.DateFrom, tbl_gui.DateFrom;

Code is

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer) 
Dim intRecordCount As Integer
intRecordCount = DCount("*", "qrygui")
If intRecordCount > 0 Then         
MsgBox "That Guide is already assigned within the date and time you entered", , "Duplicate Guide detected"
Me.Undo         
Me.txtGuideCode.SetFocus     
Else         
MsgBox "There are no Guide Assign detected within the range you have selected", , "No Duplicate Guide detected"
End If 
End Sub

Could you please advice & guide me how to change my criteria in query for uses navigation form, when you are less busy ?

Thanks and Best regards,

vbaInet · Aug 26, 2014

I don't use navigation forms because they cause headaches but the syntax should be:

Code:

Forms![COLOR="blue"]NameOfForm[/COLOR]!NavigationSubform.Form![COLOR="Blue"]FieldName[/COLOR]

... amend the blue parts

Prevent duplicate code is not working as well (1 Viewer)

ASK

Registered User.

spikepl

Eledittingent Beliped

vbaInet

AWF VIP

ASK

Registered User.

vbaInet

AWF VIP

ASK

Registered User.

vbaInet

AWF VIP

ASK

Registered User.

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread