How to write DLookup when criteria is based on joined fields

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have a table named Employees that has fields LastName,
FirstName and Email.

Using a query, I have joined the LastName and FirstName
as
mName = Me.txtLast & ", " & Me.txtFore.

The value of mName is then stored in the field Name in a
table called Assignments. It appears, for example,
as "Jet, Joan".

In a form called EmployeeAssignments, I show the value of
Name from the Assignments table.

On the form, I want to display (not store) the Email
address for the person. However, I can't figure out the
correct syntax for the DLookup command, since the value
of Name in the EmployeeAssignments table is differs from
the way it is stored in Employees. (Obviously, the value
of Email will change as the user moves from one record on
the form to another.)

Can anyone educate me on this?
 
The usual approach to this is to add a primary key field to your table
Employees, so that each record has a unique identifier.
Can you really be sure that you will never have two John Smiths working
there?

The value of the primary key is then stored in the table Assignments.
BTW, "Name" is a dangerous choice for the field name, as this word has
special meanings in Access and could easily be misinterpreted.

HTH
- Turtle
 
A much better approach would be to store EmployeeID in both tables - then
the Dlookup can be based on EmployeeID. If you use a combo in
EmployeeAssignments to select the EmployeeID you can include any combination
of the name fields in the combos RowSource query.
 
Thanks for that, Turtle and Sandra. I've changed the
names of things since my original post, but what I want
to do remains the same. As you suggested, I have also
changed the field names in both my tables (Corrrective
Actions and EmployeePhoneList) so they both refer to
EmployeeID. The two tables are joined them on EmployeeID.
However, I'm still *completely* confused as to how to get
information from one form into the other.

Form Corrective Actions calls up information in the
Corrective and Preventive Actions table. To enter
information in the Requestor field of the table
Corrective Actions, the user clicks on command button
that opens the form FrmEmployeePhoneList.

Form EmployeePhoneList opens. It is based on a query and
lists information like EmployeeID, Name (a join between
LastName and FirstName), Phone, and Email. Now, here is
what I want it to do:

When the user double-clicks on a name, it stores the
EmployeeID. The form closes and the user is returned to
Corrective Actions.

On Corrective Actions, the field Requestor is filled in
with the name of the employee associated with the
EmployeeID. In addition, I also want the phone number and
email data on the employee to be displayed, but not
stored.

I know this should be easy, but I can't get my head
around what commands to use and where to put them. Your
continued help will be greatly appreciated!
 
Hi Chris,

You might find it much easier to change Requestor control of the Corrective
Actions form into a combo box control. The rowsource for your combo would be
essentially the same query that you are currently using for the
EmployeePhoneList form. Set the ColumnCount property of the new combo to the
number of columns you want to have visible when the combo is expanded
(dropped down). The bound column of the combo would be the column that
contains the EmployeeId. Let's say the combo control is named cboEmployeeId.

Then to display the related Employee information you create a new textbox
control for each column you want to display. In the ControlSource of the
textbox you use a reference back to the combo box which pulls the
information from the appropriate column of the currently selected row. So if
your textbox is for the Phone number which is the 3rd column, the
ControlSource would be:

=cboEmployeeID.column(2)

The column property is indexed starting with 0 so column(2) points to the
3rd column.

That's it -

Let me know if you really want to stick with the 2nd form method, this is
also a valid way to select data but the combo control handles it very nicely
without alot of extra code.
 
Hi again, Sandra - with many thanks!

The combo box was my first option and worked well, but
won't do the trick because my Employee List form contains
LOTS of additional information that the user will need to
know before selecting the name from the list, as well as
containing options for updating the info, sending emails,
etc. That form is also going to be called by a number of
different forms for applications other than my current
Corrective Action project. (I AM ambitious, LOL!)

Thus, what I said earlier is really my prefered method.
It is, as you probably have already guesses) beyond my
ability right now, but if you can provide the code I'll
be able to actually see the logic and use it to aid my
understanding for other projects.

Sincerly, Chris
 
Hi Chris,

