Save Record if table field is changed (1 Viewer)

ple04

New member
Local time
Today, 11:30
Joined
Dec 19, 2017
Messages
7
Problem:
I created a form that is attached to a Linked Table and not an access table. The form saves the information to the linked table which is about 8-10 fields. My problem is I am getting duplicates saved to the form which no fields changed & I don't want that to be saved. It is a history table but I only want to save it if a field value changed or a blank field changed to having a value. Below is an example

Example of what i am looking for
Fields
Record 1 = 4 4 4 - - 4 4
Record 1 = 4 4 4 4 - 4 4
Record 1 = 4 5 4 4 - 4 5

But instead I getting this below duplicates
Fields
Record 1 = 4 4 4 - - 4 4
Record 1 = 4 4 4 - - 4 4
Record 1 = 4 4 4 - - 4 5

It will save a record if its the same.

Here is also my code.. Thank you again!


' On Error GoTo Error_trap
Dim rs As DAO.Recordset
Dim flag As Boolean
Dim Dbs As DAO.Database
'MsgBox ("Form_Unload")
Cancel = False
flag = validateGS(False)
If flag = False Then
Cancel = True
Me!GS_L.SetFocus

'Writes to the RB_TBL
Else
Set Dbs = CurrentDb
Set rs = Dbs.OpenRecordset("RB_TBL")
Debug.Print " start code here "
rs.Edit
rs!LOC_ID = Me.LOC_ID
rs!GS_L = Me.GS_L
rs!GS_LO = Me.GS_LO
rs!REF = Me.REF
rs!GS_DA = Me.GS_DA
rs!GS_P = Me.GS_P
rs!WH_L = Me.WH_L
rs!WH_LO = Me.WH_LO
rs!OP_L = Me.OP_L
rs!OP_LO = Me.OP_LO
rs!OP_REF= Me.OP_REF
rs!OP_DAT = Me.OP_DAT
rs!OP_REP = Me.OP_REP
rs!INSERT_DATE = Now()
rs.Update
Debug.Print " close code here "
rs.Close
Set rs = Nothing

End If

' Exit Sub
'Error_trap:
' Debug.Print "Error: " & Err.Number & vbTab & Err.Description
' MsgBox "Error: " & Err.Number & vbTab & Err.Description
' If Err.Number = 123456 Then
' MsgBox "XXXXX"
' Else
'
' End If
'
'Exit Sub
'Resume
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:30
Joined
Feb 19, 2002
Messages
42,970
I know you've posted this before so I don't understand how this can be your FIRST post. I'm pretty sure the question was answered but I'll do it again.

1. You've extracted the code so we are not looking at it in context. The code MUST run in the Form's AFTERUpdate event because you only want to run it if the user made some change.
2. If users are inclined to simply change a value to the same value causing the record to be dirty and forcing Access to save it, you'll need to add smarter code. Add a form level global variable. In the Current event, set it to False. Then you need code in the AfterUpdate event of every single CONTROL on the form. The code will compare the .value property with the .OldValue. If they are different, the code sets the global variable to True. Then in the BeforeUpdate event, you only save the history record if the global is True. The final step is to set the global to False again in the form's AfterUpdate event.

Code:
If Me.txtUserName & "" <> Me.txtUserName.OldValue & "" Then
    qUserUpdate = True
End If

The code concatenates a ZLS to each side of the equation to eliminate any problem caused by null values.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:30
Joined
May 7, 2009
Messages
19,169
What is LOC_ID? is this the Primary Key?
if it is you should first find this LOC_ID
in the Recordset rs, before doing the saving part:

Code:
...
...

Else
	Set Dbs=CurrentDB
	Set rs = Dbs.OpenRecordSet("RB_TBL", dbOpenDynaset)
	With rs
		.FindFirst "LOC_ID = " & ME.LOC_ID
		IF NOT .NoMatch
			.Edit
			!GS_L=Me.GS_L
			!GS_LO=Me.GS_LO
			!REF=Me.REF
			...
			...
			...
			.Update
		End If
		.Close
	End With
	Set rs=Nothing

End If
End Sub
 

ple04

New member
Local time
Today, 11:30
Joined
Dec 19, 2017
Messages
7
hey Arnelgp,

Yes the LOC_ID is the primary Key, and having multiple primary keys is fine.

but the If Not .NoMatch is throwing me an error

appreciate everyone's help!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:30
Joined
May 7, 2009
Messages
19,169
If the linked tabke us not access, use

If not .eof

Instead of .nomatch
 

ple04

New member
Local time
Today, 11:30
Joined
Dec 19, 2017
Messages
7
Still throwing an error, The tables are linked and not access.

Code:
' On Error GoTo Error_trap
Dim rs As DAO.Recordset
Dim flag As Boolean
Dim Dbs As DAO.Database
'MsgBox ("Form_Unload")
Cancel = False
flag = validateGS(False)
If flag = False Then
Cancel = True
Me!GS_L.SetFocus

