ACCESS FEED: RE: [MS_AccessPros] Fill List Box from Date Search

Selasa, 25 Juni 2013

RE: [MS_AccessPros] Fill List Box from Date Search

Grant-

You're on the right track with your query. Build something similar to be
the Row Source of your list box, but display only the data needed to make a
selection - perhaps EventID and some description of the event. When the
user clicks the button to search based on dates entered, validate the two
dates in code, then Requery the List Box to see a list of events filtered on
the dates entered. In the AfterUpdate event of the List Box, grab the
EventID selected to filter the rest of the form. I would have to know more
about the Record Source of the form to help you do that exactly, but the
simplest way would be to set the Filter property of the form to something
like:

Me.Filter = "EventID = " & Me.lstBoxEvents

.. then apply the fitler by setting the FilterOn property.

Me.FilterOn = True

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of beinkeboy
Sent: Wednesday, June 26, 2013 12:49 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Fill List Box from Date Search

Hi,

I have two text boxes for entering Before and After dates to get a date
range. I want to then fill a list box with the items in the date range.

I then want the user to click on the list box and update the bottom section
of the form.

I have added a screen grab of the form to the Photos section with the same
name as this post.

I have the following SQL statement that works in a query:

SELECT tbl_Event.EventID, tbl_Event.Date, tbl_Event.OrganisationID,
tbl_Event.Time, tbl_Organisation.Organisation, [Last Name] & ", " & [First
Name] AS Name, tbl_Event.StaffID, tbl_Staff.[Last Name], tbl_Staff.[First
Name] FROM (tbl_Organisation INNER JOIN tbl_Event ON
tbl_Organisation.OrganisationID = tbl_Event.OrganisationID) INNER JOIN
tbl_Staff ON tbl_Event.StaffID = tbl_Staff.StaffID WHERE (((tbl_Event.Date)
Between [Forms]![frm_Event]![txt_Date_After] And
[Forms]![frm_Event]![txt_Date_Before]))
ORDER BY tbl_Event.Date DESC;

I have a basic understanding of VBA but are not to good with recordsets etc.

Any assistance would be gratefully appreciated.

Thank you

Grant Beinke

------------------------------------

Yahoo! Groups Links