update table from unbound text box

[Ratman11]

Stupid question number 37 this week!!

I have a form with an unbound text box - this text box is updated on form open with the below code:

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False

Dim struserid As String

struserid = Environ("UserName")

Me.UserName = struserid

End Sub

I want the username to then be put into a table, but cannot link the text box to the table.

How do i get the result to go in to the table?

table name is tbIssueLog with a field name of UserName

 

[HarleyQuinn]

Use an APPEND query and have the value to be appended as the value of the textbox.

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[JerryKlmns]

Place a button on that form and on the click event

Dim rst as ADODB.Recordset
Set rst=New ADODB.Recordset
    With rst
        .ActiveConnection = CurrentProject.Connection
        .CursorLocation = adUseServer
        .CursorType = adOpenForwardOnly
        .LockType = adLockOptimistic
        .Source = "tbIssueLog"
        .Open
        .Fields("UserName ")= struserid
        .Update
        .Close
    End with
Set rst = Nothing

On VBE menu Tools --> References chek the box for Microsoft ActiveX Data Objects 2.x Library
If you want to write more info on table, just add more

.Fields("TheDate")= Format(Date, "dd/mm/yyyy")
.Fields("TheTime")= Format(Time, "hh:mm:ss")

where TheDate and TheTime are fields on your tbIssueLog

Just to put some more, I also record these network variables

    Dim objWscript As Object
    Set objWscript = CreateObject("Wscript.Network")
    TheComputer = objWscript.ComputerName
    UserDomain = objWscript.UserDomain
    UserName = objWscript.UserName
    Set objWscript = Nothing

Environ("UserName") is my UserName variable

 

[Ratman11]

Hi
Thanks for that, i pasted in your code as above but i keep getting but i'm getting a 'variable not defined' on the currentproject section...

 

[JerryKlmns]

Ratman11,

have you
On VBE menu Tools --> References chek the box for Microsoft ActiveX Data Objects 2.x Library

 

[Ratman11]

Yes
I've got 2.6 selected

 

[JerryKlmns]

If it stops here

.Fields("UserName ")= struserid

there 's a space at the end so replace it with

.Fields("UserName")= struserid

or even

.Fields("UserName ")= Environ("UserName")

and declare 1 variable less.......

If it stops somewhere else tell me where exactly.

 

[JerryKlmns]

extra space again

.Fields("UserName")= Environ("UserName")

 

[Ratman11]

Ok, here is the code i have:

Private Sub Command26_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

On Error GoTo Err_Command26_Click

If Nz(StoreInvoiceNumber, 0) = 0 Then
MsgBox "Please enter invoice number"
Else
With rst
.ActiveConnection = [highlight]CurrentProject[/highlight].Connection
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Source = "tbIssueLog"
.Open
.Fields("UserName") = struserid
.Update
.Close
End With
Set rst = Nothing
DoCmd.Quit
End If

Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click

End Sub


it is breaking at the highlighted section

thanks

 

[PHV]

Which version of access ? (hopefully not 97 ...)
Anyway you may consider a simple SQL instruction launched by either DoCmd.RunSQL or CurrentDB.Execute

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ratman11]

Yes, i am running access 97...oh lucky us...

tried the docmd.runsql statement and came across a couple of issues (code below)....

1) When i clicked on the button, it asked me to enter criteria for the user name, rather than taking it from struserid

2) I cannot get it to identify with a set record in the table - if it does input anything (by enclosing struserid in " " then it puts "struserid" in every record!!!

It's late, i'm tired...this is probably very easy to fix, but i'm really struggling!!

Private Sub Command26_Click()
Dim struserid As String

struserid = Environ("UserName")

On Error GoTo Err_Command26_Click

If Nz(StoreInvoiceNumber, 0) = 0 Then
MsgBox "Please enter invoice number"
Else
DoCmd.RunSQL "UPDATE [tbIssueLog] SET UserName = struserid"
DoCmd.Quit
End If

 

[PHV]

DoCmd.RunSQL "UPDATE [tbIssueLog] SET UserName = '" & struserid & "' WHERE SomeField=" & SomeValue


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ratman11]

Hi PH

Thanks for that - finally managed to get it to work.
*phew*