Multiple DLookup Criteria

[burrina]

Code: Select all

 If DLookup("[PCUserName]", "tblUser", "[PCUserName]='" <> "[ComputerLoginName] & "'")) Then

My syntax is wrong?

[HansV]

What exactly do you want to accomplish?

[burrina]

I think I have it working Hans. But if there is no user in tblUser like ComputerLoginName which equals Environ("username") Then

' you can't come in.

[HansV]

That might be

Code: Select all

If IsNull(DLookup("[PCUserName]", "tblUser", "[PCUserName]='" & [ComputerLoginName] & "'")) Then

or

Code: Select all

If DCount("[PCUserName]", "tblUser", "[PCUserName]='" & "[ComputerLoginName] & "'") = 0 Then

[burrina]

Thanks Hans,

[burrina]

Code: Select all

Dim ComputerName As String
Dim GetComputerName As String
Dim sSQL   As String

ComputerName = GetComputerName

  If IsNull(DLookup("[ComputerName]", "tblUser")) Then
DoCmd.SetWarnings False
 sSQL = "INSERT INTO tblUser (ComputerName) VALUES & ('" & ComputerName & "'))"
DoCmd.SetWarnings True
       End If

'Why does this not work?

[HansV]

The result of the three lines

Code: Select all

Dim ComputerName As String
Dim GetComputerName As String
ComputerName = GetComputerName

is that ComputerName equals the empty string "".

Do you have a function GetComputerName elsewhere in your code? If so, delete the 2nd of the above lines: Dim GetComputerName As String

[burrina]

Yes I do.
Tried this, no luck

Code: Select all

Dim User, Criteria As String
Dim ComputerLoginName As String
Dim ComputerName As String
Dim sSQL   As String

ComputerLoginName = Environ("UserName")
ComputerName = GetComputerName

  If IsNull(DLookup("[ComputerName]", "tblUser")) Then
DoCmd.SetWarnings False
 sSQL = "INSERT INTO tblUser (ComputerName) VALUES & ('" & ComputerName & "'))"
DoCmd.SetWarnings True
       End If

[HansV]

You assign a value to ComputerLoginName but you don't do anything with it. Why not?

[HansV]

Also, the lines

Code: Select all

 If IsNull(DLookup("[ComputerName]", "tblUser")) Then
DoCmd.SetWarnings False
 sSQL = "INSERT INTO tblUser (ComputerName) VALUES & ('" & ComputerName & "'))"
DoCmd.SetWarnings True
       End If

do exactly nothing: you don't execute the SQL string.

[burrina]

CurrentDb.Execute sSQL, dbFailOnError ' does not work?

[HansV]

Above the line

sSQL = "INSERT ...

insert

MsgBox sSQL

What do you see?

By the way, does tblUser contain more than one record? If so, you can't predict what DLookup("[ComputerName]", "tblUser") will return, since you don't use the Criteria argument.

[burrina]

Here is what I get. Errors ?

[HansV]

My mistake. The MsgBox line should go BELOW the line

sSQL = "INSERT ...

[burrina]

Here is plain vanilla demo, don't understand what I am missing?

[HansV]

You don't assign a value to ComputerName so it is "". In a previous version you had

ComputerName = GetComputerName

but that is not in your demo. You do assign a value to Criteria but you don't use it.

What are you trying to accomplish?

[burrina]

Made it simple. But I gave up and just used an append query. Sorry for the confusion. Code later on being used Hans, not important right now.
Thanks,