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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Prevent Duplicate Entry In Three Fields Two Trxt And One Date Feild + Go To The Previous Entry, Access 2013    
 
   
Alhakeem1977
post Jul 25 2017, 08:57 AM
Post#21



Posts: 64
Joined: 8-July 17



Thanks, Mr. Doug, I solved the Query its work fine.
If you don't mind your kind amendment to the code below:

How to prevent empty fields in [AccountNo] , [DocumentDate] and [DocumentName] in the below code ?

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler

Dim strLinkCriteria
  
    strLinkCriteria = "[AccountNo] = '" & Me!AccountNo & "' AND " & _
        "[DocumentDate] = " & Format(Me!DocumentDate, "\#dd-mmm-yyyy\#") & " AND " & _
        "[DocumentName] = '" & Me!DocumentName & "'"
    
    If DCount("*", "tblFacilityRegister", strLinkCriteria) > 0 Then
        MsgBox "This document has already been sent to Document Control earlier." & vbCrLf & _
vbCr & "Press OK to lead you to the previous record." & vbCrLf & _
vbCr & "Please write the previous Doc's ID at the ((back)) top to make sure it's actually matched.", vbCritical, "Duplicate Entry"
            
        Cancel = True
        Me.Undo
        Me.DataEntry = False
        Me.Recordset.FindFirst strLinkCriteria
End If

Cleanup:
  Exit Sub
  
  
ErrorHandler:
  ' MsgBox Err.Number & ": " & Err.Description
  Beep
    MsgBox "You must enter a value in one of the following:" & vbCrLf & _
vbCr & "Account Number, DTD or Document Name.", vbOKOnly, "Empty Fields"

  Resume Cleanup
  
End Sub


Thanks a lot

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Jul 25 2017, 09:18 AM
Post#22


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler

Dim strLinkCriteria As String
Dim strMessage As String
  
  If Len(Trim(Me!AccountNo & vbNullString)) = 0 Then
    strMessage = strMessage & " You must provide an Account Number." & vbCrLf
  End If
  
  If Len(Trim(Me!DocumentDate & vbNullString)) = 0 Then
    strMessage = strMessage & " You must provide a Document Date." & vbCrLf
  End If
  
  If Len(Trim(Me!DocumentName & vbNullString)) = 0 Then
    strMessage = strMessage & " You must provide a DocumentName." & vbCrLf
  End If
  
  If Len(strMessage) > 0 Then
    MsgBox strMessage, vbCritical
    Cancel = True
    Me.Undo
  Else
    strLinkCriteria = "[AccountNo] = '" & Me!AccountNo & "' AND " & _
        "[DocumentDate] = " & Format(Me!DocumentDate, "\#dd-mmm-yyyy\#") & " AND " & _
        "[DocumentName] = '" & Me!DocumentName & "'"
    
    If DCount("*", "tblFacilityRegister", strLinkCriteria) > 0 Then
        MsgBox "This document has already been sent to Document Control earlier." & vbCrLf & _
          "Press OK to lead you to the previous record." & vbCrLf & _
          "Please write the previous Doc's ID at the ((back)) top to make sure it's actually matched.", vbCritical, "Duplicate Entry"
            
        Cancel = True
        Me.Undo
        Me.DataEntry = False
        Me.Recordset.FindFirst strLinkCriteria
    End If
  End If

Cleanup:
  Exit Sub
    
ErrorHandler:
  ' MsgBox Err.Number & ": " & Err.Description
  Beep
    MsgBox "You must enter a value in one of the following:" & vbCrLf & _
vbCr & "Account Number, DTD or Document Name.", vbOKOnly, "Empty Fields"

  Resume Cleanup
  
End Sub

--------------------
Go to the top of the page
 
Alhakeem1977
post Jul 27 2017, 11:00 AM
Post#23



Posts: 64
Joined: 8-July 17



Thanks a lot, it's wonderful. wavehi.gif
Actually two of fields are comboxes while typing not in list I got the default error message, how can I customize it to my desire message?
Is irt through Validation Role ?

I am so sorry for my questions

Thanks again

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Jul 27 2017, 11:43 AM
Post#24


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Not sure what you mean by "while typing not in list".

--------------------
Go to the top of the page
 
Alhakeem1977
post Jul 27 2017, 12:44 PM
Post#25



Posts: 64
Joined: 8-July 17



I mean in AccountNumber field or DocumentName ( Combo boxes) when the user types from the keyboard Account number instead of select from the combo box list, getting a default error message from Ms Access ( Not in list ) I want to customize that message to the user to specify the error.

I really appreciate your help.

Thanks a gain.

Best Regards,
Redha

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Jul 27 2017, 02:09 PM
Post#26


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


See whether the entry about Combobox NotInList Event in the Wiki is enough to get you going.

--------------------
Go to the top of the page
 
Alhakeem1977
post Jul 28 2017, 01:02 AM
Post#27



Posts: 64
Joined: 8-July 17



thanks.gif

It's amazing to be in touch with such a gentle man like you Mr. Doug thank you very much. kisses.gif

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Alhakeem1977
post Aug 2 2017, 12:25 PM
Post#28



Posts: 64
Joined: 8-July 17



Dear Mr. Duog,
Your kind help, I used the same code in another form (AccountNo) as a text field, (ValueDate) as a date field and (Amount) as numeric field Long Integer, the change only at the last field which is Numeric from the previous code.
CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler

