We help IT Professionals succeed at work.

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

scbdpm
scbdpm asked
on
638 Views
Last Modified: 2011-10-19
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

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

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

=Forms.YourFormName.RecordsetClone.Recordcount

mx

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

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

Me.RecordsetClone.Recordcount

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Ok ... with the X of Y:

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

mx

Author

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

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
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!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

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

mx

Author

Commented:
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?

Author

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
What Group should I choose to get data?

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

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

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
try the first group DEF.

I am at work, not sure if we have SP3 on office... we are still on 2003......
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"we are still on 2003......"

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



DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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 ?

Author

Commented:
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?

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
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...
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
redesigned.... thanks for the lead!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.