Log table - DoCmd.RunSQL writing data as 2 rows instead of 1

[Allie12380]

Goal: Use code below to write one row of data to log table when form is opened that captures the user and form name.
Current state: Code writes the data to table but it inserts it into the tbl_Log as two rows. One with username and one with form name.
Question: How can I edit the code below to write the UserName and fSTampOpen onto the same row? Also, what is right indenting for this code to space it out?


Private Sub Form_Load()
'Loads the user text box on frmSwitch Main and logs event in tbl Log
'Loads the form name text box on frm
Me![txtUser] = NetUser()
DoCmd.SetWarnings False
Me![txtformname] = GetActiveFormName()
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into tbl_Log (UserName) Values (Forms!frmSwitch_Main!txtUser);"
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into tbl_Log (fStampOpen) Values (Forms!frmSwitch_Main!txtformname);"
DoCmd.SetWarnings False
End Sub

Here is example of how data is currently writing to the table that shows the 2 rows:

[isladogs]

Use one INSERT statement with both fields/values

[Allie12380]

I tried this as one but only get user name returned. The one insert row works in SQL database I have but cant get it to work in Access.

Private Sub Form_Load()
'Loads the user text box on frmSwitch Main and logs event in tbl Log
'Loads the form name text box on frm
Me![txtUser] = NetUser()
DoCmd.SetWarnings False
Me![txtformname] = GetActiveFormName()
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into tbl_Log (UserName,fStampOpen) Values (Forms!frmSwitch_Main!txtUser,Forms!frmSwitch_Main !txtformname);"
'DoCmd.SetWarnings False
'DoCmd.RunSQL "Insert Into tbl_Log (fStampOpen) Values (Forms!frmSwitch_Main!txtformname);"
DoCmd.SetWarnings False
End Sub

[Welshgasman]

That is because you are only supplying one value?

And please start using code tags.

[Allie12380]

How do I supply the 2nd value?

[Welshgasman]

DoCmd.RunSQL "Insert Into tbl_Log (UserName,fStampOpen) Values (Forms!frmSwitch_Main!txtUser,Forms!frmSwitch_Main!txtformname);"

When you do not know the syntax, look it up.

Actually, now I have put it into code tags, I can see you do actually have two values in there.

Have you walked your code to see what those values actually are, and not what you think they are?
Put a Debug.Print in for both those values, BEFORE you try to use them.

Tip: If that code is in Forms!frmSwitch_Main. then I would use Me.txtUser etc.
Only use full name when you have no other choice.

You had a space in the second name. Now this site sometimes adds a space itself, but if you really have that space, then it would appear you do not have Option Explicit at the top of your modules. That would highlight the fact that that control does not exist.

[June7]

Side note: need DoCmd.SetWarnings True after running SQL.

Welsh, OP has embedded control references between quotes. Works with RunSQL but requires full path reference - Me. won't work unless they do concatenation.
Option Explicit would not catch space in that string.

[Welshgasman]

Side note: need DoCmd.SetWarnings True after running SQL.

Welsh, OP has embedded control references between quotes. Works with RunSQL but requires full path reference - Me. won't work unless they do concatenation.

Ah, my mistake. That is the way I normally do it when needed, and missed that important point.
Sorry @allie12380

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

What "SQL database"? Is this still the backend?

Do you show user and formname in texboxes on every form?

[Allie12380]

Welshgasman and June7!!!!
Thank you both
Two changes that got it working:
1) Updated the DoCmd.SetWarnings True after running SQL. I had False originally.
2) In the declare variable part "Values (Forms!frmSwitch_Main!txtUser,Forms!frmSwitch_Main !txtformname);" changed to (Forms!frmSwitch_Main![txtUser],Forms!frmSwitch_Main![txtformname]). Just added brackets around the field name.

Now it all works. Would not have figured this out without both of you.

[June7]

Well, those are textbox names, not field names.
Brackets should not be needed. Works for me without.

[isladogs]

Although you now have a solution, something like the following should also work and is much neater

Private Sub Form_Load()
     'Loads the user text box on frmSwitch Main and logs event in tbl Log
      Me.txtUser = NetUser()

     
     'Loads the form name text box on frm
      Me.txtformname = GetActiveFormName()

     'Add values to table
     CurrentDb.Execute "Insert Into tbl_Log (UserName, fStampOpen) Values ('" & NetUser() & "', '" & GetActiveFormName() & "');", dbFailOnError


End Sub

The INSERT statement now uses the same functions already used to populate the 2 textboxes
Using CurrentDb.Execute means no SetWarnings statements are needed.
Using dbFailOnError means messages still appear in case of code errors

It may be possible to streamline this still further!

[Allie12380]

Thank you! Appreciate the explanations on the code elements too.

[Allie12380]

I am not sure why in this database needed the brackets. Removed them and tried it and wont work.

[Allie12380]

updated to this code and it works perfect and faster than the non-streamlined code