By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,225 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,225 IT Pros & Developers. It's quick & easy.

date-limited query hides previously-selected items in combo box

WyvsEyeView
P: 46
I have a datasheet subform on which users select a topic from a combo box populated by a query, qryListTopicsCbo. This query limits the list to only those topics edited within the last n days (where n is supplied by a field on another form). The problem I am having is that if a user has selected a topic in the past but its last edit date now falls outside of the n range, that topic no longer appears on the form. Obviously, I want topics already selected to remain visible regardless of their last edit date. I only want the drop-down list for future selections to be limited by the date range. How can I accomplish both? Thanks for any help.
Oct 8 '08 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,707
What is your selection criteria for the items currently displayed on the form?
Oct 8 '08 #2

WyvsEyeView
P: 46
This is the selection criteria for items currently displayed:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProdDocTopics.DocTopID, tblProdDocTopics.DocID, tblProdDocTopics.TopicID, tblProdDocTopics.order
  2. FROM tblProdDocTopics
  3. WHERE (((tblProdDocTopics.DocID)=[Forms]![frmDocs]![nbrDocID]))
  4. ORDER BY tblProdDocTopics.order;
And this is the query that I want to use to initially populate the combo box based on last edit date:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblTopics.topID, tblTopics.descr, tblTopics.type, tblTopics.version, tblTopics.status, tblTopics.review, tblTopics.libDoc, tblDocs.descr, tblTopics.lastEdit
  2. FROM tblDocs INNER JOIN tblTopics ON tblDocs.docID=tblTopics.libDoc
  3. WHERE (((tblTopics.lastEdit)>Date()-Forms!frmDefaults!nbrDateLimit) And ((tblTopics.active)=True))
  4. ORDER BY tblTopics.descr;
The query I was using before, that did not filter the combo box based on last edit date, was:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblTopics.topID, tblTopics.descr, tblTopics.type, tblTopics.version, tblTopics.status, tblTopics.review, tblTopics.libDoc, tblDocs.descr
  2. FROM tblDocs INNER JOIN tblTopics ON tblDocs.docID = tblTopics.libDoc
  3. WHERE (((tblTopics.active)=True))
  4. ORDER BY tblTopics.descr;
Oct 8 '08 #3

NeoPa
Expert Mod 15k+
P: 31,707
Try instead, using :
Expand|Select|Wrap|Line Numbers
  1. SELECT tT.topID,
  2.        tT.descr,
  3.        tT.type,
  4.        tT.version,
  5.        tT.status,
  6.        tT.review,
  7.        tT.libDoc,
  8.        tD.descr,
  9.        tT.lastEdit
  10.  
  11. FROM tblDocs AS tD INNER JOIN tblTopics AS tT
  12.   ON tD.docID=tT.libDoc
  13.  
  14. WHERE (tT.lastEdit>DateAdd('d',-Forms!frmDefaults!nbrDateLimit,Date()))
  15.    OR (tT.active)
  16.  
  17. ORDER BY tT.descr
Notice the use of the OR instead of AND in your WHERE clause.

This would be for your ComboBox, but probably also for the form.
Oct 9 '08 #4

WyvsEyeView
P: 46
Thanks, NeoPa, but this query returns all the active topics from tblTopics, regardless of their last edit date. I just want the ones that are active *and* edited in the last 3 days or 5 days or whatever the user enters. So I think I do need an AND clause rather than an OR clause.
Oct 9 '08 #5

NeoPa
Expert Mod 15k+
P: 31,707
The problem I am having is that if a user has selected a topic in the past but its last edit date now falls outside of the n range, that topic no longer appears on the form. Obviously, I want topics already selected to remain visible regardless of their last edit date. I only want the drop-down list for future selections to be limited by the date range. How can I accomplish both? Thanks for any help.
It sounds like you're right. I was thinking this was related to your original request (quoted) and thought that might explain why your attempt wasn't working.

To be honest I'm a little confused now, trying to understand what your problem is. Your post #3 doesn't SEEM (unless I misunderstand something) to be a direct response to my question in post #2.
Oct 9 '08 #6

WyvsEyeView
P: 46
Okay...maybe I'm not explaining myself well or not understanding your question, so I'm going to be really explicit here (probably overdo it) and maybe somewhere in my rambling I'll answer what you're asking :)

