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 8 2017, 02:01 PM
Post#1



Posts: 64
Joined: 8-July 17



Your kind help to accurate this Ms Access VBA code to get the previous record after prevent duplicate in multi fields ( Combined together) it's work fone to prevent duplicate entry but not getting the previous record

below are the details:  

·         My primary key called (DocNo) Number field with DMax function.

·         Table Name: tblFacilityRegister

1.      AccountNo (text field)

2.      DocumentDate (Date field)

3.      DocumentName (text field)

·         Form Name: frmFacilityDocsRegister

1.      AccountNo (text field)

2.      DocumentDate (Date field)

3.      DocumentName (text field)

VBA code in AfterUpdate event

Private Sub DocumentName_AfterUpdate()

Dim NewAccountNo As String
Dim NewDocumentName As String
Dim NewDocumentDate As Date
Dim stLinkCriteria As String
Dim DocNo As Long
On Error Resume Next

''''''''''' Assign the entered customer name, Date and address to a variable
NewAccountNo = Me.AccountNo.Value
NewDocumentDate = Me.DocumentDate.Value
NewDocumentName = Me.DocumentName.Value

stLinkCriteria = "[AccountNo] = " & "'" & NewAccountNo & _
"' And [DocumentDate] = " & "#" & Format(Me![DocumentDate], "dd-mmm-yyyy") & _
"# And[DocumentName] = " & "'" & NewDocumentName & "'"


If Me.AccountNo = DLookup("[AccountNO]", "tblFacilityRegister", stLinkCriteria) Then
DoCmd.Beep
MsgBox " This Document Already Exists in Database " _
& vbCr & vbCr & " with this details: " & NewAccountNo & " " & NewDocumentName & " " & NewDocumentDate & "" _
& vbCr & vbCr & "Press OK to lead you to the previous record.", vbOKOnly, "Duplicate information"
'''''''''''' undo the process and clear all fields
Me.Undo
Me.Save.Enabled = False
Me.AllowEdits = False
'''''''''''''show the record of matched customer name and address from the customer tabl
DocNo = DLookup("[DocID]", "tblFacilityRegister", stLinkCriteria)
Me.DataEntry = False
DoCmd.FindRecord DocNo, , , , , acCurrent
End If
End Sub

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

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Jul 8 2017, 02:16 PM
Post#2


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


I don't understand why you've got this code in the AfterUpdate event. That event fires after the record's been saved. Wouldn't it make more sense to put the code in the BeforeUpdate event, where you can actually prevent the record from being saved? Me.Undo will not undo the saving of the record.

--------------------
Go to the top of the page
 
Alhakeem1977
post Jul 8 2017, 03:01 PM
Post#3



Posts: 64
Joined: 8-July 17



Thanks for your kind reply Mr. Doug wavehi.gif , as I said it works fine to alert user before entering new entry if it's available in the database that's why the undo for new record but unfortunately I can't get the previous record.
I got it from this site: http://www.iaccessworld.com/prevent-duplic...try-two-fields/
YouTube https://youtu.be/XXLXo4As61Y

If you have another VBA code can do the same please add it in your reply.

Thanks in advance

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

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Jul 8 2017, 03:56 PM
Post#4


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


My apologies. I hadn't noticed that you're using the AfterUpdate event of one of the text boxes, not the AfterUpdate event of the form. I still think it's preferable to use the BeforeUpdate event of the form. After all, you can't guarantee that the user is going to input the data in the same order as the text boxes (in other words, they could choose to enter DocumentName then DocumentDate, then AccountNo)

See whether the following works for you:

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
  
Dim rs As DAO.Recordset
Dim strLinkCriteria
  
  strLinkCriteria = "[AccountNo] = '" & Me!AccountNo & "' AND " & _
    "[DocumentDate] = " & Format(Me!DocumentDate, "\#dd-mmm-yyyy\#") & " AND " & _
    "[DocumentName] = '" & Me!DocumentName & "'"
    
  Set rs = Me.Recordset
  rs.FindFirst strLinkCriteria
  If rs.NoMatch Then
' record doesn't already exist.
  Else
    Msgbox "A record already exists with that AccountNo, DocumentDate and DocumentName." & vbCrLf & _
      "The record will not be saved.", vbCritical
    Cancel = True
  End If
  
Cleanup:
  Set rs = Nothing
  Exit Sub

ErrorHandler:
  MsgBox Err.Number & ": " & Err.Description
  Resume Cleanup
  
End Sub

--------------------
Go to the top of the page
 
Alhakeem1977
post Jul 8 2017, 11:06 PM
Post#5



Posts: 64
Joined: 8-July 17



Thanks for your reply I'm the one how should apologies not you because I'm taking your valued time.
Actually I tried the code but I got an error: (3426: This action was cancelled by an associated object).
For your kind notice my form under (Data Entry)is set to Yes and I preferred to be in this mode.

Thanks a lot

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

Al Hakeem1977
Go to the top of the page
 
tina t
post Jul 9 2017, 04:17 AM
Post#6



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


QUOTE
For your kind notice my form under (Data Entry)is set to Yes and I preferred to be in this mode.

if your goals are to 1) prevent entering a duplicate record and 2) go to the record already entered, then your form cannot be set to DataEntry = Yes (True).

DataEntry mode specfically blocks all records entered in previous sessions of the form from being part of the form's current recordset. even if you search for the "previous" record at the table level, rather than searching the form's current recordset, you can't display that record in the form, as long as the form's DataEntry mode is set to Yes (True).

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
Alhakeem1977
post Jul 9 2017, 07:10 AM
Post#7



Posts: 64
Joined: 8-July 17



CODE
if your goals are to 1) prevent entering a duplicate record and 2) go to the record already entered, then your form cannot be set to DataEntry = Yes (True).

Thanks for your reply hat_tip.gif actually when you add (Me.DataEntry = False) in your code after catch the duplicate entry it will run your form to view the previous records in your database, if you see my code (Dlookup function) it has this facility by the way I saw it in the Youtube https://youtu.be/XXLXo4As61Y it works fine unfortunately, it doesn't work with me.
Now my aim to get the previous entry only because the code prevent duplicates and works fine.

Thanks again

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

Al Hakeem1977
Go to the top of the page
 
Alhakeem1977
post Jul 9 2017, 08:48 AM
Post#8



Posts: 64
Joined: 8-July 17



Dear All,
Please find attached my database.
Attached File(s)
Attached File  Credit_DC_Register.zip ( 236.42K )Number of downloads: 16
 

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

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Jul 9 2017, 09:28 AM
Post#9


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


Just a comment. If you're going to post your database, you also need to post instructions on what form to open and what data to use to see the problem.

--------------------
Go to the top of the page
 
tina t
post Jul 9 2017, 10:12 AM
Post#10



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


you're right, Alhakeem, i didn't notice the change in setting for the DataEntry property. very good. changing the DataEntry property setting does not automatically requery the form's Recordset, though. so try adding a line of code to do that, before doing the search, as

...
...
Me.DataEntry = False
Me.Requery
DoCmd.FindRecord DocNo, , , , , acCurrent
...
...

--------------------
"you can't take the sky from me"
Go to the top of the page
 
Alhakeem1977
post Jul 9 2017, 04:30 PM
Post#11



Posts: 64
Joined: 8-July 17



Yes you are right Mr. Doug I'm sorry for that.
First ignore all forms.

Open form: frmFacilityDocsRegister it will populate (tblFacilityRegister)
Then try to pass an entry you will get the error when you press save.
Obviously, my aim to prevent duplicate entry and show the previous record
to the user.
The database will be as electronic register between two Departments which can retrieve the user from the public network.

Please do not be hesitated for further clarifications.
Im sorry again for the disturbance.

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

Al Hakeem1977
Go to the top of the page
 
Alhakeem1977
post Jul 9 2017, 04:34 PM
Post#12



Posts: 64
Joined: 8-July 17



Thanks dear I will try it then I will let you know.

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

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Jul 9 2017, 04:50 PM
Post#13


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


As mentioned before, I feel using the form's BeforeUpdate event is much more appropriate than using the AfterUpdate event of the various controls, as that won't work if they happened to enter DocumentName before they enter AccountNo or DocumentDate.

Try this:

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 "A record already exists with that AccountNo, DocumentDate and DocumentName." & vbCrLf & _
            "The record will not be saved.", vbCritical
        Cancel = True
        Me.Undo
        Me.DataEntry = False
        Me.Recordset.FindFirst strLinkCriteria
  End If
  
Cleanup:
  Exit Sub

ErrorHandler:
  MsgBox Err.Number & ": " & Err.Description
  Resume Cleanup
  
End Sub

--------------------
Go to the top of the page
 
Alhakeem1977
post Jul 10 2017, 01:51 AM
Post#14



Posts: 64
Joined: 8-July 17



applause.gif (Solved)Thank you very much Mr. Doug it works perfect with no issues, you explain in such a way that anyone could understand.
I do confirm the last code works perfect.
By the way I would like to thank you tina t too for your nice comments.

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

Al Hakeem1977
Go to the top of the page
 
Alhakeem1977
post Jul 10 2017, 05:11 AM
Post#15



Posts: 64
Joined: 8-July 17



Dear Duog,
One more favour if you could, I want to use the same table(tblFacilityRrgister)
through create another form to be used by another Department to show them only the due tasks of records which not yet populated(the same row): ReceiverID, ReceiverName from function Environ("Username") and 'Received' field once select "yes" then the database will populate with this three fields together.
In another word the table aim is documents Register between two Departments to get Sender Name, Receiver Name and Documents details.

Thanks a lot in advance thanks.gif

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

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Jul 10 2017, 06:55 AM
Post#16


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


Sorry, I'm going to be on the road today, unable to look at this.

Conceptually, what you need to do is create a query that returns the details and use that query as the RecordSource of your form. I haven't looked at your database closely enough to see whether you've got the necessary tables to generate a list of all of the possible documents so that you can figure out which of those possible documents have not yet been received.

--------------------
Go to the top of the page
 
Alhakeem1977
post Jul 10 2017, 09:51 AM
Post#17



Posts: 64
Joined: 8-July 17



Why you sorry, I'm the one who should say it because I am disturbing you I appreciate your helpfulness. hat_tip.gif

Ok, I will try to create the form as you said, I will let you know if it's fixed.

thanks.gif

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

Al Hakeem1977
Go to the top of the page
 
Alhakeem1977
post Jul 10 2017, 09:51 AM
Post#18



Posts: 64
Joined: 8-July 17



welcome2UA.gif

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

Al Hakeem1977
Go to the top of the page
 
Alhakeem1977
post Jul 11 2017, 10:25 AM
Post#19



Posts: 64
Joined: 8-July 17



Thanks Mr. Duog, I created the query as said I assigned it records source on Received field of the table if it's empty then created the form on the query.
It works great as I nead, but only the issue how can alert the Receiver about there are tasks to accomplish ?

Thanks in advance

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

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Jul 11 2017, 02:49 PM
Post#20


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


Easiest way would probably be to associate some code with the OnCurrent event of the form. Have the code determine whether there are any rows that need attention, and pop up a message box if so.

--------------------
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 10:21 AM