Display message only when theres existing data

[Sniper-BoOyA-]

Good afternoon,

I just added a new function to my database, that basically keeps track of all the changes made in the database using forms. It works great! But theres just one thing i would like to add, with the code below, i get the message strReason, every time i enter data using the form. Even when there is no existing data in that record. Now it would be useless to have to give a reason for adding new data.

I was wondering if it was possible to only show the strReason message if there is data in the record that is being changed.

Can i use the Screen.ActiveControl.Value command for that ?

Here is the code :

Public Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reden voor wijziging? (Max 40 tekens)")
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Screen.ActiveForm.Name
rs!Veldnaam = strCtl
rs!datum = Date
rs!Tijd = Time()
rs!OudeWaarde = Screen.ActiveControl.OldValue
rs!NieuweWaarde = Screen.ActiveControl.Value
rs!Gebruiker = fOSUserName
rs!LoggedIn = CurrentUser()
rs!Computer = FindComputerName
rs!Reason = strReason
.Update
End With
Set db = Nothing
Set rs = Nothing
End Function

Cheers!

 

[twoplustwo]

Hi,

Have a look at the AfterUpdate event in the form

 

[Sniper-BoOyA-]

Well, at the moment there is no Afterupdate Event..

Theres only a before update event, which calls the function
TrackChanges() ..

 

[Sniper-BoOyA-]

I tried this:

If IsNull(Screen.ActiveControl.Value) = False Then
strReason = InputBox("Reden voor wijziging? (Max 40 tekens)")
End If

But it still asked for a reason when i entered data into an empty record.

 

[Sniper-BoOyA-]

Currently i am trying several methods to get this to work..

My next 'method' :

If Screen.ActiveControl.OldValue = Empty then
ELSE
strReason=InputBox("txt")

update:

Still no luck.. It keeps showting the InputBox ..

 

[ghudson]

Why not test for data in one of your required [primary] fields?

 

[Sniper-BoOyA-]

Why not test for data in one of your required [primary] fields?

Hmm..

The data in the required fields are protected though, so the user can not change them.

All the protected fields are displayed in the header of the form.

And in the body ull find other fields that our employees in the lab have to put in, according to their test results.

The code itself works great, its just that it will be annoying for having to enter a reason, when you enter new data.

So i would like to check if the Screen.Active.Control, has a value, if yes, then it can show the strReason InputBox,

If the OldValue is null or empty, then i dont want to see the strReason InputBox.

Once again, it turns out that its not as easy as it looks

 

[ghudson]

Hmm..

The code itself works great, its just that it will be annoying for having to enter a reason, when you enter new data.

Then check if the curent record is a new record if you do not want to make the user enter a reason "when you enter new data" in the forms Before Update event to check if the Reason text box is null.

    If Not Me.NewRecord And IsNull(txtReason) Or Not Me.NewRecord And txtReason = "" Then

 

[Sniper-BoOyA-]

Aahh got ya...

Sounds logical Ill give it a shot.

Thanks m8.

update :

Get a syntax error at 'me' and 'NewRecord'

 

[Sniper-BoOyA-]

I think ive got it!

I used

If Not Screen.ActiveControl.OldValue = Empty Then
strReason = InputBox("Wat is de reden voor deze wijziging? (Max 40 tekens)")

And it seems to work just fine..

I would like to thank everyone who took their time to help me out, and had the patience. Thanks again!

 

[vbaInet]

I think you want to give that "solution" a second thought. Have a little read:

http://baldyweb.com/NullEmptyEtc.htm