DoCmd.OpenForm with a filter AND a where condition? (1 Viewer)

I have a form that allows a user to view every record in the database (one at a time) via a combobox. The combobox displays a 'recordID' which is not the primary key and can (will) therefore have duplicates. The 'recordID' is reset back to 1 every calendar year.

There are 3 different ways to load the 'View Everything' form. One is via command button which opens the form to the most recent record. Another is to double click a listbox entry to open the form to the selected record. And finally, clicking the previously mentioned command button with an item selected in the listbox will open the form to that record.

Regardless of which way you enter the form, the combobox on the form will let you move around to different records.

What I'd like to do on the 'View Everything' form is filter the results of the combobox by year, with it defaulting to the year of the record that was selected or to the current year if the form was opened without a record selected. I'd also like to provide the user with a way to change which year is available via the combobox - presumably with another combobox.

My current code:
with a selection (command button with something selected and double clicking use the same code)
and without a selection
[HiddenID] is the primary key and [EmailID] is the visible 'recordID' I mentioned earlier.

So, my initial thoughts to accomplish the first goal of filtering the available records to a single year were to add a filter to the above code. But when I look at the properties sheet of the form, my Where condition from the code above is being used the filter. I think I may be a little confused about the difference between a filter and a Where condition when it comes to the property sheet display.

Is something like
or
even possible?

Would that even be the right way to go about this?

Some other thoughts I've tossed around:
-Creation of a recordset on form load to restrict what's available, but I have no experience with recordsets and I'm not even sure if that's their intended purpose.
-Adding code to the combobox to restrict what is listed in it.

I don't know if either of those methods are viable or how I'd implement them, but I'm just brainstorming right now.

Hopefully I've articulated my goals and the landscape within which I'm working well enough. Any thoughts or ideas would be most appreciated.

Well, I tried
and the combobox is still displaying EmailID's from previous years, so it looks like this method is a bust.

Anyone have any ideas?

Foe said:

What I'd like to do on the 'View Everything' form is filter the results of the combobox by year, with it defaulting to the year of the record that was selected or to the current year if the form was opened without a record selected. I'd also like to provide the user with a way to change which year is available via the combobox - presumably with another combobox.

Click to expand...

I hope I'm reading you correctly that you actually have one Form, but three (or whatever) ways to open that form in different modes. Otherwise, you need to back up a step and simplify your life.

It took me a while to understand WHERE vs. Filter, too... best way I can think to explain it is that WHERE alters the recordsource for the Form, so it's everafter limited to the criteria you specified unless you reload the form entirely. Whereas with Filter you can turn it On and Off, change the criteria, etc. Over the years I've gotten away from WHERE for forms and gone to a Filter methodology.

So, with all of that said, I think I have an idea for you:

1. And a second/auxillary combobox for your program years. Default Value is probably Year(Now()), but this is all vaporware so adjust accordingly - you mentioned that sometimes you'llwant to set it to Year([ReceivedDate]) for example. Rowsource for this can be a GROUP BY query of your entire table, but that gets slow, so you may find it easier to just have a tiny lookup table with 2009, 2010, 2011, 2012, 2013, etc. Then just add a value for 2014 when the new year comes, or have your DB do it on January 1 via VBA code.
2. Your 'search' combobox's rowsource should have a criterion that looks at this new combobox to consider what values to show. If you don't know how already, look up Cascading Combo Boxes.
3. The code that triggers after your searchbox should probably start using Filter instead of the WHERE clause. For example here's what one of mine uses currently, which allows me to switch it later pretty much at will.
   
   Code:Copy to clipboard

       If searchStr <> "" Then 'there's something to search on, hooray!
           Forms![ScoringSheet].Form.FilterOn = False
           Forms![ScoringSheet].Form.Filter = searchStr
           Forms![ScoringSheet].Form.FilterOn = True
       Else
           MsgBox "You must enter some criteria to search by.", vbExclamation, "Error: No search criteria specified."
           DoCmd.Close acForm, Me.Name
       End If

   keep in mind that code is being run from a popup form, not a searchbox, but the principles should be the same.

David R said:

I hope I'm reading you correctly that you actually have one Form, but three (or whatever) ways to open that form in different modes. Otherwise, you need to back up a step and simplify your life.

Click to expand...

Correct: One form with three ways to open it.

Regarding the rest of your reply, I'll be honest. I couldn't follow it.

However, I've made progress. What I've done is create two queries. The first one grabs all the ReceivedDate entries from my table and turns them into year(ReceivedDate). The second query runs against the first one to remove duplicates leaving me with a list of each year preset in the original table.

I created a second combobox (cboSelectYear) with the second query as it's Row Source. This gives me a combobox with a list of years available to sort by.

In the Row Source for the original combobox (cboSelectEmail) I added this:

I set cboSelectEmail to only display column 2 and it now only presents a list of EmailIDs from the year selected in cboSelectYear.

I feel as if the two query method to populate cboSelectYear isn't exactly elegant, but it works.

Next step is to add code for cboSelectEmail to update itself after a selection is made in cboSelectYear and to have the form display the most recent record from the selected year.

well, that last bit was easier than I expected it to be.


Looks like I've got this one licked...