No problem (and it's not *that* hard). I just wanted to make sure that you
had considered the easier route and/or had a good reason for doing it
differently. It sounds like you do have a good reason.

Aside: my company has an 3rd Party application that makes extensive use of
2nd form lookups rather than combo style lookups. Many of these could have
been done as a combo and it is rather annoying to me knowing that it could
have been done differently. It's not the practice itself, its the
overuse/misuse of it that annoys me.

Anyway - here's how I would do it - this should work well from other forms
as well. Here's the overview:

Command Button on calling form calls public function to get EmployeeID
public function opens EmployeeList in dialog mode Double Click event of
EmployeeName control hides EmployeeList form
Public function pulls current EmployeeId from hidden Employeelist form
Public function closes Employeelist form

A word about using the acDialog option of the windowmode parameter of
docmd.openform - this is the method I am using to open the dialog form. This
option causes the calling code to pause until one of two things occur 1) the
dialog form is closed, 2)the dialog form is hidden. By hiding the dialog
form when the selection is complete, the function can grab the current value
from the dialog form itself - no additional interface is needed.


So you've got code in 3 places and the above shows you how the pieces
interact. Here's the code:

Command button on calling form
-----------------------------------------------
Private Sub cmdGetEmployeeID_Click()
Me.EmployeeID = getEmployeeId()
End Sub

Public Function - in Standard Module
---------------------------------------------------
Public Function getEmployeeId() As Long
Dim curinfo As EmployeeInfo
DoCmd.OpenForm "frmEmployeeList", , , , , acDialog
If IsLoaded("frmEmployeeList") Then
getEmployeeId = Forms!frmEmployeeList.EmployeeID
DoCmd.Close acForm, "frmEmployeelist"
End If

End Function
Public Function IsLoaded(ByVal strFormName As String) As Integer
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function

EmployeeListForm -
------------------------------
Private Sub EmpFirstName_DblClick(Cancel As Integer)
Me.Visible = False
End Sub


That's it - I would probably add a command button in the detail section of
the EmployeeList form which does the same as the doubleclick event that you
attach to whichever control(s) you want to use to designate that a selection
has been made.

Also, on the dialog form I usually add a cancel button which closes the form
(instead of hiding it by setting the visible property to false). If you'll
notice in the getEmployeeID function above, nothing happens if the dialog
form is closed when control returns to the function - the getEmployeeId
action is canceled.
 
By the way Chris - this sounds like an ISO 9000 project! Are by any chance
an ISO guru?
 
Thank you for the code, Sandra. I've written all the code
in that you provided (Thank you!!!) but something isn't
working. I get a "Compile error/User-defined type not
defined" on the command Dim curinfo as EmployeeInfo.

After fixing that, do I issue the DLookup command in the
ControlSource boxes, and how would I write that?

Also, call me dense, but I don't understand what you mean
by "add a command button in the detail section of the
EmployeeList form which does the same as the doubleclick
event that you attach to whichever control(s) you want to
use to designate that a selection has been made". What is
the purpose of the button?

BTW, Yes - I am an "ISO Guru" !! Will be happy to assist
with any of your Quality-related issues in return for your
help on this!

Chris
 
First things first!
BTW, Yes - I am an "ISO Guru" !! Will be happy to assist
with any of your Quality-related issues in return for your
help on this!
Click to expand...

Whoopie!! If I do your database will you do my QMS??!! Just kidding - though
2 months ago I was pretty desparate! We are on the verge of our initial
registration audit having come through a preassessment/internal audit
without too many serious findings.
I get a "Compile error/User-defined type not
defined" on the command Dim curinfo as EmployeeInfo.
Click to expand...

That would be because I goofed and forgot to fix one thing - just delete
that line entirely - you don't need it here.

Actually, I wrote this thing in two versions. The one I posted was the
simplified version. The other version which I will post below replaces the
need for a Dlookup on the textboxes. Since the EmployeeList form already
has this information it is more efficient to simply pass that information
back to the calling form. The second version of the function uses a
userdefined type containing one or more elements - this just makes it easy
to pass multiple values back from a function.

Here's the code:

