Value from unbound form control

[BruceM]

I have a table for Vendor information, with a related table for PhoneNumber
since each vendor may have several. The PK in tblVendor and the FK in
tblPhone are both named VendorID. They are both Number fields (autonumber
in tblVendor). The vendor information appears on a form (frmVendor) that is
bound to tblVendor. The Phone information appears in an unbound list box
(lstPhone) that gets its data from the Current event for frmVendor:
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone WHERE VendorID = " &
Me.VendorID
By the way, I have seen this sort of code with:
& ""
at the end of it (I think that's how it went). I can't see that it makes
any difference, but if it does I would like to know.
Anyhow, this works fine. The problem occurs when I try to get the phone
number into a report I open from the form. There is a command button with
the following as its Click event:

Dim strLinkCriteria As String

strLinkCriteria = "VendorID = " & Me.VendorID
Me.Dirty = False
DoCmd.OpenReport "rptVendorInfo", acPreview, , strLinkCriteria

The rptVendorInfo has this as its Open event:

DoCmd.OpenForm "frmSender", , , , , acDialog

frmSender is an unbound form that accepts the name, e-mail address, etc. of
the person generating the report (the report is a fax to the vendor). A
command button on it sets the form's Visible property to False, and
rptVendorInfo opens with the appropriate information about the sender along
with selected fields from the vendor's record. However, I can't figure out
how to add the phone number (from the related table) to the report. I tried
an unbound text box on the report with its control source set to
Forms!frmVendor!lstPhone, but that didn't work (invalid use of Null error
message). I also tried adding tblPhone to the report's record source, but
there was ambiguity about which VendorID field was intended (in the command
button's Click event that opens the report). More about that in a moment.
It also occurred to me that something like the list box row source SQL could
work for the phone number text box on the report, but I couldn't figure out
how that works. My guess is that it is the best solution, but I can't
figure out how to implement it.
I should probably add that the phone number appears in the list box in the
order the phone numbers were added (i.e. the first phone number added for a
vendor has the lowest PhoneID (PK) number. I will change that to a ranking
system if needed, so that the top-ranked number appears first in the list
box. Or maybe there will be a check box for the main number, or something
like that. For now I am satisfied with either the first number in the list
box appearing on the report, or the currently-selected phone number on the
form appearing as the phone number in the report.
Back to an earlier point, I have gotten into the habit of giving the PK and
FK fields the same name when possible, on the theory that it simplifies
things in terms of what I need to remember about which field is supposed to
relate to which, but I have been questioning that practice. I wonder if it
would be better to, in this case, name VendorID in tblPhone something like
VendorIDch (for Child). That may make it simpler to code things like the
situation I have described. I would be interested in hearing thoughts on
this topic.

 

[Marshall Barton]

I have a table for Vendor information, with a related table for PhoneNumber
since each vendor may have several. The PK in tblVendor and the FK in
tblPhone are both named VendorID. They are both Number fields (autonumber
in tblVendor). The vendor information appears on a form (frmVendor) that is
bound to tblVendor. The Phone information appears in an unbound list box
(lstPhone) that gets its data from the Current event for frmVendor:
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone WHERE VendorID = " &
Me.VendorID
By the way, I have seen this sort of code with:
& ""
at the end of it (I think that's how it went). I can't see that it makes
any difference, but if it does I would like to know.
Anyhow, this works fine. The problem occurs when I try to get the phone
number into a report I open from the form. There is a command button with
the following as its Click event:

Dim strLinkCriteria As String

strLinkCriteria = "VendorID = " & Me.VendorID
Me.Dirty = False
DoCmd.OpenReport "rptVendorInfo", acPreview, , strLinkCriteria

The rptVendorInfo has this as its Open event:

DoCmd.OpenForm "frmSender", , , , , acDialog

frmSender is an unbound form that accepts the name, e-mail address, etc. of
the person generating the report (the report is a fax to the vendor). A
command button on it sets the form's Visible property to False, and
rptVendorInfo opens with the appropriate information about the sender along
with selected fields from the vendor's record. However, I can't figure out
how to add the phone number (from the related table) to the report. I tried
an unbound text box on the report with its control source set to
Forms!frmVendor!lstPhone, but that didn't work (invalid use of Null error
message). I also tried adding tblPhone to the report's record source, but
there was ambiguity about which VendorID field was intended (in the command
button's Click event that opens the report). More about that in a moment.
It also occurred to me that something like the list box row source SQL could
work for the phone number text box on the report, but I couldn't figure out
how that works. My guess is that it is the best solution, but I can't
figure out how to implement it.
I should probably add that the phone number appears in the list box in the
order the phone numbers were added (i.e. the first phone number added for a
vendor has the lowest PhoneID (PK) number. I will change that to a ranking
system if needed, so that the top-ranked number appears first in the list
box. Or maybe there will be a check box for the main number, or something
like that. For now I am satisfied with either the first number in the list
box appearing on the report, or the currently-selected phone number on the
form appearing as the phone number in the report.
Back to an earlier point, I have gotten into the habit of giving the PK and
FK fields the same name when possible, on the theory that it simplifies
things in terms of what I need to remember about which field is supposed to
relate to which, but I have been questioning that practice. I wonder if it
would be better to, in this case, name VendorID in tblPhone something like
VendorIDch (for Child). That may make it simpler to code things like the
situation I have described. I would be interested in hearing thoughts on
this topic.

Since you do not want all of a vendor's phone numbers in the
report, you will defintely need a way to identify which
phone number is the preferred phone. Note that this must be
done using a field in the phones table, there is no such
thing as a first record in a table.

Given that you take care of that, you can use a simple
DLookup in a text box's expression:

=DLookup("Phone", " tblPhone", "VendorID = " & Me.VendorID
& " And PrefPhone = True)

 

[Jeff Boyce]

If the query your report depends on contains both tables with a "VendorID"
field, Access can't resolve the ambiguity without your help. This may be as
simple as changing your reference to something like:

[tblPhone]![VendorID]

or

[tblVendor]![VendorID],

so Access knows which one to use.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

Thanks for the reply. I should have explained that the autonumber PhoneID
field is lowest for the first number entered, and that ordering by that
field would be acceptable. That's what I meant by the first record. I
imagined that the criteria would be something using
DMin("PhoneID","tblPhone"), but I don't know how to implement that, assuming
it would work at all.
I understand what you're saying about the extra field for a preferred phone
number. I had thought about that, but since most vendors have just a single
phone number I would rather not require it in those cases. Also, I would
need to prevent more than one subform record from being marked as the
preferred phone number. However, in order to use such a field, the simplest
from the user's point of view might be for the first number entered to be
marked automatically as the preferred number, and any numbers after that
with that field set to False. I could probably do that using If
Me.NewRecord and looking at the record count, but if the user were to select
another record as the preferred phone number, I would want the Preferred
field to be set to No in the record that was formerly marked. That second
part is what keeps me from attempting it, although I'm not sure it's
important enough to spend more time on in any case. I'm really just trying
to develop a technique that can be used in other similar situations, but
there is some need to get this project into workable shape pretty soon.

 

[BruceM]

Thanks for the reply. Maybe I was over-complicating it. I was trying to
use [Tables]![tblPhone]![VendorID].

By the way, I think I'll stick with the current naming convention. If I
modify the field name as I proposed in my original posting, I suppose I
would need to come up with a new name for the child field in every related
table. I think referencing the table is simpler in the long run.

If the query your report depends on contains both tables with a "VendorID"
field, Access can't resolve the ambiguity without your help. This may be
as simple as changing your reference to something like:

[tblPhone]![VendorID]

or

[tblVendor]![VendorID],

so Access knows which one to use.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table for Vendor information, with a related table for
PhoneNumber since each vendor may have several. The PK in tblVendor and
the FK in tblPhone are both named VendorID. They are both Number fields
(autonumber in tblVendor). The vendor information appears on a form
(frmVendor) that is bound to tblVendor. The Phone information appears in
an unbound list box (lstPhone) that gets its data from the Current event
for frmVendor:
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone WHERE VendorID = " &
Me.VendorID
By the way, I have seen this sort of code with:
& ""
at the end of it (I think that's how it went). I can't see that it makes
any difference, but if it does I would like to know.
Anyhow, this works fine. The problem occurs when I try to get the phone
number into a report I open from the form. There is a command button
with the following as its Click event:

Dim strLinkCriteria As String

strLinkCriteria = "VendorID = " & Me.VendorID
Me.Dirty = False
DoCmd.OpenReport "rptVendorInfo", acPreview, , strLinkCriteria

The rptVendorInfo has this as its Open event:

DoCmd.OpenForm "frmSender", , , , , acDialog

frmSender is an unbound form that accepts the name, e-mail address, etc.
of the person generating the report (the report is a fax to the vendor).
A command button on it sets the form's Visible property to False, and
rptVendorInfo opens with the appropriate information about the sender
along with selected fields from the vendor's record. However, I can't
figure out how to add the phone number (from the related table) to the
report. I tried an unbound text box on the report with its control
source set to Forms!frmVendor!lstPhone, but that didn't work (invalid use
of Null error message). I also tried adding tblPhone to the report's
record source, but there was ambiguity about which VendorID field was
intended (in the command button's Click event that opens the report).
More about that in a moment. It also occurred to me that something like
the list box row source SQL could work for the phone number text box on
the report, but I couldn't figure out how that works. My guess is that
it is the best solution, but I can't figure out how to implement it.
I should probably add that the phone number appears in the list box in
the order the phone numbers were added (i.e. the first phone number added
for a vendor has the lowest PhoneID (PK) number. I will change that to a
ranking system if needed, so that the top-ranked number appears first in
the list box. Or maybe there will be a check box for the main number, or
something like that. For now I am satisfied with either the first number
in the list box appearing on the report, or the currently-selected phone
number on the form appearing as the phone number in the report.
Back to an earlier point, I have gotten into the habit of giving the PK
and FK fields the same name when possible, on the theory that it
simplifies things in terms of what I need to remember about which field
is supposed to relate to which, but I have been questioning that
practice. I wonder if it would be better to, in this case, name VendorID
in tblPhone something like VendorIDch (for Child). That may make it
simpler to code things like the situation I have described. I would be
interested in hearing thoughts on this topic.

 

[Marshall Barton]

The Dlookup gets messier to retrieve a value from the record
with the smallest value in another field. I think this will
be close:

=DLookup("Phone", "tblPhone", "PhoneID = " & DMIN("PhoneID",
"tblPhone", "VendorID = " & Me.VendorID) )

You shouldn't rely on an autonumer value being in ascending,
or any other order. The only guarantee of an autonumber
primary key is that it is unique.

 

[BruceM]

I've been away for the holiday weekend, which is why I haven't acknowledged
your reply before now. Thanks again for your input. However, I must report
that I cannot get it to work, but I have discovered that if I order the
records in tblPhone by PhoneID then only the first value (the one with the
lowest PhoneID number) appears on the report. That's all I need on this
project. I know that records in a table are not in any particular order.
If I order by PhoneID I will have a listing of records in the order they
were created. I also know that autonumber is not reliable for this usage,
so I will add something like a date stamp or a check box to select the main
phone number (if I can figure out how to make sure that only one number is
selected as the main one), but for now it does what I need, and I must move
on to other parts of the project.
The problem was that I wanted to open a report based on a record in
tblVendor (and its related record(s) in tblPhone). The idea was that I
would click a command button on a form based on tblVendor, and open the
report based on the current record. If I attempted something like your DMin
suggestion for the Phone text box on the report I saw #ERROR in the unbound
text box in which I wanted Phone to appear. Then I tried adding tblPhone to
the record source for the report. If I included VendorID from tblPhone (as
well as from tblVendor) Access couldn't tell which one I wanted; however, I
could not find a way to specify the record source. I tried "VendorID =" &
Tables!tblVendor!VendorID" and every other permutation I could think of, but
I never did find a way to resolve the ambiguity. Finally I just bound the
text box to the Phone field, without attempting to be specific about which
of the phone records is to appear in the report. As I mentioned, that was
what finally worked.
The especially frustrating part about this is that I got the phone number to
appear in an unbound list box on the form by using the form's Current event
to specify the SQL for the unbound list box. However, I could not discover
a way to do this to get the phone number into the report.

The Dlookup gets messier to retrieve a value from the record
with the smallest value in another field. I think this will
be close:

=DLookup("Phone", "tblPhone", "PhoneID = " & DMIN("PhoneID",
"tblPhone", "VendorID = " & Me.VendorID) )

You shouldn't rely on an autonumer value being in ascending,
or any other order. The only guarantee of an autonumber
primary key is that it is unique.
--
Marsh
MVP [MS Access]

Thanks for the reply. I should have explained that the autonumber PhoneID
field is lowest for the first number entered, and that ordering by that
field would be acceptable. That's what I meant by the first record. I
imagined that the criteria would be something using
DMin("PhoneID","tblPhone"), but I don't know how to implement that,
assuming
it would work at all.
I understand what you're saying about the extra field for a preferred
phone
number. I had thought about that, but since most vendors have just a
single
phone number I would rather not require it in those cases. Also, I would
need to prevent more than one subform record from being marked as the
preferred phone number. However, in order to use such a field, the
simplest
from the user's point of view might be for the first number entered to be
marked automatically as the preferred number, and any numbers after that
with that field set to False. I could probably do that using If
Me.NewRecord and looking at the record count, but if the user were to
select
another record as the preferred phone number, I would want the Preferred
field to be set to No in the record that was formerly marked. That second
part is what keeps me from attempting it, although I'm not sure it's
important enough to spend more time on in any case. I'm really just
trying
to develop a technique that can be used in other similar situations, but
there is some need to get this project into workable shape pretty soon.

"Marshall Barton" wrote

 

[Marshall Barton]

I've been away for the holiday weekend, which is why I haven't acknowledged
your reply before now. Thanks again for your input. However, I must report
that I cannot get it to work, but I have discovered that if I order the
records in tblPhone by PhoneID then only the first value (the one with the
lowest PhoneID number) appears on the report. That's all I need on this
project. I know that records in a table are not in any particular order.
If I order by PhoneID I will have a listing of records in the order they
were created. I also know that autonumber is not reliable for this usage,
so I will add something like a date stamp or a check box to select the main
phone number (if I can figure out how to make sure that only one number is
selected as the main one), but for now it does what I need, and I must move
on to other parts of the project.
The problem was that I wanted to open a report based on a record in
tblVendor (and its related record(s) in tblPhone). The idea was that I
would click a command button on a form based on tblVendor, and open the
report based on the current record. If I attempted something like your DMin
suggestion for the Phone text box on the report I saw #ERROR in the unbound
text box in which I wanted Phone to appear. Then I tried adding tblPhone to
the record source for the report. If I included VendorID from tblPhone (as
well as from tblVendor) Access couldn't tell which one I wanted; however, I
could not find a way to specify the record source. I tried "VendorID =" &
Tables!tblVendor!VendorID" and every other permutation I could think of, but
I never did find a way to resolve the ambiguity. Finally I just bound the
text box to the Phone field, without attempting to be specific about which
of the phone records is to appear in the report. As I mentioned, that was
what finally worked.
The especially frustrating part about this is that I got the phone number to
appear in an unbound list box on the form by using the form's Current event
to specify the SQL for the unbound list box. However, I could not discover
a way to do this to get the phone number into the report.

While the messy DLookup can be made to work, it is far less
than an ideal solution. The Yes/No field idea is the better
approach.

In the meantime, at least you have something that allows you
to move on for now. When you get back to this issue and if
you need further assistance, post back and we'll see what we
can come up with.