Dim strLinkCriteria As String
Dim strMessage As String
    
  If Len(Trim(Me!AccountNo & vbNullString)) = 0 Then
    strMessage = strMessage & " You must provide an Account Number." & vbCrLf
  End If
  
  If Len(Trim(Me!ValueDate & vbNullString)) = 0 Then
    strMessage = strMessage & " You must provide a Value Date." & vbCrLf
  End If
  
  If Len(Trim(Me!Amount & vbNullString)) = 0 Then
  strMessage = strMessage & " You must provide a Amount." & vbCrLf
  End If
  
  If Len(strMessage) > 0 Then
   MsgBox strMessage, vbCritical
    Cancel = True
   ' Me.Undo
    
  Else
   strLinkCriteria = "[AccountNo] = '" & Me!AccountNo & "' AND " & _
   "[ValueDate] = " & Format(Me!ValueDate, "\#dd-mmm-yyyy\#") & " AND " & _
   "[Amount] = " & Me!Amount & ""
                                
    If DCount("*", "tblOpsRegister", strLinkCriteria) > 0 Then
        MsgBox "This document has already been sent to Document Control earlier." & vbCrLf & _
          "Press OK to lead you to the previous record." & vbCrLf & _
          "Please write the previous (Doc ID) at the ((back)) top to make sure it's actually matched.", vbCritical, "Duplicate Entry"
            
        Cancel = True
        Me.Undo
        Me.DataEntry = False
        Me.Recordset.FindFirst strLinkCriteria
        
         Else
          DocID = Nz(DMax("[DocID]", "tblOpsRegister"), 0) + 1
    Beep
     MsgBox "Please insure to write the Document ID and A/C number at the front top right of the document", vbOKOnly, "Notice"
        
    End If
  End If

Cleanup:
  Exit Sub
    
ErrorHandler:
   MsgBox Err.Number & ": " & Err.Description
  Beep
    MsgBox "You must enter a value in one of the following:" & vbCrLf & _
vbCr & "Account Number or Value Date.", vbOKOnly, "Empty Fields"

  Resume Cleanup
    
End Sub


Thanks a lot in advance

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Aug 2 2017, 12:51 PM
Post#29


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You don't have a question there! smile.gif

If you're getting an error message, what's the exact wording of the error? If you're not getting an error, what does the code do, and what do you want it to do instead?

--------------------
Go to the top of the page
 
Alhakeem1977
post Aug 2 2017, 04:21 PM
Post#30



Posts: 64
Joined: 8-July 17



big_grin.gif hhhhh actually I used this code you provided to me earlier as it works perfect for two text fields and one date field but unfortunately when I amend it for ( one text field called
AccountNo, one date field called ValueDate and one numeric field called Amount) when I entered a duplicate entry it warning me about duplicate entry and I got another message box says " The table 'tblOpsRegister' is already opened exclusively by another user,..." instead of leading me to the previous record.
I'm sure it because of amount field - Long Intiger - in strLinkCriteria (( "[Amount] = " & Me!Amount & "" ))
CODE
strLinkCriteria = "[AccountNo] = '" & Me!AccountNo & "' AND " & _
   "[ValueDate] = " & Format(Me!ValueDate, "\#dd-mmm-yyyy\#") & " AND " & _
   "[Amount] = " & Me!Amount & ""
.
If you could do your nice amendments as soon as possible because I am going to present it to my manager tomorrow.

For any clarifications please do not be hesitated to send your message.

Thanks a lot

Regards,
Redha

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Aug 2 2017, 07:14 PM
Post#31


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Well, the final quotes aren't necessary, but I doubt they're causing the problem:

CODE
strLinkCriteria = "[AccountNo] = '" & Me!AccountNo & "' AND " & _
   "[ValueDate] = " & Format(Me!ValueDate, "\#dd-mmm-yyyy\#") & " AND " & _
   "[Amount] = " & Me!Amount

Other than that, the syntax is correct.

Can you single-step through the code and determine exactly which line of code is raising the error?

--------------------
Go to the top of the page
 
Alhakeem1977
post Aug 3 2017, 01:21 PM
Post#32



Posts: 64
Joined: 8-July 17



Thanks dear Doug,
Yes the quotes not accusing the problem there is another thing in code, actually the form prevent the duplicate entry with worming message box of duplicate entry then instead of leading me to the previous record I got a message box from Microsoft said (No current record.), that's the issue.

Thanks my dear

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Aug 3 2017, 01:43 PM
Post#33


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Sorry, I don't understand what you're trying to describe.

--------------------
Go to the top of the page
 
Alhakeem1977
post Aug 17 2017, 04:27 PM
Post#34



Posts: 64
Joined: 8-July 17



First of all, I would like to thank you for your kind help.
Secondly I am so sorry for the delay, Actually the you provided to it works perfect, unfortunately my form wasn't bound to the table perfectly I selected again then it solve.

Thank you very much

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Aug 17 2017, 06:18 PM
Post#35


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Glad you got it working.

Good luck with your project! smile.gif

--------------------
Go to the top of the page
 
Alhakeem1977
post Oct 10 2017, 11:56 AM
Post#36



Posts: 64
Joined: 8-July 17



Hi Doug, hat_tip.gif
How are you,
I would like to express my gratitude for your kind efforts to provide me with the code that made a big change in my project.
Unfortunately, I faced a little problem in the form I added a text box showed the user the number of records entered during the session it works fine but when the user ented a duplicate entry then it will lead him to the previous record then the text will count all previous records with the current once been entered. I would be grateful if you could provide me with the amendment for the code that could be once the user click 'NewRecord' button the Findfirst command will be cancelled and get back to the current entries. Then the number of records will be counted normally.

Thanks again in advance.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 07:10 PM