Dlookup returning incorrect results

[jex57]

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

 

[xenou]

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)

 

[jex57]

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

 

[xenou]

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?