what is wrong with this code please?

siamandm
5StarLounger
Posts: 976
Joined: 01 May 2016, 09:58

what is wrong with this code please?

Post by siamandm »

Hello all
I have a form for data entry
when the user puts a duplicate data into the field food_cart_no he will get a notification box that this number already used, and after he clicks ok it supposes to open the existing data. but it is not loading

any help please

Code: Select all

Private Sub Food_cart_NO_AfterUpdate()
Dim Prevent As String
Dim Prevent2 As String
Dim Prevent3 As Integer
Prevent = Me.Food_cart_NO.Value
Prevent2 = "[Food_cart_NO] = " & "'" & Prevent & "'"
If Me.Food_cart_NO = DLookup("[Food_cart_NO]", "Poor", Prevent2) Then
MsgBox "This Food Card No ," & Prevent & ", has already been entered in Database." _
& vbCr & vbCr & "please chek name again.", vbInformation, "Duplicae information"
Me.Undo
Prevent3 = DLookup("[ID]", "Poor", Prevent2)
Me.DataEntry = False
DoCmd.FindRecord Prevent3, , , , acCurrent
End If
End Sub

User avatar
HansV
Administrator
Posts: 69402
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: what is wrong with this code please?

Post by HansV »

You're missing a comma. The line

Code: Select all

DoCmd.FindRecord Prevent3, , , , acCurrent
should be

Code: Select all

        DoCmd.FindRecord Prevent3, , , , , acCurrent
I'd prefer to write it as

Code: Select all

        DoCmd.FindRecord FindWhat:=Prevent3, OnlyCurrentField:=acCurrent
That way you don't have to count the commas.
Regards,
Hans

siamandm
5StarLounger
Posts: 976
Joined: 01 May 2016, 09:58

Re: what is wrong with this code please?

Post by siamandm »

Thanks a lot for the reply, now is working fine.

Regards

siamandm
5StarLounger
Posts: 976
Joined: 01 May 2016, 09:58

Re: what is wrong with this code please?

Post by siamandm »

Dear Hans,

the issue cam again and now I'm getting this error windows
Annotation 2020-08-29 164134.jpg
Annotation 2020-08-29 164218.jpg
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 69402
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: what is wrong with this code please?

Post by HansV »

What is the value of Prevent3 when this error occurs?

You can find out by hovering the mouse pointer over Prevent3 when the code is paused, or by entering

Code: Select all

? Prevent3
in the Immediate window and pressing Enter.
Regards,
Hans

siamandm
5StarLounger
Posts: 976
Joined: 01 May 2016, 09:58

Re: what is wrong with this code please?

Post by siamandm »

i got this
Annotation 2020-08-29 173818.jpg
when I try to put the duplicate number in Food_cart_NO field

i entered 6818827 in this field: Food_cart_NO
so we want to load the information of the 6818827 in the current form after it detected its duplicated..
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 69402
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: what is wrong with this code please?

Post by HansV »

Does this work better?

Code: Select all

Private Sub Food_cart_NO_AfterUpdate()
    Dim Prevent As String
    Dim Prevent2 As String
    Prevent = Me.Food_cart_NO.Value
    Prevent2 = "[Food_cart_NO] = " & "'" & Prevent & "'"
    If Me.Food_cart_NO = DLookup("[Food_cart_NO]", "Poor", Prevent2) Then
        With Me.RecordsetClone
            .FindFirst Prevent2
            If Not .NoMatch Then
                MsgBox "This Food Card No '" & Prevent & "' has already been entered in Database." & _
                    vbCr & vbCr & "Please check name again.", vbInformation, "Duplicate information"
                Me.Undo
                Me.DataEntry = False
                Me.Bookmark = .Bookmark
            End If
        End With
    End If
End Sub
Regards,
Hans

siamandm
5StarLounger
Posts: 976
Joined: 01 May 2016, 09:58

Re: what is wrong with this code please?

Post by siamandm »

thanks a lot for the reply,,

I tried your code but it allows duplicate Food_cart_NO entry

I would like to add some notes about this database:

this database is for my friend i have checked other parts of the database and found that
- the table which saves the new records has some empty data for the Food_Cart_No field, and the table itself allows duplicate entry.
- he mentioned that it returned correct information for some Food Cart No, but for some gets another person information.





regards

User avatar
HansV
Administrator
Posts: 69402
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: what is wrong with this code please?

Post by HansV »

You could create an index on the Food_Cart_No field, with Unique set to Yes. Access won't accept duplicates.

S3489.png
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

siamandm
5StarLounger
Posts: 976
Joined: 01 May 2016, 09:58

Re: what is wrong with this code please?

Post by siamandm »

ok, and we want the data entry form, to show a message box when a duplicate value interned, and after i click Ok show the records for that value.

how we do this, please.

Regards

User avatar
HansV
Administrator
Posts: 69402
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: what is wrong with this code please?

Post by HansV »

Could you attach a stripped-down and zipped copy of the database?
Regards,
Hans

siamandm
5StarLounger
Posts: 976
Joined: 01 May 2016, 09:58

Re: what is wrong with this code please?

Post by siamandm »

I have deleted all the data,
and now is working fine, when I try to add a record with duplicated Food_cart_NO. it shows me a message box and after I click ok, it load the data for the duplicated data.

but the issue why with the big data in the table it shows the data for the wrong person?

Code: Select all

Dim Prevent As String
Dim Prevent2 As String
Dim Prevent3 As Integer
Prevent = Me.Food_cart_NO.Value
Prevent2 = "[Food_cart_NO] = " & "'" & Prevent & "'"
If Me.Food_cart_NO = DLookup("[Food_cart_NO]", "Poor", Prevent2) Then
MsgBox "This Food Card No ," & Prevent & ", has already been entered in Database." _
& vbCr & vbCr & "please chek name again.", vbInformation, "Duplicae information"

Me.Undo

Prevent3 = DLookup("[ID]", "Poor", Prevent2)
Me.DataEntry = False
DoCmd.FindRecord Prevent3, , , , acCurrent
End If
End Sub

User avatar
HansV
Administrator
Posts: 69402
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: what is wrong with this code please?

Post by HansV »

I repeat the request in my previous reply.
Regards,
Hans

User avatar
HansV
Administrator
Posts: 69402
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: what is wrong with this code please?

Post by HansV »

Try this version:

Code: Select all

Private Sub Food_cart_NO_AfterUpdate()
    Dim Prevent As String
    Dim Prevent2 As String
    Dim Prevent3 As Integer
    Prevent = Me.Food_cart_NO.Value
    Prevent2 = "[Food_cart_NO] = " & "'" & Prevent & "'"
    If Me.Food_cart_NO = DLookup("[Food_cart_NO]", "Poor", Prevent2) Then
        MsgBox "This Food Card No ," & Prevent & ", has already been entered in Database." _
            & vbCr & vbCr & "please chek name again.", vbInformation, "Duplicae information"
        Me.Undo
        Prevent3 = DLookup("[ID]", "Poor", Prevent2)
        Me.DataEntry = False
        Me.ID.SetFocus ' This line is NEW
        DoCmd.FindRecord Prevent3, , , , acCurrent
    End If
End Sub
Regards,
Hans

siamandm
5StarLounger
Posts: 976
Joined: 01 May 2016, 09:58

Re: what is wrong with this code please?

Post by siamandm »

thank you very much now is working find.

Regards