Split Form with Datasheet on bottom

[data808]

I wanted to have two text boxes. Unbound. One box is for start date and the other for end date. I then have two buttons. One to filter and one to undo filter. Does anyone know how to make this work? The date is actually just the year. So no month or day in it. I using it for a movie data base and would just like to search from this year to this year kind of thing.



Thanks for the help.

[Dal Jeanis]

There's a bunch of different ways you could make this work. Here's one.

Add two unbound text boxes, but put two more invisible unbound text boxes on the sheet.

(txtFirstHidden) (txtFirst) (txtLast) (txtLastHidden)

When you load the form, or when you press the "undo filter", you set txtFirstHidden to 0000 and txtLastHidden to 9999 and requery the (sub)form.

When you press the "Set filter" button, you set txtFirstHidden to (A) 0000 if txtFirst is empty or non-numeric, or (B) equal to txtFirst. You set txtLastHidden to (A) 9999 if txtLast is empty or non-numeric, or (B) equal to txtLast . Then you requery the (sub)form.

The (sub)form is always filtered, but it is effectively unfiltered when the hidden values are 0000 and 9999.

There are lots of workable ways, but that one is simple enough. Make sense?

By the way, when I say "you do that", I mean you put VBA code behind the button that does those things in order. If you don't know how to do that yet, then post back for more details.

[data808]

Yeah it makes sense but i need the vba code and for which events to set it under. The more detailed the better. I'm pretty new to Access. I do know how to make the boxes invisible though. Hehe which isn't much. Would this apply to another database I have that does have the month, day, and year in the date? I may want to do something like this for my other database too.

[Dal Jeanis]

This is all a quick exercise in aircode, to point you in the right direction. I"m sure I'll type something wrong, but this should point you in a workable direction.
THe Form will have Properties

  Filter         => [DateFieldName] Between [txtHiddenFirst] AND [txtHiddenLast]
  Filter On Load => Yes

That syntax is assuming the query is in the form rather than a subform.
Start by leaving the "hidden" text boxes visible and hardcoding the txtHiddenFirst and txtHidden values to a couple of years (2011, 2012) for test purposes. Prove that your query and filter syntax is working before going any farther.

After that, put code behind the click event of the "reset filter" or "clear filter" button that changes txthiddenFirst to 0000 and txtHiddenLast to 9999, and possibly blanks out the visible ones too. The VBA for setting a field is pretty easy. As long as you're in a control on the same form it should be

Private Sub CmdClearFilter_Click()
   txtHiddenFirst = 0000
   txtHiddenLast = 9999
End Sub

YOu'll want that same code in the On Load event of the form.
Sometimes you have to use this kind of syntax, depending on the default property of a control:

mycontrolname.Value = "whatever"
mycontrolname.SomeOtherProperty = "whatever"

Maybe you should set the test boxes so they only accept numeric values.

Private Sub CmdSetFilter_Click()
   If txtFirst > "" {or numeric and greater than zero}
      txtHiddenFirst = txtFirst
   Else
      txtHiddenFirst = 0000
   End if
   If txtLast > "" {or numeric and greater than zero}
      txtHiddenLast = txtLast
   Else
      txtHiddenLast = 9999
   End if
   {you might put something here to swap them if the First is greater than the Last}
   {it's not absolutely necessary, because BETWEEN doesn't care about order}
   {if you do that, then be sure to swap both the hidden and visible ones }
   Me.Requery   
End Sub

This version would only set/adjust the filter when you click the setfilter button.

[data808]

Wow thanks for all the help. I'll let you know how this turns out. Seems pretty hard but I think once I start working on it, I may be able to figure it out. Thank you.

[data808]

"That syntax is assuming the query is in the form rather than a subform."

What exactly do you mean by subform? Sorry for the stupid questions but I am still learning the terminology.

[Dal Jeanis]

A form has a header section, a detail section, and a footer section. To use the above syntax, you put the selection fields on the header, have the form's records source as a query (or a table), set the form's filter properties as set above, and display the records in the detail section. That's one way.

A form can also have a "subform control" on it, a defined area of the form that displays a different form. You could have, literally, seven different subforms on the detail area of the same main form, each one displaying the results of a different query. For example, if you had a student database, you might have the main form's header area for selecting the student, an area (say a text box, not a subform) at the top left of the detail which displays contact information, an area (Subform) at the top right that displays the results of a query against the fees and payments tables, an area (Subform) at the bottom right that shows the current class schedule, and so on.

If your datasheet is the detail area of a main form, then you use the syntax I said. Here's a great reference that you should bookmark to see the syntax of how to refer to a form from a subform and vice versa. http://access.mvps.org/access/forms/frm0031.htm

So for the form itself, setting the filter for the form (from VBA code in the form or a control on the form) could look like this

Me.Filter = "[MyQueryDateField] BETWEEN " & txtHiddenFirst & " AND " & txtHiddenlast

Whereas setting the filter for the subform (from VBA code in the form or a control on the form) might look like this

Me!SubformControlName.Form.Filter = "[MyQueryDateField] BETWEEN " & txtHiddenFirst & " AND " & txtHiddenlast

This last translates as ...
find my form (Me)
and in my form's list of stuff (!)
find the item called SubformcontrolName,
and look at its property or member (.)
that is called