Another Dlookup query string issue

[ironfelix717]

Annoying.


Trying to pass a string - from a specific field's record - to the Dlookup to see for matches.

This code works:

Sub CrossRefUserID()

Set db = CurrentDb
Set rs = db.OpenRecordset("UsersConnected")
Dim cName As String


cName = "DELLT75"


rs.Edit
    rs.Fields("UserID").Value = DLookup("UserID", "Employees", "Domain='" & cName & "'")




End Sub

However... When passing string variable cName from recordset like....

	
	
Sub CrossRefUserID()

Set db = CurrentDb
Set rs = db.OpenRecordset("UsersConnected")
Dim cName As String


rs.Edit
    'cName = rs.Fields("computer name").Value
    rs.Fields("UserID").Value = DLookup("UserID", "Employees", "Domain='" & cName & "'")




End Sub

The Dlookup function throws an error about the query string syntax. Doing a msgbox on cName & in the second code returns a value. So... i'm guessing this is related to the super fun query string in the crtieria for Dlookup.

Any ideas?

Thanks in advance.

[pbaldy]

What value is there and what error is thrown?

[ironfelix717]

What value is there and what error is thrown?

Hi,

Please see below

Sub CrossRefUserID()



Set db = CurrentDb
Set rs = db.OpenRecordset("UsersConnected")
Dim cName As String, userid As String




'cName = "BobsPC"  - No error when passing name directly
rs.Edit
    cName = rs.Fields("computer name").Value
    MsgBox cName ' this returns message box with "BobsPC" - OK! So why would it not work below?
    rs.fields("userid") = DLookup("UserID", "Employees", "[Domain] = '" & cName & "'")


End Sub

Edit: The error is 3075 - syntax on query string or whatever. Its with the Dlookup, which makes absolutely no sense based on the evidence i've just provided.

[ironfelix717]

It is very clear the issue Dlookup is having is with this line...

cName = rs.Fields("computer name").Value

As soon as the cName stirng variable is referenced by the recordset, Dlookup has a fit over it.

Also prevalent in DCount. The syntax is right. When using Dcount with directly passing cName, i get 1 return. As soon as the rs.fields line is used to defined cName, then boom. Error.

[davegri]

Try moving the rs.edit line:

'cName = "BobsPC"  - No error when passing name directly


    cName = rs.Fields("computer name").Value
    MsgBox cName ' this returns message box with "BobsPC" - OK! So why would it not work below?
rs.Edit
    rs.fields("userid") = DLookup("UserID", "Employees", "[Domain] = '" & cName & "'")


End Sub

[ironfelix717]

Hi,


Solved the issue by changing the syntax... which makes no sense how you can pass a string directly with quotes. But when the string is referenced from an object, it won't accept. Newsflash, its still a string. If that wasn't extremely frustrating for the last 2 hours....






Correct syntax (referencing variable cName as string through the recordset field):

cName = rs.Fields("computer name").Value
userid = DCount("UserID", "Employees", "Domain=' & cName & '")

Thanks for your guys help.

[ironfelix717]

OK Update.

While I prematurely solved the thread... this is not solved.

Apparently it is impossible to use Dlookup or Dcount with an input variable as criteria from a recordset.

The following returns no syntax error, but returns 0 matches (there is a match - trust me)

Dim cName As Variant, matchCnt As Integer

        cName = rs.Fields("computer name").Value
       
        matchCnt = DCount("UserID", "Employees", "Domain=' & cName & '")
        MsgBox matchCnt

The following returns no syntax error, but the syntax has changed and the cName variable is directly referenced, and it FINDS A MATCH

Dim cName As Variant, matchCnt As Integer


cName = "JEFF752"


       
matchCnt = DCount("UserID", "Employees", "Domain='" & cName & "'")
MsgBox matchCnt

So, the issue is now that DCount is not finding matches when it should be. I prove this with the first code listed above where...

Dim cName As Variant, matchCnt As Integer

        cName = rs.Fields("computer name").Value
       msgbox cName 
        matchCnt = DCount("UserID", "Employees", "Domain=' & cName & '")
        MsgBox matchCnt

The msgbox returns... yep.. you guessed it. "JEFF752"... and returns 0 for msgbox matchcnt...


So basically this Dlookup function is pointless.

[pbaldy]

The quotes are incorrect in the failing version.

[ironfelix717]

Hi,

Which failing version? They are all failing in my opinion

[pbaldy]

Really? You said this syntax worked. Use it.

The following returns no syntax error ... and it FINDS A MATCH

matchCnt = DCount("UserID", "Employees", "Domain='" & cName & "'")

