dlookup

[vbnetman via AccessMonster.com]

In the following code;

meterFind = DCount("meterID", "tblRunningMeter", "unitID=" & Forms!frm_units!
UnitID)

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?

 

[John Vinson]

In the following code;

meterFind = DCount("meterID", "tblRunningMeter", "unitID=" & Forms!frm_units!
UnitID)

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]

 

[vbnetman via AccessMonster.com]

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.

In the following code;

[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]

 

[John Vinson]

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.

Thanks for the explanation. That wasn't at all clear before!

I think what you want is better handled by using a Recordset. If you
open a Recordset based on the meters table for a particular UnitID,
you can step through it identifying all the meters. (Maybe someday the
four-meter limit will change...!)

Try something like:

Dim rs As DAO.Recordset
Dim iMeter As Integer
Set rs = Me.RecordsetClone ' get the form's recordsource
rs.MoveFirst
Do Until rs.EOF
iMeter = rs!MeterID
<set the visibility appropriately given the known ID>
rs.MoveNext
Loop
rs.Close

John W. Vinson[MVP]

 

[vbnetman via AccessMonster.com]

John,
I'm going to need some time to look at this. I am better versed in VB.net and
am still stumbling with VB.
I will respond back.
Thank you for your valuable input