Display Relative Record Value of Recordset 1

[WillYum]

Greets!

Okay, I feel really dumb for having to ask this question but not as dumb as I'd feel if I looked *another* 3 hours for the answer on the web and in these forums for what seems like a simple thing.

I have a Continuous Form. What I want to do is display the relative value of the record in the selected recordset (it is showing filtered results by a name). Basically, that same number that shows up in the Navigation Bar (in the center of the record navigation buttons), I want to put in an Unbound textbox.

--

That's basically my question, here is some background. I have two tables that are linked by the persons name, each person is supposed to have only 3 records in the Many Table (but they could have fewer than 3), I want it to automatically count it and hopefully I will be able to disable record additions if they already have 3 related records.

Thanks!

-- WillYum

- William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?

 

[BNPMike]

You surely don't need to display the record number. From what you say there can only be zero, one , two or three. You'd need a pretty sad user not to work that out visually without a number.

If you want to stop people adding a fourth entry then add a macro or module to one of the update events on the form. Use SQL to check that the number of records is less than 3 when someone attempts to do an insert.

 

[PHV]

In the header of footer section add a TextBox with ControlSource property set to:
=Count(*)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[WillYum]

BNPMike, I envy you for having such reliable users. Unfortunately, mine are not, especially since not all three records can fit on screen at the same time (they are large). Adding that number would be useful.

PHV, yes, for limiting the number of entries, that is definitely useful, any ideas on listing the relative record value, though?

Thanks for your help.

- William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?

 

[RoyVidar]

Hi!

A textcontrol with the controlsource of =Count(*) should provide a count, but in this case, whishing to disallow more than 3 entries, perhaps something like this in the forms on current should disallow more than three entries:

[tt]if me.currentrecord>3 then
msgbox "to many!"
docmd.gotorecord,,aclast
end if[/tt]

- typed not tested

Else, you could get the count by:

[tt]debug.print me.recordsetclone.recordcount[/tt]

Roy-Vidar

 

[PHV]

Provided the rows are sorted by myID, the form is named frmTable2 and the underlaying table is Table2, add a textbox in details section with ControlSource property set to:
=DCount("myID","Table2","myID<=Forms!frmTable2.myID")

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[WillYum]

Perfect -- I really have no idea how it works... But, it does exactly what I wanted. Greatly appreciated. (Now off to figure out how it works).

Thanks to everyone for their tips.

- William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?

 

[WillYum]

Just to follow up. Because the DCount above referenced the table, it gave the relative Record Value in the *table* not the recordset of the filtered form.

So I just created a query based on the Name that DCount could reference and removed the filter.

Worked like a charm. Thanks, Everyone.

- William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?