Dlookup using 2 combo boxes to bring back a result

[kalyan46]

Hello, I am using a form which has a several combo boxes.
Ideally, I would like to select LastName, FirstName and then have the EmployeedID (text field) (dlookup) populated based on the results from the 2 mentions combo boxes.



=DLookUp("[EMPLOYEEID]","[Input-EmployeeNames]","LASTNAME = '" & [Forms]![ztestform]![LASTNAMECombo1].[Text] & "'" And "FirstNAME = '" & [Forms]![ztestform]![FirstNAMECombo1].[Text] & "'")



any assistance would be great
Thank you in advance.

[Bob Fitz]

How will you differentiate between employees if you have two people with the same name?
Why have all these different controls. You only need one combo box which can hold all the data you might need from the Employee table.
If you need to see this other detail you would have text boxes that reference the appropriate column of the combo box.

[kd2017]

At first glance the dlookup statement looks fine except for the .[Text] part. Get rid of the .[Text]

Now it will depend on how many columns you have set up for the combos. Can you post the combo's record sources?

[kalyan46]

There is a table that I have for all employee names and ID's (there is currently no duplication of employee First and Last names).
The form will be used to track training that each employee would have on several different type of machines.

The jist of the form would be that the manager would add entries from this form to update a table in the back ground
I am simply trying to remove data inputs and have data prepopulated based on First Name and Last Name (separate columns)

sorry if this is too vague

[kalyan46]

I took out the .[text] from the combo statement and the result is only bringing back the first record of the table.

LastName : SELECT DISTINCT [Input-EmployeeNames].[LastName] FROM [Input-EmployeeNames] ORDER BY [Input-EmployeeNames].[LastName];
FirstName: SELECT DISTINCT [Input-EmployeeNames].[FirstName] FROM [Input-EmployeeNames] ORDER BY [Input-EmployeeNames].[FirstName];

[kd2017]

Why not just have a 1 combobox with 2 columns? first bound column is the employee id and the second columns is the employee's full name concatenated?

SELECT DISTINCT [EMPLOYEEID], [Input-EmployeeNames].[LastName] & ", " &  [Input-EmployeeNames].[FirstName] AS FullName FROM [Input-EmployeeNames] ORDER BY [Input-EmployeeNames].[LastName];

That way the value of the combobox is already the ID of the employee that is selected.

<edit>
What bob said in post 2

[kalyan46]

Maybe I am going at this all wrong

I have a main form (frm-Main LogOn) that gives options to
1 ) Enter a new employee (frm-Add New Employee)
2 ) Add a new training record (frm-LOTO Add record)
to enter any new employees that would not be entered in the Input-EmployeeNames table
then I have an option to add a record for an employee that would receive additional training. I am trying to simplify this form so the manager would have less input and have prepopulated cell criteria.
This

[kalyan46]

Maybe I am going at this all wrong

I have a main form (frm-Main LogOn) that gives options to
1 ) Enter a new employee (frm-Add New Employee) to table Input-EmployeeNames
2 ) Add a new training record (frm-LOTO Add record) to table LOTO Master Data

Option 2) to add a record for an employee that would receive additional training. I am trying to simplify this form so the manager would have less input and have prepopulated cell criteria that would then post to table LOTO Master Data after completion of the form

[kd2017]

Take a look at this quick example

[kd2017]

After looking at your db file I think I see where the confusion is. You don't have any relationships set up. You might want to take a step back and learn some of the basics of relational databases

Here is an presentation I found that might help get you started https://www.youtube.com/watch?v=NvrpuBAMddw

[kd2017]

Here's ms access specific examples
https://www.youtube.com/watch?v=zT8ZXrj5Rpo
https://www.youtube.com/results?sear...+relationships

[Bob Fitz]

Take a look at this quick example

Before you waste any more effort on this db I would also recommend you to read a little about the basics of creating a worthwhile db.
At the very heart of it all are well designed and related tables. IMHO, it's a steep learning curve but there are many knowledgeable and helpful people here that will be only too willing to help you along the way. Ask for help with anything that you don't understand. Make the most of the knowledge that they are willing to give with you. They don't even charge you for it. It's like lessons for FREE !

[kalyan46]

Thank you, I understand how to create and uses databases, just not to familiar with forms to create records
Typically I use access to consolidate large text files to be able to report out to management on what they want to see and streamline reporting.
I am just trying to assist another group that is short handed in my company.
Thank you for your time today

[kd2017]

Thank you, I understand how to create and uses databases, just not to familiar with forms to create records
Typically I use access to consolidate large text files to be able to report out to management on what they want to see and streamline reporting.
I am just trying to assist another group that is short handed in my company.
Thank you for your time today

No one means any offense at all. It's just that based on the design of the db tables you've provided it's quite clear that who ever built this hasn't implemented the basics of relational data modeling. Doing so would make building db apps like this much easier and less prone to error.

That said, to address your immediate question I suggest the following:
1) Open the [frm-LOTO Add record] in design view
2) Delete the last name and first name combo boxes
3) Right click the EmployeeID text box and click Change to > Combo Box
4) In the EmployeeID combobox's properties go to the data tab and set the row source to
SELECT [Input-EmployeeNames].EmployeeID, [Input-EmployeeNames].LastName, [Input-EmployeeNames].FirstName FROM [Input-EmployeeNames] ORDER BY [Input-EmployeeNames].LastName, [Input-EmployeeNames].FirstName;

5) go to the employee id combo box properties' format tab and set the following properties
column count: 3
column widths: 0";2";2"


With that done you can select the employee using this combo box and it will save the selected employee's id to the master data table. later when you query against this table you'll use this id to join the employees table and dynamically show the employee names only when needed. It doesn't make sense to store the names along with the employee id in this table.