DLookup question

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I posted this earlier but is getting further down on the
list and afraid it will go without further reply.

I have a form (usrfrmPRAProjectTeamMembers)that is a
continuous form. There are 6 fields on the form (one
being ProjectTeamMemberEmail. The entry to all these
fields are stored on another table
(usrtblPRAProjectTeamMembers). The field
(ProjectTeamMember) on the form is a drop list that
populates from another table (tblTeamMembers). What I
would like to do is to reduce the amt of time for the user
by not having to type in all the info for a selected
employee. When the user selects an employee's name in the
drop list, the remaining fields fill in with the
employee's info. The following is a line from the
AfterUpdate event of the ProjectTeamMember drop list.
This does not produce an error but the field on the form
is blank. Am I missing something here or what? Can
DLookup even be used on a continuous form?

***************
Me.ProjectTeamMemberEmail = DLookup
("", "tblTeamMembers", "[ProjectTeamMemberEmail]
= '& Forms![usrfrmPRAProjectTeamMembers]!
[ProjectTeamMember]'")

***************
Any assistance given is appreciated and thanks in advance.
*** John
 
You may have the wrong quotes, try:
DLookup
("", "tblTeamMembers", "[ProjectTeamMemberEmail]
= '" & Forms![usrfrmPRAProjectTeamMembers]!
[ProjectTeamMember] & "'")

ed
 
Ed, tried it and the box is still empty. No errors. Any
other thoughts?
** John

-----Original Message-----
You may have the wrong quotes, try:
DLookup
("", "tblTeamMembers", "[ProjectTeamMemberEmail]
= '" & Forms![usrfrmPRAProjectTeamMembers]!
[ProjectTeamMember] & "'")

ed

[QUOTE="JohnE"]
I posted this earlier but is getting further down on the
list and afraid it will go without further reply.

I have a form (usrfrmPRAProjectTeamMembers)that is a
continuous form. There are 6 fields on the form (one
being ProjectTeamMemberEmail. The entry to all these
fields are stored on another table
(usrtblPRAProjectTeamMembers). The field
(ProjectTeamMember) on the form is a drop list that
populates from another table (tblTeamMembers). What I
would like to do is to reduce the amt of time for the user
by not having to type in all the info for a selected
employee. When the user selects an employee's name in the
drop list, the remaining fields fill in with the
employee's info. The following is a line from the
AfterUpdate event of the ProjectTeamMember drop list.
This does not produce an error but the field on the form
is blank. Am I missing something here or what? Can
DLookup even be used on a continuous form?

***************
Me.ProjectTeamMemberEmail = DLookup
("[Email]", "tblTeamMembers", "[ProjectTeamMemberEmail]
= '& Forms![usrfrmPRAProjectTeamMembers]!
[ProjectTeamMember]'")

***************
Any assistance given is appreciated and thanks in advance.
*** John
[/QUOTE]


.
[/QUOTE]
Click to expand...
 
If you want to send me the database, I will look at it.
mailto:[email protected]

JohnE said:
Ed, tried it and the box is still empty. No errors. Any
other thoughts?
** John

-----Original Message-----
You may have the wrong quotes, try:
DLookup
("", "tblTeamMembers", "[ProjectTeamMemberEmail]
= '" & Forms![usrfrmPRAProjectTeamMembers]!
[ProjectTeamMember] & "'")

ed

[QUOTE="JohnE"]
I posted this earlier but is getting further down on the
list and afraid it will go without further reply.

I have a form (usrfrmPRAProjectTeamMembers)that is a
continuous form. There are 6 fields on the form (one
being ProjectTeamMemberEmail. The entry to all these
fields are stored on another table
(usrtblPRAProjectTeamMembers). The field
(ProjectTeamMember) on the form is a drop list that
populates from another table (tblTeamMembers). What I
would like to do is to reduce the amt of time for the user
by not having to type in all the info for a selected
employee. When the user selects an employee's name in the
drop list, the remaining fields fill in with the
employee's info. The following is a line from the
AfterUpdate event of the ProjectTeamMember drop list.
This does not produce an error but the field on the form
is blank. Am I missing something here or what? Can
DLookup even be used on a continuous form?

***************
Me.ProjectTeamMemberEmail = DLookup
("[Email]", "tblTeamMembers", "[ProjectTeamMemberEmail]
= '& Forms![usrfrmPRAProjectTeamMembers]!
[ProjectTeamMember]'")

***************
Any assistance given is appreciated and thanks in advance.
*** John
[/QUOTE]


.
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Ed, thanks for the offer but it is rather large and much
of the info is confidential.
I'll explore some other possibilities.
** John
-----Original Message-----
If you want to send me the database, I will look at it.
mailto:[email protected]

JohnE said:
Ed, tried it and the box is still empty. No errors. Any
other thoughts?
** John

-----Original Message-----
You may have the wrong quotes, try:
DLookup
("", "tblTeamMembers", "[ProjectTeamMemberEmail]
= '" & Forms![usrfrmPRAProjectTeamMembers]!
[ProjectTeamMember] & "'")

ed

I posted this earlier but is getting further down on the
list and afraid it will go without further reply.

I have a form (usrfrmPRAProjectTeamMembers)that is a
continuous form. There are 6 fields on the form (one
being ProjectTeamMemberEmail. The entry to all these
fields are stored on another table
(usrtblPRAProjectTeamMembers). The field
(ProjectTeamMember) on the form is a drop list that
populates from another table (tblTeamMembers). What I
would like to do is to reduce the amt of time for[/QUOTE] the
user[QUOTE]
by not having to type in all the info for a selected
employee. When the user selects an employee's name[/QUOTE] in
the[QUOTE]
drop list, the remaining fields fill in with the
employee's info. The following is a line from the
AfterUpdate event of the ProjectTeamMember drop list.
This does not produce an error but the field on the form
is blank. Am I missing something here or what? Can
DLookup even be used on a continuous form?

***************
Me.ProjectTeamMemberEmail = DLookup
("[Email]", "tblTeamMembers", "[ProjectTeamMemberEmail]
= '& Forms![usrfrmPRAProjectTeamMembers]!
[ProjectTeamMember]'")

***************
Any assistance given is appreciated and thanks in advance.
*** John



.
[/QUOTE][/QUOTE]


.
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
John,

The preferred method would be to not use DLookup at all. You indicate that
you have a drop list. I will assume you are using a combo box, though it
could be a list box. For your combo box Row Source, use (or create) a query
that contains all the columns you want from tblTeamMembers. Then, in your
AfterUpdate event for cboProjectTeamMember, update the other values as
follows:

txtProjectTeamMemberLstname = cboProjectTeamMember.Column(1)
txtProjectTeamMemberFstname = cboProjectTeamMember.Column(2)
txtProjectTeamMemberEmail = cboProjectTeamMember.Column(3)
etc.

Note that the column numbers are zero-based: the first column from the combo
box is 0, the second is 1, ...

Especially because you are using code behind your forms, I would also
strongly recommend that your control names be different from your field
(column) names. There are conventions for this: normally combo boxes have
the prefix "cbo", text boxes have the prefix "txt", and so forth. This
helps eliminate ambiguity, i.e. are you intending to update a field or the
text box that contains its value?

Hope this helps. Let me know if you need further explanation.


--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184
 
Back
Top