Insert Data From One Field To Another On Save

[Tezcatlipoca]

Hi all,

Just a quickie. I have a sub-form (frmLogs) that sits within a main form (frmView). The main form takes care of the client's details (say, Mr Bloggs), the sub-form (which has its client number field linked to the master form's client number field, so only shows data relevant to that client) records the details of that client's orders (so Mr Bloggs' orders for a Giraffe and a packet of Crisps are listed as separate sub-form records).

All this works perfectly.

Also on the main form are two locked textfields. One has the username of the currently logged in user; the other the current date and time.
These fields are replicated on the subform, but are currently blank and editable.

I now want to stamp a new record on the subform with the login name and time of the database user. My question is, when clicking to add a new record button on the subform, how do I get the LoginName field and Time field on the subform to automatically fill out with the data from the LoginName field and Time field on the main form?

 

[DCrake]

If you go to the table that holds the information and in design mode set the default value options accordingly

FldDateTimeStamp = Now()
FldCurrentUser = FindUserName()

Where FindUserName = a user defined function that gets either the Access user name or the application user name, whichever you want to store. Myself I record both as well as the computer name.

Therefore I can see which user was logged onto what computer and which user opended the application. The only thing you can't trap is if the person who logs in is not the person performing the action.

David

 

[Tezcatlipoca]

Thanks for the prompt response, DCrake, but unfortunately your solution on the username isn't going to work for me.

I've already managed to implement the timestamp by attaching

Private Sub btnAdd_Click()
On Error GoTo Err_btnAdd_Click
    Me.Form.AllowAdditions = True 'I normally have AllowAdditions switched off to prevent users adding records with the mouse wheel and/or by not using my 'add call' button
    DoCmd.GoToRecord , , acNewRec
    Me.CallLog = Now() 'CallLog is the enabled yet locked field on the subform that logs the time a call is received
    Me.Notes.SetFocus 'Notes is where the call details go.  This just makes life easier for my user(s)
Exit_btnAdd_Click:
    Exit Sub
Err_btnAdd_Click:
    Resume Exit_btnAdd_Click
End Sub

VB code to my button that allows users to add a new record. This works beautifully, but the username is still a problem. Let me explain further.

When my database first starts, a modal popup login window appears. The user selects a username from a list of names stored in a linked SQL table. They then enter the password for that user and click a Login button.

Assuming the details match, the user is logged in to the main form (frmView), which displays the main client details (name, number, etc. from the table tblMembers). frmView contains the subform, frmLogs. frmLogs draws from table tblLogs, and has its client number field linked to frmView's client number feld, so that a user can select a client, then scroll through all the calls attached to that client in the subform.

One other function is that when the login screen is closed with the Login button, whatever username has been put into the username box is saved as a string. When frmView opens, it calls this string and uses it to populate a 'Username:' box in the corner, showing which user is currently logged in. This works perfectly.

My problem is that when my user(s) add a call on the subform, I want both the timestamp (already working) and the current user (not working) to get logged with the call notes. Basically, I don't know how to get my VB script to grab the username data in frmView.LoginName and pass it into a box on the subform!

 

[Tezcatlipoca]

Ah, just spotted the flaw in my code. It now works, but thought I'd post here just in case anyone else was having the same issue.

I've simply added:

Me.Username = Forms!frmView!LoginName

to my 'Add Call' buton code as outlined above. The button now sets up a new record, allows additions, then grabs the username logged in the main form and populates the relevant field in the subform with it.

 

[DCrake]

Glad you sorted out. It's better to spot your own mistakes first than rely on others to trip you up.

David

 

[RCheesley]

Thanks for posting your solution, enabled me to fix something too!

Ruth