Audit trail not working on form with subform

[RuralGuy]

Have you single stepped the code or set a breakpoint to see if that line appears to be executing?

[jpkeller55]

I have never single stepped a code before...Is that difficult to do?

I have tested that it all functions fine if a new record is inserted and if the module is called on a single form with no subform. It is just the edits on the Form/subform that are not getting populated correctly to the Audit trail field (Updates). How can I single step this code? Thanks for your assistance. I am very close to solving this with yours and Adam's help.

[RuralGuy]

While looking at the code, left click outside of the edit margin and you will place a breakpoint on that line. It needs to be a line that actually executes and not just a documentation line. Then run the form and when that line is reached the editor will pop up on that line. Pressing the F8 key single steps and hovering over a variable will pop up the value of the variable in a baloon. F5 will let the code take off again if you want. Make no changes to the code while in this debug mode as it has been know to cause corruption.

[ajetrumpet]

keller,

make sure to that if you're hovering over var's in the code, to do so AFTER the execution passes the line. code execution does not take place until after the code is read (e.g. - when your yellow line highlights the next line)

Hey Allen, thanks for that corruption tip. Didn't know that.

[jpkeller55]

OK, I figured out how to step through the code based on your instructions. I will take a deeper look at this in a bit. Thanks!

[jpkeller55]

I have stepped through the code. When I enter a new record, the code stops at the break point per the screen shot. When I try to edit a record, it stops at the breakpoint but there is too much data to see in the hover over. I can't see the full value. I am not sure what I am looking for.

[ajetrumpet]

keller,

type "?chr(10)" and "?chr(13)" in the immediate window. it doesn't return anything. what did you mean those to be? that is one problem for sure. Access is giving you boxes I think because they don't have any return values.

Not sure, just throwing that out there. Allen will probably be able to help you more than I. He's got much more experience.

[jpkeller55]

the chr(10) and chr(13) are just adding a new line in the field I believe. From the attached screen shot, you can see how the code is failing to put the date and time for the two edits that I made to a question. It did capture the new record date and time.

[ajetrumpet]

keller,

to be honest with you, I'm not sure by looking at the shots. I'm sure you can get it fix by uploading the file or a portion of it here. I'm out of ideas without looking at it directly. I'm almost certain it's something simple, but it's a lot easier to look at it on my own screen than going through many more posts back and forth to figure it out.

If you can't upload, I'm sure someone else will be able to spot it...

[jpkeller55]

Wont be able to publicly post the file at this time, but I appreciate any insight.

[RuralGuy]

keller,

type "?chr(10)" and "?chr(13)" in the immediate window. it doesn't return anything. what did you mean those to be? that is one problem for sure. Access is giving you boxes I think because they don't have any return values.

Not sure, just throwing that out there. Allen will probably be able to help you more than I. He's got much more experience.

Adam,
Chr(13) = CarriageReturn and Chr(10) = LineFeed. Both are control codes in ASCII and so there is nothing to display. It is just like vbCrLf.

[ajetrumpet]

thank you Allen. I did misread those when they outputted. I never test chr() because never really use them.

[RuralGuy]

What kind of field is the Updates field? Memo, Text? How about using some Debug.Print statements to write this field to the immediate window as a diagnostic tool?

[jpkeller55]

I am pretty sure I have figured out what is going on here but not sure how to fix. I think the reason the characters in this command are not populating the tblQuestions.Updates when I use a form with a subform but does print when only using a form with no subform.

"Changes made on " & Now() & " by " & Environ("USERNAME") & ";"

Because the audit trail could potentially go to two different UPDATES fields (tblQuestions.Updates and tblQuestResponseSets.Updates) when data exists in the subform, Access is reading the controls associated with the subform first and then the controls on the main form. The above code is populating correctly to the tblQuestResponseSets.Updates where the responses are stored. But once Access has finished reading the subform controls it then moves on to the main form controls. The audit trail for this is stored in tblQuestions.Updates. Since the code already wrote the "Changes made on...." to the tblQuestResponseSets nothing gets printed to the tblQuestions.Updates. Does this sound reasonable and if so can the code be tweaked to fix?

Updates field is memo by the way.

[ajetrumpet]

j,

even if the there are two UPDATES fields, that shouldn't be a problem because you are directly referencing the correct field with the MYFORM variable. Could it be maybe that it IS actually writing twice like you want, but it is writing the same data to the same field twice because the MYFORM variable is the same both times?

I took a look more closely and it is a bit confusing of how it is working. I did think of that though, so I thought I would mention...