Access 365: Search LastName in one table to put EmployeeID in another

[renigar]

This is my first stab at a relational database and it works (I don't know the most efficient way to do things and wouldn't have gotten this far without google) but the user has asked for some tweaks.
I created an Employee database that is used for general employee information, keeping track of employee evaluation dates, and uniform inventory (uniform stock in, uniforms issued to employee). What currently has me stumped (maybe a mental block) is, I have a form for UniformsOut, which fills in the UniformsOut Table from the white background fields. The gray background fields are for user convenience. See below.

The EmployeeID combo box when dropped down shows the EmployeeID, LastName and FirstName in 3 columns (from the EmployeeInfo table).

By user request I'd like to use the Find button to search the last name (typing in the first few letters) from the EmployeeInfo table and when a selection from the filtered list is made have it drop the EmployeeID in its linked field on this form.

Questions showing my lack of knowledge:
If a database is open are the tables in that database open even if there not on screen?
If not, does the EmployeeID table have to be open to search it?
If not, how does DLookup do it?

If there is a better way to do things, I'm open to any suggestions.

Thanks,
renigar

Here is the start of some code:

'VBA code'
Private Sub UniOutFindBtn_Click()

Dim strLN As String
Dim EmpID As Long

strLN = InputBox("Please Enter Last Name" & vbNewLine & "To Search For", "Last Name Search")

If strLN = "" Then Exit Sub

[EmployeeInfoT].Filter = "LastName Like ""*" & strLN & "*"""
[EmployeeInfoT].FilterOn = True


End Sub

 

[dhookom]

I expect you could use a combo box row source like:
SELECT [Employees].[EmployeeID], [Employees].[LastName], [Employees].[FirstName]
FROM [Employees] ORDER BY [LastName], [FirstName];

Set the properties of the combo box
Bound Column: 1
Column Widths: 0";1";1"
Column Count: 3

You can skip all of the DLookup()s if you add the columns to your combo box [=[cboEmpID]] and then replace the DLookup() with something like:

=[cboEmpID].Column(x) where "x" is the column number beginning with 0 as the first column.

This should enable a find as you type for the last name.

I'm curious about your table structure.

 

[renigar]

dhookom,
Thanks for your help. I had the combo box row source almost like you suggested. I added the Division column. I didn't have the brackets around the field names, and it worked as far as showing the information. When I put the brackets on the field names it didn't work at all. I used your cbo.column(x) and that works great. I was not able to type letters of the last name in the EmployeeID field. It threw an invalid entry error. I suspect because the field is set as a number. Do you think it would work if I set the property to text in the table?

As to table structure, I have five tables:
EmployeeInfoT Primary key is EmployeeID
EvaluationDatesT Primary key is Auto number, foreign key is EmployeeID
UniformItemsT Primary key is UniformID
UniformInT Primary key is Auto number, foreign key is UniformID
UniformOutT Primary key is Auto number, foreign key is UniformID, also has an EmployeeID field

One of the Queries I have is UniformStockQ to do the math for uniform stock on hand.

There is more to it.

renigar

 

[dhookom]

Do not change the data type of the EmployeeID field. Did you set the column widths as suggested?

 

[Andrzejek]

When I put the brackets on the field names it didn't work at all

You don't need the brackets if: 1. you do not have spaces in your table/field names, and 2. you do not use reserved words for your names.
So, your SQL may simply look like:

SELECT EmployeeID, LastName, FirstName
FROM Employees ORDER BY LastName, FirstName;

 

[dhookom]

Andy is correct about the brackets. Access puts them (and table names) in when creating queries and I usually remove the unnecessary ones when posting but got a bit lazy.

 

[renigar]

Thank you. I overlooked setting the ID column to 0. So the search now works but I would still like the ID to appear in the box once the selection is made. Is that possible?

 

[dhookom]

You can concatenate the name fields with the ID or add another text box near the combo box with a control source of:
=[EmployeeID]

Row Source:

SELECT EmployeeID, LastName & ", " & FirstName & " " & EmployeeID As FullName
FROM Employees 
ORDER BY  LastName & ", " & FirstName & " " & EmployeeID;

This change your combo box to two columns.

 

[renigar]

Thanks again dhookom,
I rearranged things a bit an ended up making the box for the EmployeeID use the =[cboEmpID].Column(x) tip you showed me. This works fine for a visual reference to the number.