Hello John,
Thank you for the response. Here’s a brief overview. I track meters and their
readings for equipment (hours, miles etc). Each unit is allowed a maximum of
4 meters. The user can add, delete or replace any meter.
There are 2 tables;
tblUnits – unitID is the PK
tblRunningMeter – unitID and meterID together serve as the PK for the table.
tblUnits is on a one to many relationship with tblRunningMeter
I currently have 2 forms; one based on tblUnits and one based on
tblRunningMeter and I have the two synchronized – call them form 1 and form 2.
I’ve set up form 2 as an ‘eyes only’ form. Merely a form that displays info
about the various meters that may be on a piece of equipment. As a user
cycles thru equipment, the info changes on form 2 about its meters. Form 2
has 4 radio buttons, each representing a meter. If a unit has 3 meters for
example, only 3 radio buttons are enabled. Clicking a radio button shows info
about that meter. I used a dcount to determine the number of units. Here’s
the problem that came up; once a meter is set up as meter 2, I want to keep
it as meter 2. If it later gets deleted, I will re-assign it in code with
something like “00001 or 0001a†and move it to a historical table. If it gets
replaced, I will simply flag it as a replacement and the new meter taking its
place will still be meter 2, just unflagged…whew. Now, the problem. Since
I’ve used a dcount, I can only count. In other words, if I delete meter 2,
then the table shows meters 1,3 and 4 and radio buttons 1,2 and 3 are enabled.
I’m looking for a function (I think) that returns the meterIDs for a unit,
rather than a count, so that the true meterID is reflected.
John said:
[quoted text clipped - 3 lines]
meterFind will always be the first meter it finds (meter 1). Can this be
coded to increment (like a loop) in order to find all meters?
Eh? meterFind won't be a meter; it will be a count of all the non-NULL
values of the field MeterID in tblRunningMeter for which the UnitID is
equal to the value in the form control.
Could you explain the relationship between Meters and Units, and just
what you expect this expression to return? Might it not be simpler to
use a Recordset (returning multiple records) rather than a Domain
Function, which inherently returns only one value?
John W. Vinson[MVP]