Record source from report based on nested querys?

bignose2

Registered User.
Local time
Today, 05:20
Joined
May 2, 2010
Messages
251
HI,

difficult to explain but will have a go.

I have a report that is based on nested (I think thats the phrase) query's.
Complicated Query based on another query (so I can't see a way to get at the the source SQL to change or use elsewhere)

This gives a list of say 20 records I generally want printed. I use the exact same query criteria with a separate update query to add the same to a table.

However I then wanted to just pick one with exact matching ID's I select on a form.

I could not see an easy way to apply this without making another set of nested querys which seems a little excessive

Anyway, an easy way for the printed report to do this is a simple filter added after, works great.
I can't see a way to do the same for an update query.
I was wondering if I could get the record source of this report and add to my table. I have tried with
' Dim db As DAO.Database
' Dim rs As DAO.Recordset

'Set db = CurrentDb

'Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset)

' Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
and dozens of variations over some hours but a variety of errors
mainly "too few parameters."
Not sure where this would take me anyway but not getting far.

I know very vague.
Thanks I/A
 
Try entering the full name of the recordsource

Set rs = db.OpenRecordset("NameofRecordSource", dbOpenDynaset)

"too few parameters" may indicate that you didn't select a valid object that exists within your Currentdb

No ' before the word Set

Cheers!
Goh
 
Thanks for that,

It is not a table Object etc that I am trying to get the source of so I don't have the full name.

I have a report based on nested query's so I can't find a way to get the SQL

To make matters worse I ONLY SOMETIMES want to open the report with a further filter using:

DoCmd.OpenReport stDocName, acPreview, , "ID = " & Me!MMMRegID
So this uses the underlying nest query's & ADDS the ID Filter above. (this is not a unique ID) there may me a number of results.

This is easy & great for the report but I would now like to use the same FULL criteria for a update query to add to a table.

It may be a bit programming in reverse but I can't see a way to ADD a simple filter ON TOP of the nested querys and doing it by further filtering the Report is easy.

Is there a way to get the source of the resulting records that are now in this report.



I do remove the ' for testing, it was only I copied direct from my code & rem'd them out as it was not working
 
Whether a record source is a table or complex query, it represents some fields and some values. There are conditions with the query which could make it non updateable. see this from Allen Browne.

The reality though is that you must determine your requirements in detail if you are to develop some automation support. You know your needs better than any reader. You have to breakdown the SOMETIMES into specific requirements.

If you can't describe or define it, then nobody can build it.
Good luck.
 

Users who are viewing this thread

Back
Top Bottom