Executing Query after click on Textbox using Date value in textbox

[FL0XN0X]

Hi,

I have got a form with a textbox which contains a date. I'd like to execute a Query when I click the textbox using the date value in the textbox as a parameter in the Query.


Is this possible and how ?
Kind regards,
Bart

[pbaldy]

An action query? You can put:

DoCmd.OpenQuery "QueryName"

behind the click or double click events of the textbox. For a simple select query, I'd open a form or report based on it. Most of us never let users into tables or queries directly.

[FL0XN0X]

Hi pbaldy,

Thanks for your reply. Let me try to provide you with more details:

I have got 2 forms: Form1 and Form2
Form1 is based on a query, Query1. One of the objects in Form1, is a textbox containing a date
Form2 is based on a query, Query2. Query2 uses a date value provided by a textbox in Form2

What I want to achieve is that I do not have to fill in the date in Form2 to execute Query2 using this date as a parameter, but that Form2 is shown based on Query2 which is using the date from the textbox that was clicked in Form1.

[pbaldy]

Would it be appropriate to take the date criteria off the second query/form and use this to open it from form 1?

BaldyWeb wherecondition

[FL0XN0X]

Actually, it comes down to this: a CrossTab query where rows are dates and columns are specific values. The values within the crosstab query are 'counts', ie. the number of times the corresponding records occur.
For Example:

Criteria1 Criteria2
Date1 4 5
Date2 3 1

What I want to achieve is when I click the Textbox holding Date1, that 9 records are shown: 4 records have Date1 and Criteria1 and 5 records have Date1 and Criteria2 (you can compare it with a pivot table in Excel, where an extra tab is generated wih the corresponding records when a value in the piovot table is clicked)

[pbaldy]

You'd have to base the form/report on the detailed data underlying the crosstab (table or query). The technique above would limit it to the dates with the date you clicked on.

[FL0XN0X]

pbaldy,

OK. I have this query for the detailed data and a corresponding form based on this query. But how do I pass the clicked date value to be used into this query ?

[pbaldy]

I would use the method in post 4, but the query could reference the clicked date in its criteria.

[FL0XN0X]

Hi pbaldy,

I used the method in post 4 and it works. Thank you for the info. One more question however...

Now when I click in Form1 on the date, Form2 is opened with the WhereCondition using date from Form1, but the actual scenario is that Form1 is a subform and when I click on the date of Form1, then Form2 opens, but it should open as a subform, replacing Form1 (or making Form 1 invisible).
Can you assist me on this one please?
Kind regards,
Bart

[pbaldy]

Ah, a moving target, with an entirely different solution. You could have 2 subform controls, overlapping each other with the second one hidden. The second refers to the first for its criteria. In your click event, requery the second subform, make it visible, set focus off the first and then hide the first. You could also do it with a single subform and manipulate its record source.

[FL0XN0X]

pbaldy,


I have the 2 Subforms overlapping each other. So, now when I click the date in Subform1, I make Subform1 hidden and make Subform2 visible, but have do I pass now the clicked date to this Subform2 which is already visible. You mention 'Requery' but the Requery does not provid ethe WhereCondition property ...

On Click Event of first Subform:

Making the Second Suibform visible:
Forms![Overzicht capaciteit].[SubForm_Bezetting_per_dag].Visible = True
SetFocus
Forms![Overzicht capaciteit].[SubForm_Bezetting_per_dag].SetFocus
Hiding the first Subform
Forms![Overzicht capaciteit].[Capaciteit Type dagen per datum].Visible = False

Using the

'DoCmd.OpenForm "Subform_Bezetting_per_dag", , , "Datum=#" & Me.Datum & "#", I could pass the date from the first form to the second, but how to do this now ?

[pbaldy]

You missed:

The second refers to the first for its criteria.

In other words, the source query for the second includes a criteria that refers to the date textbox on the first. It should use the clicked-on record.