Dlookup returning incorrect results

jex57

Board Regular
Joined
Oct 29, 2015
Messages
62
Hi all,
I have a form that before updating, I want to store the old value of one particular field. The original field is an integer, and I want to store the text description.

My code looks like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)​

Dim FormName As String​
Dim OldBusinessterm As Long​
Dim testfield As Long​
Dim NewBusinessTerm As Long​
Dim NewRec As DAO.Database​
Dim rstUpdate As DAO.Recordset​
Dim MyBusinessTerm As Variant​



OldBusinessterm = Me!BusinessTermID.OldValue​
NewBusinessTerm = Me!BusinessTermID.Value​

MyBusinessTerm = DLookup("businesstermdesc", "tblbusinessterm", "BusinessTermID = " & OldBusinessterm)​


Set NewRec = CurrentDb​
Set rstUpdate = NewRec.OpenRecordset("TblFieldTermLink")​

rstUpdate.AddNew​
rstUpdate("GTSBusinessTerm").Value = MyBusinessTerm​
rstUpdate("BusinessTermID").Value = Me!BusinessTermID.Value​
rstUpdate.Update​
End Sub​


My original value of business term is 5914 - stored in OLD Business Term
My New value is 5915 - Stored in New business term.


What the DLOOKUP is doing - its populatating
DLookup("businesstermdesc" - From the new term

"BusinessTermID = " - From the new term

and then trying to match 5915 to my old term of 5914

Can anyone estabish what I have done incorrectly?

TIA
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
YOu say here that you are looking up new business term:
What the DLOOKUP is doing - its populatating
DLookup("businesstermdesc" - From the new term

"BusinessTermID = " - From the new term

But you actually look up the old term:
MyBusinessTerm = DLookup("businesstermdesc", "tblbusinessterm", "BusinessTermID = " & OldBusinessterm)
 
Upvote 0
Hi
What I am supposed to be doing is doing the dlookup on the OLD value and not the new value.

I dont want the new value.
I have a number of business terms for example
A Title
B Title Code
C Person Title

But some of these terms are not needed as in essence they all imply the same thing.
So what I want to do is when a person changes Title Code - to rather Read title. I want to store in a seperate table
Title - Title Code

So that I dont loose the data for later reference - almost like an audit trail, but only on two fields whereby I store the old value and the new value.

The issue is I can store the new value as an ID - this works
I need to store the old value as the description and not the ID.

So my dlookup - needs to look up the old value description and write that to the table.
I am picking up the old value correctly
I am picking up the new value correctly,

But what the Dlookup does in context:

Dlookup (NEW BUSINESS TERM DESC, TBLBUSINESSTERM, NEWBUSINESSTERMID = OLD BUSINESSTERMID)
(these are the values displayed when I hover over the fields.)

Which will clearly not work.
What I expect to see is
Dlookup (Not defined until dlookup runs, TBLBUSINESSTERM, BUSINESSTERMID from table = OLD BUSINESSTERMID)


Hope this makes more sense
 
Upvote 0
Hi, its not clear. Are you trying to look up the ID in the same table that you are updating? Why don't you know what field you are looking in until dlookup runs?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top