'Writes to the RB_TBL
 Else
        Set Dbs = CurrentDb
        Set rs = Dbs.OpenRecordset("RB_TBL", dbOpenDynaset)
        Debug.Print " start code here "
        With rs
        .FindFirst "LOC_ID = " & Me.LOC_ID
Debug.Print " start code here "
If Not . EOF
rs.Edit
rs!LOC_ID = Me.LOC_ID
rs!GS_L = Me.GS_L
rs!GS_LO = Me.GS_LO
rs!REF = Me.REF
rs!GS_DA = Me.GS_DA
rs!GS_P = Me.GS_P
rs!WH_L = Me.WH_L
rs!WH_LO = Me.WH_LO
rs!OP_L = Me.OP_L
rs!OP_LO = Me.OP_LO
rs!OP_REF= Me.OP_REF
rs!OP_DAT = Me.OP_DAT
rs!OP_REP = Me.OP_REP
rs!INSERT_DATE = Now()
rs.Update
Debug.Print " close code here "
End If
rs.Close

End With
Set rs = Nothing

End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:30
Joined
May 7, 2009
Messages
19,169
use this instead:

Code:
..
..
ELSE
	Set Dbs = CurrentDb()
	Set rs = Dbs.OpenRecordset("SELECT * FROM RB_TBL WHERE LOC_ID=" & Me.LOC_ID)
	
	If Not (rs.BOF And rs.EOF) Then
		rs.MoveFirst
		rs.Edit
		rs!GS_L = Me.GS_L
		rs!GS_LO = Me.GS_LO
		...
		...
		rs.Update
	End If
	rs.Close
	Set rs = Nothing
End If
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:30
Joined
Feb 19, 2002
Messages
42,970
You are off down a rabbit hole. pie04 wants to insert a COPY of any record that was CHANGED. This code is an attempt to log record changes in a HISTORY table. He is not looking to update existing records. The whole point of a log is to see all the changes.

The problem is what I suggested in my original post. Please refer to that. The problem is most likely that the code is in the wrong event.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:30
Joined
May 7, 2009
Messages
19,169
Youre off the bat hole. Refer to post 1. He is editing on his code.
 

ple04

New member
Local time
Today, 11:30
Joined
Dec 19, 2017
Messages
7
Sorry for the confusion, Pat is on the path i am trying to get to. I am new to VBA, so don't mean to cause any thing.

I want to insert a copy of any record that has changed, pretty much i want to copy every record as a new record if a field has been changed but not copy if nothing has been changed. I want to keep track of every change that happens to a record and not over ride the existing one.

Sorry again for the confusion!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:30
Joined
Feb 19, 2002
Messages
42,970
ple04. Did you read my post? Did you understand it? Did you act on it? WHERE (in which event) is your code running??????????????????????

arnel -
My problem is I am getting duplicates saved to the form which no fields changed & I don't want that to be saved. It is a history table but I only want to save it if a field value changed or a blank field changed to having a value.
The first sentence is confusing since records are not saved to forms.
 
Last edited:

ple04

New member
Local time
Today, 11:30
Joined
Dec 19, 2017
Messages
7
I did read your post, and i did not understand it sorry. like i said i am extremely new to this.

The code is in a Private Sub Form_Unload(Cancel As Integer)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:30
Joined
Feb 19, 2002
Messages
42,970
Thank you. Now read the part where it said which event to use.
The code MUST run in the Form's AFTERUpdate event because you only want to run it if the user made some change.
If that doesn't fix the problem, you will have to consider #2.

By running the code in the unload event, the code runs EVERY SINGLE TIME the form is opened regardless of whether any data was modified. Since you are logging changes, it makes sense to run the code ONLY when the record is modified.
 
Last edited:

ple04

New member
Local time
Today, 11:30
Joined
Dec 19, 2017
Messages
7
could help explain the second option, thank you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:30
Joined
Feb 19, 2002
Messages
42,970
So, you moved the code to the AfterUpdate event and that didn't fix the problem?

The second option is only necessary if people modify a control by changing it to the same value. Although this does happen on occasion, it shouldn't be necessary to code around. If this happens frequently, then it is most likely that you have caused the problem yourself by having your own code dirty a record before a user has ever touched it.

Do you have code that sets values in controls? What event is it in? If it is not in the Form's BeforeInsert, BeforeUpdate, or Dirty events, then YOU are dirtying the record yourself.

The code you need to implement #2 is simple but it needs to go into EVERY AfterUpdate event for the form's controls. The code compares the current value of the control to the previous value. If they are different, it sets the form level variable to true. The form's AfterUpdate event then checks the variable and if it is true, that means that at least one control was actually modified and you need to create the history record. The form's Current event and AfterUpdate event (after first checking the value) set the variable to False to clear whatever the previous setting was.
 

Users who are viewing this thread

Top Bottom