Allan Browne's calendar

[Chris]

Hi,

I'd like to use Allan Browne's popup calendar on a form with a subform:
on the form frmResultList, I have a combobox cboSalesRep. When selecting a
name here I get in a subform subfrmResultList the result of a query showing
the sales results of that salesrep.
Is it possible to use Allan Browne's popup calendar to enter a start and end
date of a period showing the results of the salesrep during that period? I
can get the result by putting a Between-statement in the criteriabox of the
query, but I'd prefer using the popup calendar.

Thanks for your help.
Chris

 

[Tom Wickerath]

Hi Chris,

You didn't state whether your main form, "frmResultList", is a bound form
(ie. includes a Record Source) or is an unbound form. I prefer designing
search forms to find records using unbound forms. It is certainly possible to
use Allen's calendar control with an unbound search form. Here is an example
that you can download from my web site:

http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

I also have a tutorial on this technique, available here:
http://www.seattleaccess.org/downloads.htm

Look for this download:
Query By Form - Multi Select
Tom Wickerath, February 12, 2008

The tutorial includes a Word document, and a cut-down version of the
Northwind sample database. However, this sample does not currently include a
date range search.

If you are working with a bound form, then I suppose you would somehow need
to use the dates in unbound text boxes, with the values applied to a form
filter.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Chris]

Hi Tom,

thanks for your reply. I've been looking into the example from your website
and I notived the RequerySubform() is quite important. However when I tried
to understand (and adapt) the VBA for this, I got lost.

Here is my situation (the field names might seem a bit strange, but I am
dutchspeaking, so):

main form: frmResultList

SELECT *
FROM qry_verkoper;
(* = id_verkoper and naam_verkoper)

subform: frmResultListDate (based on qryResultDate)

SELECT tbl_verkoper.ID_verkoper, tbl_verkoper.naam_verkoper,
tbl_klant.klant_nr, tbl_klant.klant_naam, tbl_klant.klant_plaats,
tbl_actie.[datum uitvoering], tbl_actie.resultaatId,
tblResultaat.resultaatOmschrijving, tbl_actie.resultaatTxt
FROM tbl_verkoper INNER JOIN (tbl_klant INNER JOIN (tblResultaat RIGHT JOIN
tbl_actie ON tblResultaat.resultaatID = tbl_actie.resultaatId) ON
tbl_klant.klant_nr = tbl_actie.idKlantnr) ON tbl_verkoper.ID_verkoper =
tbl_klant.id_verkoper
GROUP BY tbl_verkoper.ID_verkoper, tbl_verkoper.naam_verkoper,
tbl_klant.klant_nr, tbl_klant.klant_naam, tbl_klant.klant_plaats,
tbl_actie.[datum uitvoering], tbl_actie.resultaatId,
tblResultaat.resultaatOmschrijving, tbl_actie.resultaatTxt
HAVING (((tbl_actie.resultaatId)<>2 And (tbl_actie.resultaatId)<>0));

Main and subform are connected by id_verkoper and IDverkoper.

What I am trying to get at is to select a salesrep (verkoper) from the
combobox on the main form and then choose a start date and end date (for
which I have 2 text fields with date picker button) from the date picker to
have as a result the records for the salesrep during the chosen period.

Does this make sense for any help?

Kind regards,
Chris

 

[Tom Wickerath]

Hi Chris,

thanks for your reply. I've been looking into the example from your website
and I noticed the RequerySubform() is quite important.

Yes, this function is central to the QBF functionality. You may have noticed
that each control that one can make selections from includes a call to this
function in the After Update event procedure. You can see this by examining
the Properties dialog, with the Event tab selected, when you pick one of the
controls.

However when I tried to understand (and adapt) the VBA for this, I got lost.

Did you grab the second download as well, which includes a Word document
that attempts to explain how it all works?

main form: frmResultList

Bound or unbound? My samples are based on an unbound main form.

subform: frmResultListDate (based on qryResultDate)

For the SQL statement that you showed here, you will likely want to
"hard-code" the SELECT clause. I don't see a WHERE clause in this SQL
statement, which is what the QBF example builds on-the-fly. So, where you are
currently showing "HAVING (((tbl_actie.resultaatId)<>2 And
(tbl_actie.resultaatId)<>0))", is the resultaatId the variable part?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Tom,

thanks for your reply. I've been looking into the example from your website
and I noticed the RequerySubform() is quite important. However when I tried
to understand (and adapt) the VBA for this, I got lost.

Here is my situation (the field names might seem a bit strange, but I am
dutchspeaking, so):

main form: frmResultList

SELECT *
FROM qry_verkoper;
(* = id_verkoper and naam_verkoper)

subform: frmResultListDate (based on qryResultDate)

SELECT tbl_verkoper.ID_verkoper, tbl_verkoper.naam_verkoper,
tbl_klant.klant_nr, tbl_klant.klant_naam, tbl_klant.klant_plaats,
tbl_actie.[datum uitvoering], tbl_actie.resultaatId,
tblResultaat.resultaatOmschrijving, tbl_actie.resultaatTxt
FROM tbl_verkoper INNER JOIN (tbl_klant INNER JOIN (tblResultaat RIGHT JOIN
tbl_actie ON tblResultaat.resultaatID = tbl_actie.resultaatId) ON
tbl_klant.klant_nr = tbl_actie.idKlantnr) ON tbl_verkoper.ID_verkoper =
tbl_klant.id_verkoper
GROUP BY tbl_verkoper.ID_verkoper, tbl_verkoper.naam_verkoper,
tbl_klant.klant_nr, tbl_klant.klant_naam, tbl_klant.klant_plaats,
tbl_actie.[datum uitvoering], tbl_actie.resultaatId,
tblResultaat.resultaatOmschrijving, tbl_actie.resultaatTxt


Main and subform are connected by id_verkoper and IDverkoper.

What I am trying to get at is to select a salesrep (verkoper) from the
combobox on the main form and then choose a start date and end date (for
which I have 2 text fields with date picker button) from the date picker to
have as a result the records for the salesrep during the chosen period.

Does this make sense for any help?

Kind regards,
Chris