In the standard module:
------------------------------------
Public Type EmployeeInfo
'add other elements as necessary
EmployeeID As Long
EmpFirstName As String
EmpLastName As String
End Type

Public Function getEmployeeInfo() As EmployeeInfo
Dim curinfo As EmployeeInfo
'create a new instance of the type
DoCmd.OpenForm "frmEmployeeList", , , , , acDialog
If IsLoaded("frmEmployeeList") Then
'each element of the structure is assigne a value from the
'dialog form
curinfo.EmployeeID = Forms!frmEmployeeList.EmployeeID
curinfo.EmpFirstName = Forms!frmEmployeeList.EmpFirstName
curinfo.EmpLastName = Forms!frmEmployeeList.EmpLastName
getEmployeeInfo = curinfo
DoCmd.Close acForm, "frmEmployeelist"
End If
End Function

On the main form
-------------------------
Private Sub cmdGetEmployeeInfo_Click()
Dim curinfo As EmployeeInfo
curinfo = getEmployeeInfo()
' Use each element of the returned structure to
' assign values to the unbound textboxes.
Me.EmployeeID = curinfo.EmployeeID
Me.EmpFirstName = curinfo.EmpFirstName
Me.EmpLastName = curinfo.EmpLastName
End Sub

Let me know if you still need to (or would rather) use Dlookups to fill the
unbound textboxes.
Also, call me dense, but I don't understand what you mean
by "add a command button in the detail section of the
EmployeeList form which does the same as the doubleclick
event that you attach to whichever control(s) you want to
use to designate that a selection has been made". What is
the purpose of the button?
Click to expand...

If your users know to double click on a field then it has no purpose. Some
of mine will use implied actions (double clicking on a control rather than a
button) but others need to see a command button before they realize that
they can click something to cause something else to happen. No amount of
training seems to cure this so I always add a button in the detail section.
I use the same event code in the command button's click event that I use in
the double-click events of any controls.
 
Sandra, the logic is starting to become a bit more clear
to me now, but I'm still having problems. I get a "Sub or
Function not defined" at the code line beginning
If IsLoaded("frmEmployeeList") Then

I THINK I still need the DLookup function because the only
information I want to actually store in the
CorrectiveAction table (in a field called Requestor) is
the EmployeeID. The Corrective Action form will show the
EmployeeID and LastName + ", " + FirstName. (I understand
this is the correct way to construct a table so you don't
duplicate or store unnecessary information in multiple
tables.)

(Additional fields such as Reviewer and Assignee will be
just like the Requestor field.)

After all this, Sandra, I WILL write your QMS for you!LOL!
If you would like advice, let me know and I'll send you my
email address. I've successfully taken our company to the
new standard from scratch and know the possible pitfalls.

Cheers,
Chris
 
Hi Chris,

You still need the Isloaded function from my earlier post - I'll include it
again below -

Public Function IsLoaded(ByVal strFormName As String) As Integer
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function

As long as your control(s) for name are unbound, it doesn't really matter
how you get data into them. The assignment statement works just as well as
the Dlookup - better actually if the data was already available on the
EmployeeList form since this saves another round trip to the table. By
unbound, I mean that the ControlSource of the control is empty and therefore
whatever goes into the control is *not* saved in any field of a table. You
are correct that storing EmployeeID only is preferable.

Just change the event code on the main form to form your name string (I
forgot that you wanted to put it into a single string)

Private Sub cmdGetEmployeeInfo_Click()
Dim curinfo As EmployeeInfo
curinfo = getEmployeeInfo()
' Use each element of the returned structure to
' assign values to the unbound textboxes.
Me.EmployeeID = curinfo.EmployeeID
Me.EmpFullName = curinfo.EmpLastName & ", " & curinfo.EmpFirstName
End Sub

Please do send me your email address - there are numerous times I have
questions about ISO and I spend hours poring over the internet looking for
good info. I've found a few newgroups but they are not as active as I would
like! My email is smdaigle which is at the mvps.org web site. I should just
spell it out correctly but spam is getting out of control.
 
Back
Top