Re: Value from unbound form control

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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]


BruceM wrote:
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
BruceM wrote:

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)
.

Relevant Pages

  • Re: Value from unbound form control
    ... I understand what you're saying about the extra field for a preferred phone ... since each vendor may have several. ... the person generating the report. ... button's Click event that opens the report). ...
    (microsoft.public.access.reports)
  • Re: Value from unbound form control
    ... PhoneNumber since each vendor may have several. ... DoCmd.OpenReport "rptVendorInfo", acPreview,, strLinkCriteria ... of the person generating the report. ... intended (in the command button's Click event that opens the report). ...
    (microsoft.public.access.reports)
  • Re: Clarification: Re: 6 Tables, 1 Report, W/O 6 Qrys
    ... Make a form with three or four unbound text boxes on it. ... Next, make a new report. ... then finally it opens the report which in turn pulls the ... >> each type of vendor) and you wanted to get by with only ...
    (microsoft.public.access.reports)
  • Regarding excellent web hosting by Defined.net
    ... we are happy to report that we only have ... Defined is located in Southern California where we originally did business ... Defined was acquired by new a new owner, Chuck, a while back, and we now ... anyone who is not getting a response from a vendor to examine the ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... >> It should not matter HOW my implementation generates the report. ... >is more likely to experience problems over a broader scope. ... Clear out 'john' and press enter. ... with no need to contact the vendor. ...
    (comp.object)