Link to home
Start Free TrialLog in
Avatar of scbdpm
scbdpmFlag for United States of America

asked on

Getting distinct when using "Set rs = Me.RecordsetClone"

I have the following code in the form's 'Form_Current" event to display the number of records in a text box.

is there a way to use this same code, modified, for distinct?

Background: the form is actually a subform (subGroupIntervention) and on it is a subform (subGroupIntervention_Tool).

There's a one-to-many relationship between the Intervention and Tool (henc why  the above form to subform is used).

My problem: when the code snippet below runs, if there is one intervention and three tools, the recordcount is three, not one. I want it to show for only one intervention...

BTW, the main form (frmGroupMain) filters for the GroupID so that you only seen Intervnetions for that Group.
Set rs = Me.RecordsetClone
rs.MoveLast
Me.txtRecCount = Me.CurrentRecord & " of " & rs.RecordCount

Open in new window

Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

All you need is this in a text box - no code required

=Forms.YourFormName.RecordsetClone.Recordcount

mx

Or, in code you could set the value of an unbound text box for display:

Me.RecordsetClone.Recordcount

mx
Ok ... with the X of Y:

Me.txtRecCount = Me.CurrentRecord & " of " & Me.RecordsetClone.Recordcount

mx
Avatar of scbdpm

ASKER

thanks, I already have that working.... my problem is a bit more unique than just showing recordcount..... please re-read posting.....

Change the form's query to include / exclude the records you want, including the DISTINCT predicate.

Also ... even though you have it working, no need to do the Move Last ...

mx
Avatar of scbdpm

ASKER

mx;

I think perhaps this is more complex than what I explained above.

The query already is including only records for that group....
To take it one step further, I have record 'movement' buttons: move next, more previous, etc.

I am having an issue when I open the form (with subform : subGroupIntervention on the form and the subform subGroupIntervention_Tool on subGroupIntervention) it.

Lets say there is one Intervention and three Tools (one to three relationship??). The subform (subGroupIntervention) is displaying three records (which I dont want). However in testing further, using the next record button takes me to the second record (it looks same as first) and then third one (also same). These three appear the same because it is showing the three records&..

Help!
Sorry, but I guess I'm not seeing why you can't handle that in the query for the subform - using GroupBy ?

mx
Avatar of scbdpm

ASKER

currently the query that feeds the subform has no criteria. I never set it up.... but what I see is correct. This must be because the 'filtering' is done via the 'link' between paren and child (correct?)......

I was playing around with the query but can't seem to make it work.... my fields on the query are GroupID; InterventionID and ToolID. what would I group by?
Avatar of scbdpm

ASKER

Ok, the issue is with the Intervention/Tool tab
 You will see that in the 'record count' textbox; there are looks like there are three records. And, if you use the 'more next record' button, you don't get an error but it appears like nothing is happening.
In fact, you are just moving through those three records (one Intervention to three tools).....



EE-Outpatient-ContactLog.zip
What Group should I choose to get data?

mx
btw ... if I try now try to open form frmNewGroupEntry in Design view, I get the Access must close message!

mx
I meant frmGroupDataEntry ... and nevermind ... I did a Compact & Repair and that went away.

Just curious ... do you have Office Service Pak 3 on your system. If so, you should immediately uninstall Office and re-install - to get rid of that service pak.  I was having this issue with a client - when ever she sent me an mdb from her system.

mx
Avatar of scbdpm

ASKER

try the first group DEF.

I am at work, not sure if we have SP3 on office... we are still on 2003......
"we are still on 2003......"

Yes ... SP3 for Office 2003 is what I meant.



ok back to the problem.

Both of these subforms:

subGroupIntervention_Tools and subGroupTools_4Intervention  have the same Recordsource - qryGroupIntervention_Tool.

That doesn't really make sense to me.  And ... in the tblGroupIntervention_Tool table, all three records have the same Data, except for the ToolID. So, when you navigate, you will of course see the same record.  Now, I guess that is what you are seeing?  Are you SURE that the recordsource for subGroupIntervention_Tools  should be qryGroupIntervention_Tool ??

mx

If you change the Link Child/Master Fields on subform control 'subGroupTools_4Intervention' from

GroupID;InterventionID
to

GroupID;ToolID

you will get different results when you navigate, but I'm  not sure if that is the intended result ?

Avatar of scbdpm

ASKER

Ok, looking at your two postings:

#1) Q: "SURE that the recordsource for subGroupIntervention_Tools  should be qryGroupIntervention_Tool ??".
Admittedly, no.... not sure. see #2 for possible explanation of how the db should be designed (and which I think I failed to do).

#2) You are correct,
"If you change the Link Child/Master Fields on subform control 'subGroupTools_4Intervention' from
you will get different results when you navigate, but I'm  not sure if that is the intended result ?"
that is not what I am looking for.
What I am looking for is similiar to the Topic Measure/intervention tab. On the intervention/tool tab, there may be several tools for each intervention. Basically, I'd like to use the 'navigate' buttons to go from one Intervention to the next and then the subform woudl display the tools that are associatioed with that intervention. does that make sense?

Well, I think we have identified the issues here.  You need to modify your design to make this work.  In general, each subform should have a record source that is connected to a 'related' table.

mx
Avatar of scbdpm

ASKER

and I thought I did....

because the different sections are similar, I did a copy of the table/query/form and just changed for each...

being that I am under a time crunch, I likley didn't think this through...
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of scbdpm

ASKER

redesigned.... thanks for the lead!