Would DLookUp Help?

[pbrown2]

There is a query that runs from a table that is made by using, make table and admend table queries to combine all the sections.
In the report there are the fields [CreatedBy], [ModifiedBy], [MgtModifiedBy] and [RemovedBy]. Instead of the userID being returned, we need to have the concat. of the users actual [LastName] and [FirstName] from the table tblUsers. I have tried to incorporate tblUsers into the the query, however many relationships have been tried, but none seem to work. Therefore, it seems that the only way to return the concatination of [FirstName] [LastName] would be to use DLookUp. Also, I have tried to use the DLookUp for a Unbound text box, but keep getting errors.

How would this be written into the report?

Thank you for any and all help,

PBrown

 

[pbrown2]

Can anyone explain why the below works in a Form but not in a report????

[NetworkID] = Environ("username&quot
Reports![General Info]![Name] = DLookup("[FirstName] & ' ' & [LastName]", "tblUsers", "[UserID] = '" & [NetworkID] & "'&quot


The only change made from going from a form to a report is that:

Forms! was changed to Reports! & [Switchboard] was changed to [General Info].

I even keep the names of the (2) unbound text boxes the same: NetworkID & Name

The error: You can not assign a value to this object keep appearing and debug goes directly to the the [NetworkID] = environ("username&quot. I have even taken that part out and simply put the control source to NetworkID = environ("username&quot, and took out line 1 of the on open. However, the same error appears.

Anyone have any suggestions??? It seems I am on the right path, or am I??

Thank you for any and all help,

PBrown

 

[dhookom]

If [CreatedBy], [ModifiedBy], [MgtModifiedBy] and [RemovedBy] all contain the UserId then you should be able to add 4 copies of tblUsers and join them to the fields. You may need to use joins that select all records from main table. Create alias for the tblUsers fields like:
CreateName:tblUsers.FirstName & ", " & tblUsers.LastName
ModifyName:tblUsers_1.FirstName & ", " & tblUsers_1.LastName
etc.
DLookup() is generally a horrible waste of resources.
Another neat alternative is to display the users names in a combo box that is bound to the CreateBy (etc) fields. The row source of the combo boxes would be something like
SELECT UserID, FirstName & ", " & LastName
FROM tblUsers


Duane
MS Access MVP

 

[pbrown2]

It always the "simple" things....

I appreciate your help!


Perhaps another item is as "simple" and I would be greatful if you could take a look. The title is "Repeating Header" and is in this forum, sorry, but I do not know how to attach the direct thread link.....


Once again,

Thank you!!!!!