[davegri]

Don't know your exact table setup, but this worked for me...
Keep in mind, if UserID in UsersConnected is an autonumber, it can't be updated.

[ironfelix717]

Thanks for the reply davegri... I return with more information.

I am so frustrated with Access right now. This is clearly a bug.

When creating a dummy database the following code works. Syntax is correct and returns matches as it should.

Sub CrossRefUserID()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim mtchcnt As Integer, cName As String
Set db = CurrentDb
Set rs = db.OpenRecordset("UsersConnected")


cName = rs.Fields("computer name")
MsgBox cName
mtchcnt = DCount("UserID", "Employees", "Domain='" & cName & "'")
MsgBox mtchcnt
End Sub

Now, when i use that EXACT code in my actual database, the "Error 3075: Syntax Error in string query expression"

Explain that. HAAAA.

Thanks for the help. Scratching my head.

[davegri]

Thanks for the help. Scratching my head.

Something is different in the actual DB. If you can post it here someone will figure it out.

[ironfelix717]

Something is different in the actual DB. If you can post it here someone will figure it out.

Unfortunately I can't post the DB as its proprietary. I could potentially reduce a copy and prove a sanitized version with just the form/tables in suspect.


So, i yet again return with more information... The point of this procedure is to compare connections to user computer names. If a connection contains a computer ID that is not registered in user computer names, then its a foreign connection and should be disconnected (hacker?).

I get the connections by running the following code (i tweaked it but don't take credit)

Sub RunUserCheck()
  Dim cn As ADODB.Connection


  Set cn = New ADODB.Connection
  ' Open the connection
  With cn
    .CursorLocation = adUseServer
    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Mac\Home\Desktop\NewMRP\Database\Database_Main.accdb"
  End With

    Call UpdateUserRoster(cn)
end sub

---------------------------------------------------------


Sub UpdateUserRoster(cnnConnection As ADODB.Connection)


  Dim rstTmp As New ADODB.Recordset
  Dim strTmp As String
  Set db = CurrentDb
  Set rs = db.OpenRecordset("UsersConnected")


  ' This is the value to pass to Jet to get the user roster back.
  Const cstrJetUserRosterGUID As String = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"


  On Error GoTo PROC_ERR


  ' Jet exposes the user roster as a provider-specific schema rowset.
  ' To get Jet to return this, we open a recordset and pass the special GUID value.
  Set rstTmp = cnnConnection.OpenSchema(adSchemaProviderSpecific, , cstrJetUserRosterGUID)


  ' The recordset contains four fields:
  ' COMPUTER_NAME: The machine name of the user's computer
  ' LOGIN_NAME:    The name the user logged into Access with
  ' CONNECTED:     True if the user is still connected
  ' SUSPECT_STATE: Connection was terminated normally or not (generally returns nothing if the user terminated normally or is still in the database)


  If Not rs.BOF = True Then rs.MoveFirst




'  DoCmd.SetWarnings (False)
'  DoCmd.RunSQL "DELETE * FROM [UsersConnected]"
'  DoCmd.SetWarnings (True)


  With rstTmp
    Do Until .EOF
            rs.AddNew
            rs.Fields("Computer Name") = .Fields(0).Value      'Take note to these - i am adding the computer names of the connection to a table "UsersConnected"
            rs.Fields("Windows User Name") = .Fields(1).Value
            rs.Fields("Connected") = .Fields(2).Value
            rs.Fields("Suspect State") = .Fields(3).Value
            rs.Update
     .MoveNext
    Loop
  End With
  rstTmp.Close
    rs.Close
    Call CrossRefUserID  'Compare "UsersConnected" to "Employees" 


PROC_EXIT:


  Exit Sub


PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "ADOShowUserRosterToString"
  Resume PROC_EXIT

End Sub


End Sub

OK... So, the reason the dummy database does not throw an error is because the value is directly typed into the table. When I run the code, it finds a match and no error... However, when I use my actual database and it populates the table via the Sub UpdateUserRoster() code, then SOMETHING happens with the data. I don't know that answer. But WHATEVER is imported into my table is NOT the same as me TYPING into the table manually.

For instance "BobsComputer" typed in by me throws no error. If I let the code run and "BobsComputer" is found as a connected user and wrote to the table, the syntax error returns.


So... SOMETHING is fishy with the UpdateUserRoster code and "what" exactly it is importing into my table. Apparently its not the same as typing it in - yet its still short text.

Need a database expert! Thanks guys.

[davegri]

Do you have OPTION EXPLICIT at the top of every code module?
If not, add that and then Debug > Compile from the VBE menu.
Have you done a compact/repair?