WriteChange Function HELP Please

[LisaEllen]

HELLO,

I have a function that seems to be working well on a sinlge form if someone makes a change except for two things. First I need the code (below) to reflect the person's Windows username and not the DB login which comes up in all cases as ADMIN as we don't have passwords assigned to our database. How could the code below be changed reflect the persons Windows login versus the DB sign in? This is the code I am using... and I have seen Environ=("username") but I could not get it to work - I am very new to code.

The second question - is I could not get it to record with a sub-form situation - is there a way to do that? I can post my Database if that would be helpful.

Thank you for any help. I truly appreciate it.

LisaEllen

Function WriteChanges()

Dim f As Form
Dim c As Control
Dim frm As String
Dim user As String
Dim sql As String
Dim changes As String
Dim db As DAO.Database

Set f = Screen.ActiveForm
Set db = CurrentDb

frm = Screen.ActiveForm.Name
user = Application.CurrentUser
changes = ""

sql = "INSERT INTO AuditTrail " & _
"([FormName], [Username], [ChangesMade]) " & _
"VALUES ('" & frm & "', '" & user & "', "

For Each c In f.Controls

Select Case c.ControlType


Case acTextBox, acComboBox, acListBox, acOptionGroup
If IsNull(c.OldValue) And Not IsNull(c.Value) Then
changes = changes & _
c.Name & "--" & "BLANK" & "--" & c.Value & _
vbCrLf
ElseIf IsNull(c.Value) And Not IsNull(c.OldValue) Then
changes = changes & _
c.Name & "--" & c.OldValue & "--" & "BLANK" & _
vbCrLf
ElseIf c.Value <> c.OldValue Then
changes = changes & _
c.Name & "--" & c.OldValue & "--" & c.Value & _
vbCrLf
End If
End Select

Next c

sql = sql & "'" & changes & "');"

db.Execute sql, dbFailOnError

Set f = Nothing
Set db = Nothing

End Function

[pbaldy]

I know you cross posted this, so I'll be brief. Changing this line:

user = Application.CurrentUser

to

user = Environ("username")

should get the logged in user. For that to work with a subform, you'll likely need to add a case to your Select/Case that looked for a subform control, and add a control loop within that.

[LisaEllen]

Thank you Paul, that worked and I want you to know that I was close and tried that but did not have it written correctly so I am getting better. Also I meant no disrespect by posting at the other site, I wasn't sure how all that works - is that OK to do? You have been so helpful to me and I want you to know that I truly appreciate all your guidance and have learned a great deal and I always give you credit at work ...

I really don't have a clue about the sub-form code end of it - would you help me I post the database? I totally understand if you don't want to.

Again, I thank you from the bottom of my heart.

LisaEllen

[LisaEllen]

Hello,

I have not heard back about the sub-form but had another question about the code - is there a way to add the Customer's ID to the code so I know which customer had the change - I tried to do it myself but it would not run so I am missing something and not sure what? Any help with just that please?

Thank you again.

LisaEllen

[June7]

It would be courteous to provide links to cross-posted threads.

What field would you want to save the customer ID into? Where would the value come from?

Don't think the frm.ScreenActiveForm line will work with subform because the main form is the 'active' form even if the cursor is in a subform control. If code is not behind subform then subform reference must include the subform container control name, like:

Forms!mainformname.subformcontainername.Form.contr olname

[ssanfu]

I downloaded the two dBs from https://www.accessforums.net/access/...tml#post187805
I've been looking at the dB from Post #10. I was going to modify your code to add the [Customer ID] (and the subform audit trail) per yor request, but I can't find a form (any form) that calls the function WriteChanges().


----------------------------------
Also, I saw several things that cause me concern:
Why does the AuditTrail table have 3 memo fields?
You have several tables where the PK field is text. It would be better if the PK field is an autonumber.
In object names you have special characters and spaces. Should only use letters, numbers and the underscore.

The major thing I saw is that the tables are not normalized.
In the table "Client Name", what do the fields "Charge Rate per Day", "Charge Rate per Audit", "Hot List", "Blazing Hot List", .... have to do with the client name???
Same thing with the tables "Audit Detail" and "Audits Booked Table". There appears to be fields that should be in other tables.

It looks kind of like an import from Excel spreadsheets.

[LisaEllen]

Good morning Steve,

Sorry for the confusion. I am attaching the DB with WriteChange Function include in the Module and only applied to two forms - Input/Edit Client Details - 2 which the changes should feed into the AuditTrail table and then on the Input / Edit Audit Review Sub Form which is a subform to another form-Input /Audit Detail Main Form. I put the WriteChange Function in the BeforeUpdate event of each form. What I need is to have it also capture the Client ID number so I know which customer the change took place on and to make work with the which the subform which is attached to the Input /Audit Detail Main Form - on the switchboard the forms are the first and second choices - when I go into the subform and make a change - it sees that I opened it but does not record the change.

I looked at the AuditTrail and I did not see any memo fields - I tried to set the table up the way the directions told me to? Maybe it is different in the other Database?

Also, I inherited this Database - it wasn't one that I designed...I apologize for not knowing more, I am new to code and trying to learn.

In the Client Name table - those fields were in there and the owner of the Database did not want me to remove them - this is a old database that I converted to 2010. Then he wanted me to add the Hot List fields because we have certain clients that are being watched but they haven't started this yet...

The Audits Detail is where keep track of all the exams that come in and the Audits booked is all the exams we schedule ...

I hope this is a little clearer and again, thank you for any help. I have been reading and researching and trying to come up with a way to do this and I get it to work somewhat but then get stuck and this is the DB I use at work - I just put dummy data in it.

I appreciate your help.

LisaEllen

[LisaEllen]

Hello June7,

Thank you for the help and I don't mean to be confusing...I removed that other thread that I put on the other website and apologize for not knowing.

LisaEllen

[ssanfu]

Working on it...... might take a couple of days......

[LisaEllen]

Thank you Steve so much and I hope I can help you someday!

LisaEllen

[SteveH2508]

I have recently played with this code and hit the subform problem as well. I fixed it by passing the calling form as an extra argument into the function and replacing the Screen.Activeform stuff.

[LisaEllen]

Hello Steve,

I am just checking in - any luck on this function?

Thank you.

LisaEllen

[June7]

Have you tried the suggestions from post 5 and 11?

[LisaEllen]

I did but did not have it in there correctly...I am new to code and struggle so I do apologize and I though Steve was looking at it - maybe I misunderstood his response and certainly don't mean to put any extra work on anyone. I have been trying to find another way to do it and found an audit trail code that works - it includes the primary key ID number of each table, old value and new value, username, etc. but again I run into the subform problem and then I thought well, get rid of the subforms - pull up the main client form, build some buttons to navigate to the other forms like scheduling and exam data and link them by customer ID and it works great - it will capture the edits but you can't see the client's name but the client ID is visible and it only filters on that client's existing records ... BUT if you then decide to ADD a new Record - it does not pull in the customer ID that you are filtered on (just a blank record) because it is not linked to anything so you would need to add that client ID and I am not sure I can trust the users to do that so I am stumped again.

I just don't know how to tell the code to look at the subform ... and I appreciate your suggestions, I am just unsure where to put it the code.

Thank you for any help.

LisaEllen

[ssanfu]

Sorry... I was making a little progress, then Life jumped up and kicked me in the head (only 3 feet lower).

The problem I have been working on is that a change in a subform does not cause an event to fire in the main form. So the code has to be able to handle both the main form and subform changes. Or there has to be two subroutines - one for the main form and one for the subform.

I'll get back to looking at it tonight.