Auditing changes and Forced input.

[Homegrownandy]

This is a Continuation from here: https://www.accessforums.net/program...tml#post297155

Currently I have a macro assigned to the table 'Workdays'. When this table is updated, new records are made in an auditing table. As seen in the other thread.

What I was trying to do was have the user enter information regarding this change in an input box. I don't believe this can work with my current setup because:



*Assigning it to the date text box that I want to track changes is un relatable. That text box relates to the job and the day. It cant possibly link to the auditing table to update it.

I think that instead of the macro updating at table level I need a VBA based auditing system. Which I can add an insert box to. I'm reluctant to do this as it took me long enough to get this working (despite how easy it actually is).

So I'm after suggestions really. If I can assign an input to the macro itself I would prefer that. (or some other way to get this working with the macro)

Alternatively, Ill look into the VBA for this. Does anyone have recommendations or advice?


Edit: If I can get a query that pulls the last added record from the auditing table. I can open a form that relates to that record on the after update event of the date box. Assuming the macro runs first this might be an option.

[Homegrownandy]

Update: I now have a form open on the last record added to the audit table to force the users input. But the problem is this event happens the same time as the record is added there. So, it displays the previous record added not the current.

I'm trying to get this working:

Private Sub Scheduled_Start_AfterUpdate()
 Dim PauseTime, Start
 PauseTime = 5 ' Set duration in seconds
 Start = Timer ' Set start time.
 Do While Timer < Start + PauseTime
 DoCmd.OpenForm "audit"
loop

End Sub

It doesn't matter where I have DoCmd.OpenForm "audit" it still opens on the record one before last.

I have also tried to implement a delay in the form load event. still no progress.

Anyone have an idea how I can slow this down so its not getting data before the table is updated?

[ItsMe]

Here is one function that will count whole seconds. IF you want t count 2 or more seconds, this will work. It is just not very accurate because it does not understand how to manage fractions of a second. So if you have it count 3 seconds, it might only count for a little longer than 1 or it might count just under 4 seconds.

Private Sub pleaseWait(intDelay As Integer)

      Dim dblDelayTill As Double
      dblDelayTill = DateAdd("s", intDelay, Now)
      While DateDiff("s", Now, dblDelayTill) > 0: Wend

End Sub

If you need to count fractions of a second, you can place the following in its own Standard module.

Option Compare Database

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Sub PauseCode(MillisecondsCount As Long)
Sleep MillisecondsCount
End Sub

[Homegrownandy]

Okay it seems to work. the form does wait to open..... but the issue is still there.,

Its not getting the last record because it gets the "last" record before the table is updated. When the audit is updated that record is second from last.

the problem is the table doesn't update until the form is closed. I need to implement a save in the code if I can before the form opens then everything should be okay. (googling) haha

If Me.Dirty Then Me.Dirty = False in the forms dirty event doesn't work...

Any suggestions?

[ItsMe]

I do not know of a way to pause a Saved Macro Object. Also, if a Macro appends a record to a table, I do not know how you would retrieve the PK value. I suppose a timestamp field could help to understand which record within a table is the most recent.

[Homegrownandy]

you misunderstand sorry.

the form is a data entry form...the way it adds records to the table is all fine. Its just.... it only adds the records on the close event of the form.

the close event of the form is after the new one opens....


I either need to close the form.. (preferably not) OR save the record on the update event of the text box.

does this make sense?

[ItsMe]

I use the following code to commit records to the table. IIRC, the Unload event fires prior to the Close event of a form.

if me.dirty = true then
me.dirty = false
end if

[Homegrownandy]

Hi ItsMe. I use that on the "dirty" event of the form and still it doesn't save until form is closed.

[Homegrownandy]

RunCommand acCmdSaveRecord before form open did the trick!