Return the Recordset.FindFirst strLinkCriteria in a message box (1 Viewer)

[Alhakeem1977]

Your kind help to make the below code return the result in the message box instead of returning the previous record in the form.

Obviously this part:

Me.Recordset.FindFirst strLinkCriteria

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

Dim strLinkCriteria As String
Dim strMessage As String
Dim DocID As Integer

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

       Me.SeqNo = Nz(DMax("[SeqNo]", "tblFacilityRegister", "Year([SentDate]) = " & Year(Me.[SentDate])), 0) + 1
       Me.DocID = Format([SeqNo], "0000") & "/" & Format([SentDate], "yy")
    Me.img1.Visible = False
    Me.DocID.BackColor = vbRed
    Me.Detail.BackColor = vbGreen
    Me.New_Record.SetFocus
    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

Thanks in advance.

 

[arnelgp]

strLinkCriteria = "[AccountNo] = " & Me!AccountNo & " AND " & _
        "[DocumentDate] = " & Format$(Me!DocumentDate, "\#mm\/dd\/yyyy\#") & " AND " & _
        "[DocumentName] = " & Me!DocumentName

If AccountNo , DocumentName are both string delimit them:

strLinkCriteria = "[AccountNo] = " & chr(34) & Me!AccountNo & chr(34) &  " AND " & _
        "[DocumentDate] = " & Format$(Me!DocumentDate, "\#mm\/dd\/yyyy\#") & " AND " & _
        "[DocumentName] = " & chr(34) & Me!DocumentName & chr(34)

 

[Alhakeem1977]

Thanks for your earliest response, my aim is how to get the previous record details after capture the duplicate entry in a message box instead of getting it in the form?

Could you restructure the entire code or any member of this nice site?

I am so sorry to bother you with me.

Thanks a lot.

 

[Alhakeem1977]

I would like to get it in the below message box:

If DCount("*", "tblFacilityRegister", strLinkCriteria) > 0 Then
    Me.img1.Visible = True
        MsgBox "This document has already been sent earlier!" & vbCrLf & _
          "Press OK to lead you to the previous record." & vbCrLf & _
          "Please write the(Doc ID) at the ((back)) top to make sure it's actually matched." & vbCrLf & _
          "Ignore the number of records.", vbCritical, "Duplicate Entry"

AccountNo and DocumentName both are integers from combo boxes.

 

[The_Doc_Man]

my aim is how to get the previous record details after capture the duplicate entry in a message box instead of getting it in the form

My question is related to the obvious fact that English is not your first language and therefore some multi-valued options exist in how we can interpret your question. Let me try to re-state this. You can confirm or correct it. If we can agree on your goal, we can have a chance at getting you there.

You have a form where you enter data - but sometimes users enter data that has been entered before based on some tests on key fields. If you detect a duplicate, you want to notify them that the data set has already been entered.

It is the next part that becomes syntactically confusing. Are you saying that you want to query the record that the new entry would duplicate, and that you then want to show non-key details of that entry? You already know the key details because you found that the form's current record would duplicate the keys, and therefore the non-key details are already available for the new record. But you want to see the non-key details of the original record. And you want to somehow show them in the message box.

Is that what you are asking?

 

[Alhakeem1977]

English is not your first language

Yes, is it, I'm sorry for that, I know it's very hard for you dears.
But I know that I will get my want from this nice site as usual.

But you want to see the non-key details of the original record. And you want to somehow show them in the message box.

Is that what you are asking?

Yes, that is exactly what I want.

 

[arnelgp]

So what info do you need to show?
Also you must use .RecordsetClone and not .Recordset, so that the record in the form does not move.

 

[Alhakeem1977]

So what info do you need to show?
Also you must use .RecordsetClone and not .Recordset, so that the record in the form does not move.

I want to get the DocID of the previous record which is the most important info.

 

[arnelgp]

...
...
If DCount("*", "tblFacilityRegister", strLinkCriteria) > 0 Then
    Me.img1.Visible = True
    With Me.RecordsetClone
         .FindFirst strLinkCriteria
        MsgBox "This document has already been sent earlier!" & vbCrLf & _
          "Doc ID is " & !DocID & vbCrLf & _
          "Press OK to lead you to the previous record." & vbCrLf & _
          "Please write the(Doc ID) at the ((back)) top to make sure it's actually matched." & vbCrLf & _
          "Ignore the number of records.", vbCritical, "Duplicate Entry"
         End With
        Cancel = True
        Me.Undo
        Me.DataEntry = False
        Me.AllowEdits = False
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'Me.Recordset.FindFirst strLinkCriteria
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Me.New_Record.SetFocus
        Me.txtDocsToBeSent.Requery
      
        ...
        ...

 

[Alhakeem1977]

That is exactly what I want but I got an error message 3021 for the first time.

Thanks again for your patience.

 

[arnelgp]

Remove the With me.recordsetclone ... end with
Also the FindFirst.

Instead if dcount() use dlookup():

Dim varID
varID= dlookup("DocID", "tblFacilityRegister", strLinkCriteria)
If Not isnull(varID) Then
MsgBox "This document has already been sent earlier!" & vbCrLf & _
"Doc ID is " & varID & vbCrLf & _
"Press OK to lead you to the previous record." & vbCrLf & _
"Please write the(Doc ID) at the ((back)) top to make sure it's actually matched." & vbCrLf & _
"Ignore the number of records.", vbCritical, "Duplicate Entry"

 

[Alhakeem1977]

Thanks for your quick reaction, I'll try the code after waking up tomorrow and I'll let you know about the result.
Thank you so much

 

[Alhakeem1977]

Thank you so much arnelgp for your code you provided to me, it works without any error.
I do not know how to thank you for your help.

 

[arnelgp]

You are welcome.