Assigning user name

[szag via AccessMonster.com]

I am struggling with assigning user name to a table. I can get it to populate
in a form but I want to push it right to a table. Here is the code I was
trying to use but I get the error:

User defined type not defined related to the “New ADODB.Recordsetâ€

The code...

Private Sub Form_Load()

Set dbs = CurrentDb
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

sqlString = "select * from tblUser"
rs.Open sqlString, cn, adOpenDynamic, adLockOptimistic
rs.AddNew

rs![UserName] = Environ("UserName")
rs.Update
rs.Close


Set cn = Nothing
Set rs = Nothing
Set dbs = Nothing


End Sub

 

[PieterLinden via AccessMonster.com]

use
rs.Fields("Username") = fOSUserName()

See:
http://www.mvps.org/access/api/api0008.htm

 

[szag via AccessMonster.com]

Thanks!

Pretty new to this. Is it as simple as pasting the function (from the link)
into a module?

And then changing the one line you mention below.

 

[szag via AccessMonster.com]

Actually no matter what I cahnge I keep getting the error:

User defined type not defined related to the “New ADODB.Recordsetâ€

 

[John Spencer]

I work with DAO so the fact that you are working with ADODB may make this
comment irrelevant. Why not just use an insert query?

Dim strUser as String
strUser = Environ("UserName")
sqlString = "INSERT INTO TblUser (UserName) Values (""" & _
strUser & """)"

Then run a query with that string.

Of course, you could use the fosUser function found at
http://www.mvps.org/access/api/api0008.htm
(Copy the code from the above URL and paste it into a VBA module - not a form
or report class module.

strUser = fOSUser()

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[szag via AccessMonster.com]

John - thanks.

Sorry not much on the programming side. 2 remaining questions::

1. where does this code code:

Dim strUser as String
strUser = Environ("UserName")
sqlString = "INSERT INTO TblUser (UserName) Values (""" & _
strUser & """)"

2. What do you mean (or how) by running a query with that string?

sorry for the stupid questions...

I work with DAO so the fact that you are working with ADODB may make this
comment irrelevant. Why not just use an insert query?

Dim strUser as String
strUser = Environ("UserName")
sqlString = "INSERT INTO TblUser (UserName) Values (""" & _
strUser & """)"

Then run a query with that string.

Of course, you could use the fosUser function found at
http://www.mvps.org/access/api/api0008.htm
(Copy the code from the above URL and paste it into a VBA module - not a form
or report class module.

strUser = fOSUser()

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am struggling with assigning user name to a table. I can get it to populate
in a form but I want to push it right to a table. Here is the code I was

[quoted text clipped - 23 lines]

End Sub

 

[John Spencer]

Sorry, I assumed that since you were posting VBA you had a good understanding
of it.

If you are using ADO then I can't help you further. With DAO you could use

dbs.Execute sqlString

So the entire event would be

Private Sub Form_Load()
Dim strUser as String
Dim sqlString as String

Set dbs = CurrentDb

strUser = Environ("UserName")
'Alternative would be to use fOSUser() as noted previously

sqlString = "INSERT INTO TblUser (UserName) Values (""" & _
strUser & """)"

dbs.Execute sqlString, dbFailOnError

Set dbs = Nothing

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[szag via AccessMonster.com]

It Worked! Thanks John.

Sorry, I assumed that since you were posting VBA you had a good understanding
of it.

If you are using ADO then I can't help you further. With DAO you could use

dbs.Execute sqlString

So the entire event would be

Private Sub Form_Load()
Dim strUser as String
Dim sqlString as String

Set dbs = CurrentDb

strUser = Environ("UserName")
'Alternative would be to use fOSUser() as noted previously

sqlString = "INSERT INTO TblUser (UserName) Values (""" & _
strUser & """)"

dbs.Execute sqlString, dbFailOnError

Set dbs = Nothing

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John - thanks.

[quoted text clipped - 10 lines]

sorry for the stupid questions...

 

[szag via AccessMonster.com]

oops one last question. what do I have to add to......

sqlString = "INSERT INTO TblUser (UserName) Values (""" & _
strUser & """)"

......if I want to add the text "Closed" to the txtStatus field in the
TblUser table to sqlString

Sorry, I assumed that since you were posting VBA you had a good understanding
of it.

If you are using ADO then I can't help you further. With DAO you could use

dbs.Execute sqlString

So the entire event would be

Private Sub Form_Load()
Dim strUser as String
Dim sqlString as String

Set dbs = CurrentDb

strUser = Environ("UserName")
'Alternative would be to use fOSUser() as noted previously

sqlString = "INSERT INTO TblUser (UserName) Values (""" & _
strUser & """)"

dbs.Execute sqlString, dbFailOnError

Set dbs = Nothing

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John - thanks.

[quoted text clipped - 10 lines]

sorry for the stupid questions...

 

[John Spencer]

sqlString = "INSERT INTO TblUser (UserName, [StatusField]) Values (""" & _
strUser & """, ""Closed"" )"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

oops one last question. what do I have to add to......

sqlString = "INSERT INTO TblUser (UserName) Values (""" & _
strUser & """)"

.....if I want to add the text "Closed" to the txtStatus field in the
TblUser table to sqlString

Sorry, I assumed that since you were posting VBA you had a good understanding
of it.

If you are using ADO then I can't help you further. With DAO you could use

dbs.Execute sqlString

So the entire event would be

Private Sub Form_Load()
Dim strUser as String
Dim sqlString as String

Set dbs = CurrentDb

strUser = Environ("UserName")
'Alternative would be to use fOSUser() as noted previously

sqlString = "INSERT INTO TblUser (UserName) Values (""" & _
strUser & """)"

dbs.Execute sqlString, dbFailOnError

Set dbs = Nothing

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John - thanks.

[quoted text clipped - 10 lines]

sorry for the stupid questions...