Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-10-2018, 02:21 PM   #1
p4man
Newly Registered User
 
Join Date: Nov 2018
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
p4man is on a distinguished road
dlookup help please

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

p4man is offline   Reply With Quote
Old 11-10-2018, 03:16 PM   #2
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: dlookup help please

If Not IsNull(DLookup("ProductName", "TblScans","[ProductName] = '" & Me!ProductName & "'"))

On my tab but think I got it
__________________
After 20 years working with access i have no more hair to give.
MickJav is offline   Reply With Quote
Old 11-10-2018, 03:42 PM   #3
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,908
Thanks: 2
Thanked 411 Times in 404 Posts
Cronk will become famous soon enough
Re: dlookup help please

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.

Cronk is offline   Reply With Quote
Old 11-11-2018, 01:11 AM   #4
p4man
Newly Registered User
 
Join Date: Nov 2018
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
p4man is on a distinguished road
Re: dlookup help please

Thanks Cronk
Works after the "Then" and to "True"
Total problem fix Can't thank you enough
Stress relief Wow.

p4man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Nest dlookup inside a dlookup Djblois Modules & VBA 2 06-29-2011 12:06 PM
DLookup within DLookup not working brunopg7 Queries 3 04-22-2011 07:08 AM
DLookup using another Dlookup result stu999 Queries 4 11-09-2010 07:02 AM
Dlookup.... doran_doran Queries 4 03-18-2004 09:04 PM
Dlookup (then) Dlookup Next Record, How? musicmaker Forms 1 01-18-2001 09:16 AM




All times are GMT -8. The time now is 08:37 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World