OldValue problem

[SCubed]

I apologize in advance for what may seem like "stupid" postings, but I am used to working with much more rebust languages and databases than VB and Access. But, I have to fix what my client gave me, so thus, I'm posting my little heart out to meet my deadline!

Here's my problem. I need to be able to know the value of the a field prior to a change being made to it. It is a bound field in a combo box. I have tried using the OldValue property on the combo box in every event that made sense and it has a null value. So, I resorted to making a global variable that stored the value on the clicked, enter, before update, etc. events and still, I could not get the correct value consistently. Is this a bug in my version or am I missing something. I am running Access 9.0.3821 SR-1. I have been banging my head against a wall about the simplest coding concepts and this just adds to my overall frustration! Any help you can give me is appreciated!

Thank you!

SCubed

 

[cghoga]

Try Me.Undo

If you need to go back more than one change I would recommend storing the record in a history table by running an SQL statement in the After Update procedure.

 

[Onyxpurr]

I would have to check my database at work (I'm at home right now), but this is what I did:

- Set a global variable called lastvalue
- In the OnEnter event I put
If IsNull(me.cboBox) Then
lastvalue = ""
Else
lastvalue = me.cboBox
End If

- Then OnChange I checked the value (the only problem with OnChange is that it fires when the user either selects a value or each time the user types a character which could be troublesome.)

If this doesn't help, let me know and I'll double-check my db when I get to work.

 

[SCubed]

Glad ot know someone is still awake!

OK, here's what I'm up against...I have it "somewhat working", but not good enough...

In my "OnChange" event, I have this code:

Private Sub ComboAssetOwner_Change()

globalOldValue = ComboAssetOwner.Value

End Sub

Then in my "SAVECHANGES" button I have this code:

Private Sub btnSaveChanges_Click()
On Error GoTo Err_btnSaveChanges_Click

Dim liReturn As Integer
Dim lsSQL As String

lsSQL = "UPDATE AssetOwner SET AssetOwner = '" & ComboAssetOwner.Value & _
"' WHERE AssetOwner = '" & globalOldValue & "'"

DoCmd.RunSQL lsSQL

liReturn = MsgBox("Save was successful.", 64, "Save Confirmation&quot

'DoCmd.Requery

Exit_btnSaveChanges_Click:
Exit Sub

Err_btnSaveChanges_Click:
MsgBox Err.Description
Resume Exit_btnSaveChanges_Click

End Sub

It all seems to work and then I get the ugly "duplicate record, index, etc." message. But it's NOT a duplicate. I cannot figure out why it is doing this!!! Please help!

Thanks!

SCubed

 

[Onyxpurr]

Do you have that field indexed for no duplicates? Do you have more than one asset owner with the same name?

If you are doing this from a form, try to refresh the form after running the sql command and see if it happens again.

 

[SCubed]

OK, I'm getting closer. A Refresh in another area appears to be the culprit. However, now I'm getting a message saying "Invalid use of Null" on a line of code where I'm setting my global variable = Null. I've tried other things to reset the variable, but get syntax errors. How do I do this?

Thanks again!

 

[Onyxpurr]

Can you set it to "" instead?