Something wrong with dlookup?

[Afrosheen via AccessMonster.com]

Thanks for reading this.

I'm using a Dlookup routine to find the email address where the yes/no field
= true and that part works.

160 Email = Nz(DLookup("EMail", "tblSupervisor", "TapCo"))
170 txtTapCo = Me.Supervisor
180 strBCC = strBCC & "; " & Email
MsgBox strBCC

Once the field is found though the txtTapCo=Me.Supervisor is supposed to
return with the value of the person found in the dlookup, I thought. It
doesn't. What happens is that the txtTapCo will come up with some other name
entirely.

For ex: The dlookup will find and show me the email address of the person
where the TapCo = true. I need to also get the name of the person.

What am I doing wrong?

 

[Jack Leach]

160 Email = Nz(DLookup("EMail", "tblSupervisor", "TapCo"))

170 txtTapCo = Me.Supervisor
180 strBCC = strBCC & "; " & Email

txtTapCo is being set the the value of the control Supervisor on the form,
and seems to have no relation to the DLookup value from the previous line.
If you want txtTapCo to be the supervisor from the same record that Email
came from, you'll want to run another Dlookup to get that value as well,
rather than pulling it from a form.

ex:
Email = Nz(DLookup("EMail", "tblSupervisor", "TapCo"))
txtTapCo = Nz(DLookup("Supervisor", "tblSupervisor", "TapCo"))

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[BruceM]

Your DLookup is telling Access to find an EMail field in tblSupervisor in
which TapCo is true. If there is more than one such record Access will
return the first instance (I assume by physical order in the table).

If you are storing the Supervisor name rather than an ID you would do
something like this:
Email = Nz(DLookup("EMail", "tblSupervisor", "Supervisor = " &
Me.Supervisor))

If you are selecting Supervisor from a combo box or list box, the Row Source
should be able to include the E-mail address in a hidden column. For
instance, if the Row Source is Supervisor & EMail, set the combo box Bound
Column to 1, the Column Count to 2, and the Column Widths to something like
1.5";0"

Now you can reference the hidden column as needed:

MsgBox strBCC & "; " & Me.cboSupervisor.Column(1)

cboSupervisor is the name of the combo box bound to Supervisor. Column
count is zero based when using the Column property, so Column(1) is actually
the second column (the first is 0).

If you are not using a combo box it may be possible to include the E-mail in
the form's Record Source query, but there is not enough detail to say for
sure how that would work.

If a Supervisor's name changes, what would you like to have appear in
records from before the name change: the old name or the new one? If you
would like to have old records reflect the current name, consider using an
unchanging ID number rather than the name, if you are not already. If you
are storing the full name in the field, consider breaking it into FirstName
and LastName fields at least, which will give you more flexibility in
searches, sorting, and so forth. It is almost always the best choice to use
an ID number for a name record.

 

[Afrosheen via AccessMonster.com]

Thanks fellas for coming back so quick. I see where I was wrong and used the
suggestions. I just thought that if I after finding a record with the dlookup
i could manipulate the field information.

BruceM: I'll take a look at how I set up the table and see what I can do.

Jack: If you'll remember the report question I had and we worked on, after
your suggestions I finally got that singing.

Your DLookup is telling Access to find an EMail field in tblSupervisor in
which TapCo is true. If there is more than one such record Access will
return the first instance (I assume by physical order in the table).

If you are storing the Supervisor name rather than an ID you would do
something like this:
Email = Nz(DLookup("EMail", "tblSupervisor", "Supervisor = " &
Me.Supervisor))

If you are selecting Supervisor from a combo box or list box, the Row Source
should be able to include the E-mail address in a hidden column. For
instance, if the Row Source is Supervisor & EMail, set the combo box Bound
Column to 1, the Column Count to 2, and the Column Widths to something like
1.5";0"

Now you can reference the hidden column as needed:

MsgBox strBCC & "; " & Me.cboSupervisor.Column(1)

cboSupervisor is the name of the combo box bound to Supervisor. Column
count is zero based when using the Column property, so Column(1) is actually
the second column (the first is 0).

If you are not using a combo box it may be possible to include the E-mail in
the form's Record Source query, but there is not enough detail to say for
sure how that would work.

If a Supervisor's name changes, what would you like to have appear in
records from before the name change: the old name or the new one? If you
would like to have old records reflect the current name, consider using an
unchanging ID number rather than the name, if you are not already. If you
are storing the full name in the field, consider breaking it into FirstName
and LastName fields at least, which will give you more flexibility in
searches, sorting, and so forth. It is almost always the best choice to use
an ID number for a name record.

Thanks for reading this.

[quoted text clipped - 17 lines]

What am I doing wrong?

 

[Jack Leach]

Jack: If you'll remember the report question I had and we worked on, after

your suggestions I finally got that singing.

good to hear

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[BruceM]

As for "manipulating the field information", it depends entirely on what you
are trying to do, how you are trying to do it, and what data are available
to the code or expression.

Thanks fellas for coming back so quick. I see where I was wrong and used
the
suggestions. I just thought that if I after finding a record with the
dlookup
i could manipulate the field information.

BruceM: I'll take a look at how I set up the table and see what I can do.

Jack: If you'll remember the report question I had and we worked on, after
your suggestions I finally got that singing.

Your DLookup is telling Access to find an EMail field in tblSupervisor in
which TapCo is true. If there is more than one such record Access will
return the first instance (I assume by physical order in the table).

If you are storing the Supervisor name rather than an ID you would do
something like this:
Email = Nz(DLookup("EMail", "tblSupervisor", "Supervisor = " &
Me.Supervisor))

If you are selecting Supervisor from a combo box or list box, the Row
Source
should be able to include the E-mail address in a hidden column. For
instance, if the Row Source is Supervisor & EMail, set the combo box Bound
Column to 1, the Column Count to 2, and the Column Widths to something
like
1.5";0"

Now you can reference the hidden column as needed:

MsgBox strBCC & "; " & Me.cboSupervisor.Column(1)

cboSupervisor is the name of the combo box bound to Supervisor. Column
count is zero based when using the Column property, so Column(1) is
actually
the second column (the first is 0).

If you are not using a combo box it may be possible to include the E-mail
in
the form's Record Source query, but there is not enough detail to say for
sure how that would work.

If a Supervisor's name changes, what would you like to have appear in
records from before the name change: the old name or the new one? If you
would like to have old records reflect the current name, consider using an
unchanging ID number rather than the name, if you are not already. If you
are storing the full name in the field, consider breaking it into
FirstName
and LastName fields at least, which will give you more flexibility in
searches, sorting, and so forth. It is almost always the best choice to
use
an ID number for a name record.

Thanks for reading this.

[quoted text clipped - 17 lines]

What am I doing wrong?