Need help troubleshooting...

[Rican]

I'm trying to use a database that i had at an old job to a new place. The db contains a lot of DLookup code. In this environment, i keep getting a lot of errors because of the DLookups. Is there a way i can change them to something less complicated? One of the DLookup lines go something like this: CallerID = NZ(DLookup("[useridunclass]", "[tblusers]", "[useridunclass] = getuser() "))

I can't find a 'useridunclass' anywhere... I also don't know how to search all the code for it. I would appreciate any help.

 

[dan-cat]

One of the DLookup lines go something like this: CallerID = NZ(DLookup("[useridunclass]", "[tblusers]", "[useridunclass] = getuser() "))

Ughh!

I'm afraid DLookups are usually used because of their simplicity, they are also very bad practice. This command is basically saying find me the value of the field 'useridunclass' contained within the table tblUsers where the same field is equal to the value attained by the function getUser(). They have also used the NZ command but haven't if you see what I mean.


Well by the time the value of the function getUser has been calculated I bet you there was time to open a quick recordset with the appropriate criteria get the value and return the value. The code is slightly more complicated but boy are recordsets useful. Here is an example in DAO:

DIM db as DAO.Database
DIM rec as DAO.Recordset
Dim strSQL As String
Dim strUser As String
Dim strCallerId as String

strUser = getUser()
strSQL = "SELECT userIdunClass FROM tblUsers WHERE userIdunClass ='" & strUser & "'"

Set db = CurrentDb
Set rec = db.OpenRecordset(strSQL)

strCallerId = rec("userIdunClass")

rec.Close
Set rec = nothing
Set db = Nothing

Slightly longer but once you have opened a recordset you can do so much with it (check the number of records, edit values, loop through each record individually...) all without having to calculate your values (ie getUser()) over and over again

 

[Rican]

What can i do if there's no useridunclass field in table tblusers?

 

[Rican]

Also, the getUser is supposed to be the currently logged on user... So that the database automatically parses that info to a value. ?? I don't even know if I can explain it right...

 

[Pat Hartman]

Determine if you need it. If you do, add it (why was it deleted to begin with?). If you don't need it, delete the DLookup()s that reference it.

 

[Rican]

I don't know why it was deleted...I will add it.

 

[dan-cat]

I don't know why it was deleted...I will add it.

Errm all you are going to do is add a field with no values contained therein which seems fairly pointless. Unless the field is populated with values in some way.

If you think about it, the line of code is saying find me the row where the column value of [useridunclass] matches getUser(). Well in your case the value of useridunclass is always going to be null anyway

 

[Rican]

This database is all messed up. I added a field named useridunclass, and it passed the 'stuck' point but now it's looking for a UserHostName. How is it possible that these fields are missing?

 

[Shep]

It's probably looking for something defined by the getUser() function...

Anyway, if there is no field named "useridunclass" in the tblusers table, something is terribly wrong and it's never going to work. You have some work to do.