dlookup help please (1 Viewer)

p4man

New member
Local time
Today, 03:57
Joined
Nov 10, 2018
Messages
10
I have a db one of the tables (TblScans) I have field called ProductName and a field called SapNo and a yes/no field called Repeated

In a form (frmScans) ProductName is entered as Text
then SapNo in its own field
I found from the net a dlookup that i use in SapNo Beforeupdate Event procedure if the Product name has been repeated it deletes it from the form but what I want it to do is change repeated yes /no to yes for that record
Here is the code I use at the moment

Private Sub SapNo_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("ProductName", "TblScans", _
"ProductName = " & Chr$(34) & Me!ProductName & Chr$(34))) _
And Me!ProductName <> Nz(Me!ProductName.OldValue) Then
Me.Undo
DoCmd.Close acForm, "FrmScans", acSaveYes
DoCmd.OpenForm "FrmScans", acNormal
Cancel = True

End If
End Sub

Can someone please help
 

Dreamweaver

Well-known member
Local time
Today, 10:57
Joined
Nov 28, 2005
Messages
2,466
If Not IsNull(DLookup("ProductName", "TblScans","[ProductName] = '" & Me!ProductName & "'"))

On my tab but think I got it
 

Cronk

Registered User.
Local time
Today, 21:57
Joined
Jul 4, 2013
Messages
2,770
Rather than undoing the change, closing/opening form use
Code:
If Not IsNull(DLookup("ProductName", "TblScans", _
    "ProductName = " & Chr$(34) & Me!ProductName & Chr$(34))) _
    And Me!ProductName <> Nz(Me!ProductName.OldValue)
   me.Repeated = false
endif


Incidentally, you can use chr$(34) or chr(34)
Using single quotes around text data cannot handle text if there is an embodied apostrophe.
 

p4man

New member
Local time
Today, 03:57
Joined
Nov 10, 2018
Messages
10
Thanks Cronk
Works after the "Then" and to "True"
Total problem fix Can't thank you enough
Stress relief Wow.
 

Users who are viewing this thread

Top Bottom