dlookup

[brorick]

I had created a query that had two tables. I linked the EmployeeID field from table1 to the Login field in table2. This query was used as the record source for a form. Unfortunately for some reason I could not update the query from the form or directly in the query. I removed one of the tables in the query and suddenly I could update the query. To work around this problem I decided to just use a dlookup function in my query. Trust me when I say that I tried for sometime to avoid taking this wierd approach, but I am at a loss for time and need to move forward.

Can anyone tell me how I can reference a field in a table for the criteria in Dlookup.

Example:
Full Name: DLookUp("[Full Name]","Tbl_FDEmployees","[Login]=" & Roster!EmployeeLANID)

Note: Roster is a table name and EmployeeLANID is the field name.

I know that I should reference a field in a form, but this is not an option. I would rather reference another field in the same query but I have had no luck. Any help is greatly appreciated. Thank you. :mkay

[OBP]

Hello again, the usual reason that you can't update a record set is that you have a conflict in the data structure from table relationships.
Is this the database that I helped you with before?

[brorick]

Hello and Happy New Year. This is a different database. I have played quite a bit with the relationship between the two tables and I did not have much success. But, it seemed as though I could get away with using the dlookup function. I welcome any other recommendations?

[OBP]

Happy New Year to you too.
Can, you post or email me something to look at?

[brorick]

I have attached a small sample of the database. It is a very simple layout. As you can see with the form and the query that I do not have the option to update the information. Of course if I remove one of the tables from the query I can update the information. I would prefer to be able to allow the user to view the full name and department name of each enrolled employee. Thanks for your help in advance. :thumb

[OBP]

brorick, I have fixed the query by making the LoginID a key Indexed field and then setting it with a one to many relationship with the Roster EmployeeID.
But the Table design is not efficient, you do not need to have the Employee Name and department in the Roster Table, just the EmployeeID taken from the LoginID, then display the name and Department on the form, don't save it in the Roster Table.
I will post what I think is ideal.

[OBP]

Have a look at this table/query/form design and the relationship setup.

[brorick]

OBP thank you for your help. You once again came through. I went back and looked at my original table based on your recommendation and I discovered that I had 3 records which had been duplicated in error. For this reason I was unable to make the Login field a primary key which in turn prevented me from updating my query. I corrected the problem updated the relationship between the two tables and now I can update the query as needed. Have a great day/night.

[OBP]

Did you have a look at the form with Combo box?