• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Selecting Dates and displaying related fields via Combobox

Hi I would like to select dates from my table via combo box  and display the related information on the Split form's datasheet.

For example I have the below data in the table:
date                        name                 code
05/01/2011             John Smith          A
05/01/2011             Jane Smith          B
05/15/2011             George Ellis        A
05/15/2011             Jane Smith         K

So when I select from my combobox the date of 05/01/2011 I only would like to see the below information  in the datasheet.

05/01/2011             John Smith          A
05/01/2011             Jane Smith          B


Any help will be greatly appreciated.
0
Sheogorath
Asked:
Sheogorath
  • 2
  • 2
1 Solution
 
danishaniCommented:
You can make use of the Filter() property of your Subform, based on your Combox value.

In the AfterUpdate event of your Combobox, try something like this:

Dim strFilter As String

strFilter = "[DateFieldName]=" & Me.ComboboxName

Forms![YourMainFormName]![YourSubFormName].Filter = strFilter

Forms![YourMainFormName]![YourSubFormName].FilterOn = True

HTH,
Daniel
0
 
danishaniCommented:
Oops, saw one error in above post, dealing with a Date Field, you need hashes:

Dim strFilter As String

strFilter = "[DateFieldName]=#" & Me.ComboboxName & "#"

Forms![YourMainFormName]![YourSubFormName].Filter = strFilter

Forms![YourMainFormName]![YourSubFormName].FilterOn = True

HTH,
Daniel
0
 
Jeffrey CoachmanCommented:
Typically a Date picker is used for this...

Listing dates from a list of records can create issue with duplicates, forcing you use DISTINCT or GROUP BY in the SQL (in other words,  complicating things)

So, in your case the rowsource for the combobox may need to be something like this:

SELECT YourDateField
FROM YourTable
GROUP BY YourDateField

<display the related information on the Split form's datasheet>
Just to be clear, any filter will be displayed in both parts of a split form

;-)

JeffCoachman
0
 
Jeffrey CoachmanCommented:
Thanks.

As a follow up, you can simply create a table of all "available" dates, and update it periodically.

You can auto-fill sequential dates in Excel quite easily...

;-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now