VBA code on Access Form, recording lngID as 0 in Access 2016 (1 Viewer)

robarthur

New member
Local time
Today, 16:53
Joined
Aug 28, 2019
Messages
2
Access 365 Version 1907 which, I think, is Access 2016 equivalent.

Using the following code from the datawright.com site in Australia, I have set up Track Changes on a form in my database. So every time data in a field is changed, the changes are saved to a separate table, along with the record number (or NOT with the record number, in my case)

Function LogChanges(lngID As Long, Optional strField As String = "")
'haven't managed to get it to capture the record number yet. Annoyingly.
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varOld As Variant
Dim varNew As Variant
Dim strFormName As String
Dim strControlName As String

varOld = Screen.ActiveControl.OldValue
varNew = Screen.ActiveControl.Value
strFormName = Screen.ActiveForm.Name
strControlName = Screen.ActiveControl.Name
Set dbs = CurrentDb()
Set rst = dbs.TableDefs("tbl_100_Track_Changes").OpenRecordset

With rst
.AddNew
!FormName = strFormName
!ControlName = strControlName
If strField = "" Then
!FieldName = strControlName
Else
!FieldName = strField
End If
!RecordID = lngID
!UserName = Environ("username")
If Not IsNull(varOld) Then
!OldValue = CStr(varOld)
End If
!NewValue = CStr(varNew)
.Update
End With
'clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function

Private Sub BusinessImpactDescption_BeforeUpdate(Cancel As Integer)
Dim PolicyID As Long
Call LogChanges(PolicyID)
End Sub

I have spent the day trying to get the code to report the record number in my Track Changes table:

LogID FormName ControlName FieldName RecordID UserName OldValue NewValue TimeStamp
15 frm_002_All_Policies Summary Summary 0 robarthur Test 28/08/2019 14:13:35
16 frm_001_Policies Summary Summary 0 robarthur Final Attempt 28/08/2019 14:20:48
17 frm_001_Policies Relevant Legislation Relevant Legislation 0 robarthur Final Attempt 28/08/2019 14:20:49
18 frm_001_Policies Underpinning Knowledge Underpinning Knowledge 0 robarthur Final Attempt 28/08/2019 14:20:53
19 frm_001_Policies Summary Summary 0 robarthur Second attempt 28/08/2019 14:20:57
20 frm_001_Policies Definitions Definitions 0 robarthur Final attempt2 28/08/2019 14:21:13
21 frm_001_Policies Definitions Definitions 0 robarthur Plus one 28/08/2019 14:21:15
22 frm_001_Policies Summary Summary 0 robarthur Final test 28/08/2019 14:22:59
23 frm_001_Policies Summary Summary 0 robarthur Final test 28/08/2019 14:23:06
24 frm_001_Policies Summary Summary 0 robarthur test 28/08/2019 14:35:54

Everything works fine, but the RecordID always returns a zero. I've disabled the Default Value, so it is actually returning a zero.

If anyone has any ideas on this one, it will save me from chucking the whole kit out of the window!

All the other fields populated first time. It's just this RecordID that's caused me misery all day.

Thanks for your help.

Rob
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:53
Joined
May 7, 2009
Messages
19,169
its here that is fault:

Private Sub BusinessImpactDescption_BeforeUpdate(Cancel As Integer)
Dim PolicyID As Long
Call LogChanges(PolicyID)
End Sub

you declare PolicyId as long but never put a numeric value to it.
maybe something like:

PolicyID=Me.ID
Call LogChanges(PolicyID)
 

robarthur

New member
Local time
Today, 16:53
Joined
Aug 28, 2019
Messages
2
Amazing. I could cry!
I've been on that for hours and hours.
Wish I'd posted it before.
Thank you so much for your help.
What a Star!

Thanks again,

Rob
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:53
Joined
Oct 29, 2018
Messages
21,358
Hi Rob. Welcome to AWF!


You could also just use the ID directly like:
Code:
Call LogChanges(Me.ID)
Access gives you choices!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:53
Joined
May 7, 2009
Messages
19,169
If you did not include that one piece of sub, we'll take pages on this forum just troubleshooting where went wrong.

goodluck!
 

Users who are viewing this thread

Top Bottom