Originally, I set up frmProdDocTopics (actually sfrmProdDocTopics) to display only the topics associated with the active document in tblProdDocTopics. The first query in my reply #3 is the RecordSource for sfrmProdDocTopics. The combo box was set to display all active topics from tblTopics. The third query in reply #3 was my original RowSource for the TopicID field on sfrmProdDocTopics. So then, after you selected a topic, obviously it would continue to appear in sfrmProdDocTopics as associated with the active document. That's exactly what was happening when I populated TopicID using the third query.

I needed a way to limit the list in the combo box because it could grow very long and hard to navigate and slow to load. A friend suggested limiting it by last edited date initially and then giving users a way to display the whole list if the topic they wanted to select wasn't in the initial filtered list. So then I changed the RowSource for TopicID to the second query in my reply #3.

The unexpected result was that this query seems to not only filter the combo box list but also to filter previously-selected records so that the previously-selected topic only appears if it meets the criteria in the new query. I expected it to filter the combo box only for new records, not for existing ones. This is where I'm stumped.

Does that explain my issue any better? Thanks!
Oct 9 '08 #7

NeoPa
Expert Mod 15k+
P: 31,707
Does that explain my issue any better? Thanks!
Probably :)

Unfortunately I'm wiped out tonight and will have to look at this in more detail tomorrow. Sorry for the extra delay.
Oct 9 '08 #8

NeoPa
Expert Mod 15k+
P: 31,707
Just a reminder post to help me find the thread when I get home tonight. I would hope I can post something helpful then.
Oct 10 '08 #9

NeoPa
Expert Mod 15k+
P: 31,707
It seems I was a little overoptimistic.

Sorry I haven't replied earlier, but I've struggled and struggled to make sense of this, but there is so much here that isn't related that I can't see where the question fits in.

Which query do you have a problem with?

Am I right in thinking that this query is displaying only those topics that fit in the date range, whereas you want it to include all previously selected topics AS WELL as those unselected ones which fall within the date range?

I feel sure with two simple answers to these two simple questions we will be in a position to sort this out.
Oct 12 '08 #10

WyvsEyeView
P: 46
Hi NeoPa...I talked this over with the friend who gave me the original suggestion and I think I have concluded that I need two forms; one form can't do what I want. So I think I will have one form on which I select the topic to associate with the document, and that form will be populated using the date-filtered query (the second one in my earlier post, I believe). My current form will no longer be used to select the topic, but will only display the topics already associated with the document, using the non-filtered query (the third one in my earlier post). I think your difficulty in understanding my original post was that I was attempting something fundamentally contradictory and you were trying to give me more credit for logic than I deserved. :)
Oct 13 '08 #11

NeoPa
Expert Mod 15k+
P: 31,707
I presume this means you're happy with this thread as far as it goes. I'm pleased :)

I suspect we could have solved the issue between us, but I also suspect your new approach is actually more sensible anyway.

PS. You'll have to explain the rationale of your handle sometime. It's interesting and different (Not have to obviously - I'm curious though).
Oct 13 '08 #12

WyvsEyeView
P: 46
I *am* happy with the solution. I think one form would have been slicker and one less click for users, but probably more trouble than it was worth to implement.

My handle comes from a nickname a friend gave me...Wyvern/Wyv, as in the dragon. And the EyeView part comes from my being a photographer...so I see the world through a Wyv's Eye View, you might say.

How about NeoPa? Care to share?
Oct 13 '08 #13

NeoPa
Expert Mod 15k+
P: 31,707
That must be from the South/West then (I'm guessing).

NeoPa. Sure.

My son learned gaming on his father's knee (literally) and when he was about 14/15 he was becoming a bit of a clan gamer (Very good). He called himself Neo (Matrix fan of course). When he got me to join him on a gaming server he was frequenting I decided being NeoPa was appropriate, and gave the clue that I was not a young, 1337 gamer, but a much less competent parent. That got me a little leeway. Gamers playing are not known for their patience ;)

I eventually became good enough to join, and eventually became an Admin of, the clan (Smoking Guns - A "Wolfenstein - Enemy Territory" Competition Clan), but in truth I was never the best gunfighter they had. I could think though, and die (many times) for the cause :)
Oct 13 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.