Continuous Form

[Reggie Laffond]

On a continuous form I would like to have a text field calculate the number
of records in a second table that match the key field. I have a one to many
relationship with entegrity and cascading deletes which works correctly. I
tried using the oncurrent event to run some code but it seems to fire just
once - not once per record. Since the value of the field which points at the
second table changes each record I can't figure how to manipulate with code
or the DLOOKUP function to get what I need.

I've been struggling with this problem which doesn't seem like it should be
that hard to accomplish.

Can someone point me in the right direction?

Access 2000 under Windows XP.

Thanks in advance!

 

[Sandra Daigle]

The Current event does fire after each record navigation so it should work -
can you post the code that was giving your problems? Also, you might want to
consider the Dcount function:

lngMyCount=DCount("MyPrimaryKey","MyTable","MyPrimaryKey=" & me.MyValue)

 

[Graham Mandeno]

Hi Reggie

Your textbox should have a ControlSource something like this:
=DCount("*", "OtherTable", "[Related Field]=" & [Key field])

Then there should be no need for any Form_Current code at all. At most you
might need:
MyTextBox.Requery

 

[Reggie Laffond]

DCount is perfect. I've use DLookup often but never DCount.

As always, you guys are great.

The Current event does fire after each record navigation so it should
work - can you post the code that was giving your problems? Also, you
might want to consider the Dcount function:

lngMyCount=DCount("MyPrimaryKey","MyTable","MyPrimaryKey=" & me.MyValue)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

On a continuous form I would like to have a text field calculate the
number of records in a second table that match the key field. I have
a one to many relationship with entegrity and cascading deletes which
works correctly. I tried using the oncurrent event to run some code
but it seems to fire just once - not once per record. Since the value
of the field which points at the second table changes each record I
can't figure how to manipulate with code or the DLOOKUP function to
get what I need.
I've been struggling with this problem which doesn't seem like it
should be that hard to accomplish.

Can someone point me in the right direction?

Access 2000 under Windows XP.

Thanks in advance!

 

[Reggie Laffond]

DCount is perfect. I've use DLookup often but never DCount.

As always, you guys are great.

Hi Reggie

Your textbox should have a ControlSource something like this:
=DCount("*", "OtherTable", "[Related Field]=" & [Key field])

Then there should be no need for any Form_Current code at all. At most
you might need:
MyTextBox.Requery

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

On a continuous form I would like to have a text field calculate the
number of records in a second table that match the key field. I have a
one to many relationship with entegrity and cascading deletes which works
correctly. I tried using the oncurrent event to run some code but it
seems to fire just once - not once per record. Since the value of the
field which points at the second table changes each record I can't figure
how to manipulate with code or the DLOOKUP function to get what I need.

I've been struggling with this problem which doesn't seem like it should
be that hard to accomplish.

Can someone point me in the right direction?

Access 2000 under Windows XP.

Thanks in advance!