Full Version: sql statement with Where...
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
doran_doran
UPDATE tbl_admin SET tbl_admin.ComputerName =getcomputername WHERE (((tbl_admin.UserID)=username()));

how do I convert above query for form sql query

dim strSQL as string
strSQL = modified version of above query



GetComputerName is a function and it works
UserName is a function and it works
LittleViews
Well, start with getting rid of all the (). Next, get rid of all mention of tbl_admin. that includes the dot.

"update tbl_admin set computerName = " & getcomputername & " where userID = " & userName
doran_doran
Thanks for the help.... does this makes sense..

I am trying to obtain computer name only if me.txtcomputer name is null or if me.txtcomputername does not match with the current pc name GetComputerName

If IsNull(Me.txtComputerName) Or Not GetComputerName Then
Dim strSQL As String
strSQL = "UPDATE tbl_admin SET tbl_admin.ComputerName = '" & GetComputerName & "' WHERE tbl_admin.UserID= " & GetCurrentUserName
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
End If
dannyseager
Turning the warnings off is a bad idea... execute the SQL with

CurrentDB.execute strSQL

Try
CODE
If Len(Me.txtComputerName & "") = 0 or me.txtcomputername<>GetComputerName then
    CurrentDB.execute "UPDATE tbl_admin SET tbl_admin.ComputerName = '" & GetComputerName & "' WHERE tbl_admin.UserID= " & GetCurrentUserName
else
end if


Not sure about your SQL though....

What does GetCurrentUserName return? A number (i.e. the ID)
doran_doran
It's not populating data like it should
I dont receive any error.

getcurrentusername is alpha-numeric value like y23er12 (novell log in id)
LittleViews
Danny's example is good.

Now then, if there is something wrong with your SQL, it'll stop your application dead in its tracks.

BEFORE firing your SQL, do this:

1 - place a blank field on your form. Name it "X"

2 - in your VB just before you are going to fire your SQL do this:

Me.x = "all of my SQL string"
Exit Sub

3 - run your form. Copy the resulting information from field X and test it in the Query manager. The Query manager will tell you where you screwed up.

You might have an error someplace else, but often it is the SQL that'll stop things dead.
doran_doran
If Len(Me.txtComputerName & "") = 0 Or Me.txtComputerName <> GetComputerName Then
CurrentDb.Execute "UPDATE tbl_admin SET tbl_admin.ComputerName = '" & Me.txtComputerName & "' WHERE tbl_admin.UserID= '" & Me.txtUserName & "'"
Me.ListComputerName.Requery
End If


I took out the if thing and the sql works. when I put the if then it does not do it's job. I think there is a issue with if command.
dannyseager
ok.... let's do some testing....

try running this

Msgbox "Length of computername : " & Len(Me.txtComputerName & vbnullstring) & _
"Computer Name : " & nz(Me.txtComputerName,"")
"Computer Name Function : " & nz(GetComputerName,"")

What results do you get?
doran_doran
I am very sorryyyyy.....

it should be Listbox "listcomputername" not txtcomputername.

If IsNull(Me.ListComputerName.Column(0)) Or Me.ListComputerName.Column(0) <> GetComputerName() Then
CurrentDb.Execute "UPDATE tbl_admin SET tbl_admin.ComputerName = '" & Me.txtComputerName & "' WHERE tbl_admin.UserID= '" & Me.txtUserName & "'"
Me.ListComputerName.Requery
End If


This is good code and it's all working like a charm.

Thanks everyone for pitching in...
dannyseager
Glad you got it working.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.