Dlookup Function

mba_110

Board Regular
Joined
Nov 28, 2012
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone.

I am trying to pull information of [Employees_table] based on [Emp_ID] on access 2010 form i have created combo box for [Emp_ID] and named as "Emp_IDcombo" on form i have several fields from tables and queries to be pulled on form's text box fields however, i have following problem with my first attempt "EmployeeNametxt" text box on my form to pull it from [Employees_table]'s "EmployeeName".

I manage to write this expression on control source of combo box =DLookUp("[EmployeeNametxt]","Employees_table","Emp_ID=' " & [me].[Emp_IDCombo] & " ' ") but unable to get correct result and it is showing #Name? error.

My [Emp_ID] field in [Employees_table] is Text field and [EmployeeName] is also text field in table.

Please can anyone help to fix this please.


thanks & regards,

MBA
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You would do better to avoid DLOOKUP in this circumstance. You can do all the work with the combo using a query. I'm assuming that you want to display the employee's name in the combo. if so, try this:
In the combo's Properties, go to the Data tab and select the Control Source. Select the name of the field that will store the data; the data type should match the emp_ID data type.
Make sure that the Row Source Type is Table / Query.
When you click the ... button to the right of the Row Source you will go to a query designer. Pull down the Employees table and add Emp_ID and EmployeeNametxt to the grid. If you like, you can sort Ascending on the EmployeeNametxt field. Close the grid, save when prompted.
Now go to the Format tab. Adjust Column Count to 2, Bound Column to 1 (so the field stores the Emp_ID) and set the Column Widths to 0;3

This will store the ID and display the name, and it should be much faster than a DLOOKUP.

Denis
 
Upvote 0
Many thanks denis

However, i have solved the above problem but i have another issue with those tables which is not having direct relationship either many to many or one to many relationships, let me give you the exact details.

I have Employees_table which is connected to Nationality_table by intermediate relationship without any FK or PK the field i want from Nationality_table is [Country] and nationality_tables PK is N_ID i want this based on Emp_ID on my form once i select employee ID than it should show me employees Country.

I did try doing it with DLookup function but unable to succeed here is my try

=DLookUp("[Country]","[Nationality_table]","[Emp_ID]='" & [Emp_IDCombo] & "'")

Please if its need coding for VBA than kindly provide the code or coding procedure.


thanks & Regards,

MBA
 
Upvote 0
Just a couple of questions to clarify:
1. is EMP_ID a number? If it is, you are using the syntax for text and you should instead try
Rich (BB code):
=DLookUp("[Country]","[Nationality_table]","[Emp_ID]=" & [Emp_IDCombo])


2. Does your join table have an Emp_ID field? It should, so that you can relate the two easily. As long as you have proper primary keys, queries joining 2 or more tables should be editable. I would base your form on a multi-table query. That way, you can just include the table / query fields in the form every time you want to see related data. No need for DLOOKUPS, which will both make your life easier and improve performance.

Denis
 
Upvote 0
Many thanks Denis,

I have used your words as reference to solve the problem.

Here i came again with same issue of access forms i hope you will not mind answering this also.

I have table called "Contracts_table" which is linked to "Employees_table" via [Contract_ID] however, i am trying to do a form that enter data in "Contracts_table" the normal way of doing it selecting the fields of contracts table but the twist in that is i have added combo box to [Emp_ID] in contracts_form and added Contractstablesub_Form to contracts form in order to see the all contracts before entering the new.

Now what i need is when i select the combo box of [Emp_ID] it should show me the fields of that employee contract which is obviously recorded before and it has like fifteen text box fields on form which is coming from contracts_table all this must be appear based on combo selection but no one should be able to change it the previous records unless Modify button is provided on form, secondly if i need to add new contract than form should allow me to put details on all those fifteen text boxes of contracts_table and it should update the contracts_table also Dlookup is working only to see the previous records based on combo selection but when i need to enter new records it is not allowing me to enter data because of Dlookup function is recorded on control source of each text boxes.

below is the fields name & references of my database.

[Emp_ID] {FK} is text field in Contracts_table and Contracts_table connected to Employees_table {PK} via [contracts_ID] which is Autonumber field and Row Source is SELECT [Employees_table].[Emp_ID], [Employees_table].[EmployeeName] FROM Employees_table; and combo box name is EmpIDcmbo.

Fields of Contracts_table is SELECT [Contracts_table].Contract_ID, [Contracts_table].Emp_ID, [Contracts_table].HireDate, [Contracts_table].EmployeeName, [Contracts_table].Department, [Contracts_table].Designation, [Contracts_table].Basic, [Contracts_table].Housing, [Contracts_table].Food,[Contracts_table].Transportation,[Contracts_table].GrossSalary,[Contracts_table].Leave,[Contracts_table].ticket,[Contracts_table].status,[Contracts_table].CalculationDate,[Contracts_table].contractType

Its look like coding need to be done it can be also solve using two forms as far as i know but not really wanted to do two forms same thing.

thanks & regards,

MBA
 
Upvote 0
i think you would benefit from looking at some tutorials on form design because it sounds like you're treating Access like a spreadsheet. That will only cause you pain.
in this case, what you need is two forms set up as a main form and a subform. Which is the main form can be decided like this: Main --> Sub is one -- > many. Look at your relationships. if Employees is on the One side, that is the main form and Contracts will be the subform.

OK. Subform should be fairly compact, if you can manage it. Create the Contracts form and lay out the fields, then save and close it. Make sure that Emp_ID is one of the fields on the Contracts form.
Open Employees in Design view, then drag and drop Customers into the Detail section of Employees.
IMMEDIATELY go to the properties, select the Data tab, and check to see that the Master and Child fields are populated. They should both be Emp_ID; if not, enter the field names.
Save, and go check it out. Every contract associated with an employee will sync with that employee's record. And every sub record created in this form stays linked to the correct employee. You can spend time adjusting layouts and making it look good, but that technique will get you started.

Denis
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top