User Profile

radio1 · Mar 9 '07, 06:32 PM

Thank you (yet again, referring to previous occasions in another thread) Mary, for your input. Sorry for my delay in replying, I suspect my spam filter may be preventing me from getting thread-update notification emails.

I completely understand and agree with what you're saying, and in fact I actually have both screens you describe:

(A)- a main 'subjects' form with a 'studies/study' subform
(B) a main 'study' form with a 'studies/subjects'

...

radio1 · Mar 9 '07, 05:58 PM

Thanks much for the input, Rux!

Sorry for my delay in replying, I suspect my spam filter may be preventing me from getting thread-update notification emails.

Three things:

1- I'm going to give your updated timer code a whirl. While I haven't had any run-time problems due to my implementation, the timer code seems to cause me some curious cursor/typing behaviour at dev time within the VB code editor when...

radio1 · Jan 17 '07, 08:26 PM

Thank you again, NeoPa for all of your input!

I have *finally* coded a (kludgy) workaround(s) to this problem, and I'll describe it here for posterity.

Again, an important aspect of these two display-only fields was that they be user-filterable via Access's standard filtering mechanisms.

Here's what I did:

1- referring to my initial post, I eliminated all code from the SECOND code snippet,...

radio1 · Jan 14 '07, 02:42 PM

I think you're essentially right, NeoPa, in the case of the GROUP BY clause -- its presence seems to be what's making Access treat all the columns in the view as non-updatable.

I've now even tried replacing my subview 'viewSubjectsSt udySumm' with a Table-Value User-Defined Function with the hope that it may result in "hiding" that GROUP BY clause from Access, but this still did not resolve the problem.

In general,...

radio1 · Jan 14 '07, 01:28 AM

Hi NeoPa -- thanks for staying with the thread.

If you look at the second snippet I posted showing the two Views ('viewSubjectsM ain' and the subview 'viewSubjectsSt udySumm'), you'll see I essentially have a query on 'subjects', then (left) joined on 'studies', then joined on 'study'.
The GROUP BY clause is on a field in 'studies', not on 'subjects', therefore all fields returned from 'viewSubjectsMa in' that originated from 'subjects'...

radio1 · Jan 12 '07, 11:06 PM

Thanks for your input Steve. Please read my reply to NeoPa above for some new thoughts and information.

Your input gave me more things to Google, and I found some things of interest in what appears to be the document you found your posted snippet in: http://p2p.wrox.com/topic.asp?TOPIC_ID=22741

I'll try to pursue the function angle and/or the INSTEAD OF angle mentioned in the link.

Thanks again -- I'll...

radio1 · Jan 12 '07, 11:00 PM

Thanks, NeoPa, for the reply and the kind words about my post. I did put in considerable time and effort to be as clear as possible!

I understand and agree with everything you said.

In my particular case, though, the "main" query returns uniquely keyed records, and the GROUP BY only applies to the JOIN'd view which effectively results in adding 2 calculated columns to the rows of the main view. Since there...

radio1 · Nov 9 '06, 10:47 PM

It's not a GOOD solution, but I have implemented something that will work in my application almost all of the time. I ended up doing a very-crude conversion of the WHERE clause from Jet's syntax to T-SQL's syntax.

This code (mostly) works for the data types I'm using. It should fail if the user enters a filter including the # or " characters (possible), or any of the other strings in the Replace function calls (much less likely)....

radio1 · Nov 1 '06, 10:02 PM

Re:

Thanks for the suggestion, mmccarthy. I've been playing with it, hoping to be able to loop through the form's filtered recordset and add the records to a temporary table, and then report from that table (I don't know if this would have worked anyway -- I didn't get that far).
For me, the following TEST-ONLY code in my button click handler displays the # of records in the original recordset, not the user-filtered...

radio1 · Oct 28 '06, 10:34 PM

Thanks very much for the reply, mmccarthy, but (and please forgive me if I'm incorrect!) I don't think your suggestion addresses my core problem.

In my ADP project, I'm trying to Report the data that is current in a Form -- by current I mean including any field/form filtering the user has done.

The code example I've shown tries to determine what the current user filters are via the Me.Filter variable from the Form....

radio1 · Oct 28 '06, 04:12 AM

You're welcome.

Please note that there are possibly "bigger/better/stronger/faster" ways of doing it -- I'm still quite new at Access and VBA coding. You just happened to ask the one question in the entire forum that I was apparently qualified to answer!

Cheers,

-radio1

radio1 · Oct 27 '06, 09:23 PM

The following works for me (in Access 2002) -- my Date-Of-Birth field is called "dob". This formula prevents "error" strings from appearing in the calculated age field when the dob field hasn't yet been entered or is invalid.

Code:

=IIf(Nz([dob],"")="","",DateDiff("yyyy",[dob],Now())+(Now()<DateSerial(Year(Now()),Month([dob]),Day([dob]))))

Hope...

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

(ADP) form problem: "This Recordset is not updatable"

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Report the current filtered records from a Form (in an ADP project)