Append data to a table from an onclick property in a form

[edzigns]

I have a button on a form that when you click to add and save new record to tblMasterTicketTracking, I would also like it to lookup current user (UserID) and append that to tblMasterTicketTracking, field 'CreatedBy'.... and append current time/ date to tblMasterTicketTracking, field 'CreatedOn'.... can I do this in VBA (as I already am running VBA for the onclick, or do I need to use an append query, or create, then run append query via VBA????

I have a Module that tracks logons and logoffs by User....

Any help would be great...., I can provide more info if necessary.

I am just in la la land today....

 

[BLeslie88]

Here...

Dim vCnt As String
Set db = CurrentDb()
Set rsTable = db.OpenRecordset("YourTable", dbOpenDynaset)
if rsTable.eof and rsTable.bof then
vCnt = 0
else
rsTable.movelast
vCnt = rsTable(0) + 1
End If
rsTable.AddNew
rsTable(0) = vCnt
rsTable(1) = txtWhatever
rsTable(2) = txtSoFourth
rsTable(3) = varUserID ' Assume you have it stored somewhere
rsTable.Update
rsTable.Close

Something like this should suffice, good luck....

 

[edzigns]

Most worked....but....

Thank you for the coding... I am new and I still am working on trying to grasp the VBA... my problem is with the Current logged in user...

The Module that I am using to store Username is as follows:

Public Type UserInfo

ViewID As Integer
AccessID As Integer
Active As Boolean
Password As String
UserID As String
SecurityID As String

End Type

Public User As UserInfo

and the code that you gave me for the save_and_notify onclick is:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rs As Recordset
Dim User As UserInfo
Dim SecurityID As Long 'if I use as string it gives me an error

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblOpenedBy", dbOpenDynaset)

rst.MoveLast

rst.AddNew
rst("MasterTicketNo") = Forms!frmMasterTicketTracking.MasterTicketNo
rst("SecurityID") = SecurityID
rst("CreatedOn") = Now()
rst.Update
rst.close

...it creates the record, adds the correct MasterTicketNo, the time and date, but adds a 0 in the SecurityID field (which doesn't exist for any of my users)...

 

[BLeslie88]

Sorry...

I do not code with types, and haven't since C++ in school..
We will need someone elses feedback. Also would help to see when you assign your variables, such as when they log in? When you assign them to the table... Although you would only require the user ID for further referencing in reports or analysis. which is why I never bothered looking into types... I just reference all tables by ID's unless I am using arrays for calculations... Anyhow hope you get your answers. Leave some more details about your problem for answers. ok...

Regards,