GetUserName help please

[sizemoreg]

Good Day
I am in need of some help. My dilemma is with getting User name data when my team enters and/ or edits records. What I have is an invoice processing database. This database records Invoices received, audited, and paid. With this database I have 3 separate steps that require a different user to process an Invoice. This requirement comes from a SOX compliance standard, the same user can't enter the Invoice into the system, then audit the invoice for payment approval, and finally pay the invoice. 3 different people have to be involved to insure that data is valid before being paid out.

I need to record each person's contribution to this process. I've tried code I found (fOSUsername() ), which accurately records the initial entry, but then as it progresses through the other steps, a new record is created ( making duplicate and triplicate Invoice entries)<- not good. I need to record all this within the one record. I am sure I missed, or haven't found the correct coding to make this happen, and this is where I need help. The ability to only record multiple users in one record.

Currently my table is set up in this fashion

Header row: [ID] [EntryAgent] [AuditAgent] [PaymentAgent] [InvoiceRecieptDate] [InvoiceEntryDate] [ApprovedAmount] [PaymentAmount]


001 John Doe Jane Doe Jr. Doe 01/01/2015 01/02/2015 $5.00 $5.00


I would appreciate any direction, or help I can get

Thanks

[ranman256]

vUser = Environ("Username")

you dont put multi users in 1 record. you make several records.

[sizemoreg]

Yeah, that's where I hit the wall. I can't have multiple records of one Invoice number. I currently have my table set up with 3 fields, one field for each person, they just search for that record and edit their part of the process. I was hoping to get something that would automatically add their login name (username) without creating a whole new record.

Thanks for the quick reply

[nick404]

Could have a login form to open on db load and then your form will pull the username info from that form. Can be unbound

[June7]

Subsequent users must find the existing record for editing. It seems you are trying to implement a search process but without reviewing attempted code and/or better understanding of data structure and form design, we really can't advise. If you want to provide db for analysis, follow instructions at bottom of my post.

[Missinglinq]

Assuming, from your narrative, that

• The EntryAgent originates the Record
• The AuditAgent approves the amount to be paid
• The PaymentAgent actually makes the payment

you can have the appropriate 'agent' Field populated when the Control each agent is responsible for is completed...something like this:

Private Sub InvoiceEntryDate_AfterUpdate()
 Me.EntryAgent = Environ("Username")
End Sub

Private Sub ApprovedAmount_AfterUpdate()
 Me.AuditAgent = Environ("Username")
End Sub

Private Sub PaymentAmount_AfterUpdate()
 Me.PaymentAgent = Environ("Username")
End Sub

This presupposes that your users aren't intentionally trying to jigger your system! Making sure that all three agent names are different could be done, but would require additional code for a validation check to be made before each agent name is entered.

Linq ;0)>

[knarfreppep]

This is an approach one might consider.

When the operator opens the app (logs on) use the AfterUpdate event of a combo box behind which is their ID, Name, and Password to set a global variable e.g. gintCurrentOperatorID.

For each table which is sensitive/ciritcal, have an 'audit trail' table, e.g. tblInvoices > tblInvoices_AuditTrail.

In the AfterUpdate event of the form(s) sitting on e.g. tblInvoices populate fields dtmCreated, intCreatedBy, dtmLastChanged, and intLastChangedBy as appropriate using Now() and gintCurrentOperatorID then run a query to append the full new/changed record to tblInvoices_AuditTrail .

You'll allways be able to determine exactly who did what and when and roll back if necessary.

And records in tblInvoices_AuditTrail could tell the 'story' of an invoice's progress using a 'status' flag: new, approved, part-paid, paid, reversed, etc.

I like to also put an 'Audit' button on the form(s) to show dtmCreated, intCreatedBy, dtmLastChanged, and intLastChangedBy ... this is 1) handy and 2) a useful internal control because it lets operators know their activity is being tracked (i.e. if your objective is to prevent fraud rather than just catch it after the event).

As to the 'separation of duties' problem, it's simple to prevent operators from seeing records and having rights over adding and changing records according to their role(s).