Using queries on sub forms (1 Viewer)

Spud33 · May 15, 2017

I am seeking help with what logically I see as an easy task, I appreciate this may be my 1st mistake.

I have a form that opens up 5 sub forms within and runs a separate query against each of the sub forms to populate the value required, which is the same value for each subform which is pulled from 5 seperate tables.

I have entered a ask parameter for eack query so when I launch the main form I get 5 pop ups asking for the value to be entered.

When entered (5 times) it runs and produces the results as expected.

Is there any way I can make it only ask once to run the same against each sub form query?

pbaldy · May 15, 2017

Most of us would suggest using a form for the user to enter their input, and point all the queries to that.

Spud33 · May 27, 2017

I have created a form that asks me for the input, the problem I have is it requires me to populate that input 5 times, to run each query, what I am seeking help with is to enter the value once and it to run across the 5 queries, does any one know how I can achieve this?, regards

pbaldy · May 28, 2017

If the queries point to the form, they should not ask for a value. Is the form open? Is everything spelled correctly?

moke123 · May 28, 2017

So the 5 subforms have the common value that you are entering in the input box. Does the parent form have that same value in its recordset?

Spud33 · May 28, 2017

Thank You for all your comments,

I have a main form lets call it Form A that has nothing attributed to it, it is basically a dumb form for the purpose of being able to review the data presented by the 5 x sub forms, all of which belong to separate tables which are fed in by the originators by excel documents which are imported into the backend to form the tables.

I then have 5 Sub Forms that I have embedded into "Form A" lets call these:

Form A1
Form A2
Form A3
Form A4
Form A5

All have a common field value but the field name is different on each of the 5 subforms\tables but allow me to interrogate the data running a query against a particular field.

I have created seperate query against each Sub form to pull up the data I require:

Form A1 Query
Form A2 Query
Form A3 Query
Form A4 Query
Form A5 Query

At this stage all works well, I am receiving a prompt for the relevant criteria agianst each query as expected as the Sub Forms opens which runs its respective query, but having to enter the value in 5 times. All the queries need the same value entering i.e. "Ducks".

I then created a seperate form called "FormQueryState" which has an unbound field (I guess I am expecting this to be where I enter "Ducks" to be aplied across all 5 queries).

I then added the following criteria to each of the 5 queries against the qualifiying field

[Forms]![FormQueryState].[txtEnterState]

When I run any of the forms or queries I get a pop up box:

Enter Parameter value
Forms!FormQueryState.txtEnterState

Form "FormQueryState" appears not to be loading and it looks as though it is still taking values directly from each queries. It will do this 5 times then present data as expected.

I am positive I am missng something really obvious, in relation to setting the form to feed the queries, but are not seeing the wood for the trees at the moment, I will probbaly kick myself

Uncle Gizmo · May 28, 2017

If I understand your question correctly, then you have 5 queries. Each query opens a small criteria form asking for the criteria for that query. The criteria is the same for each query and you would like to be able to enter this criteria in one operation. As has already been pointed out, the way to do this is to enter the criteria on to the main form (Form A) ....

You should get a good idea of what I mean and how to go about it from my YouTube video here:- VBA Beginner - Opening a Query - Nifty Access --- This video is one in a playlist on using VBA in MS Access the play list is here:- VBA Beginner - Nifty Access

Gasman · May 28, 2017

If I use the Builder to define the criteria for a query I get

Code:

Forms![Form1]![Text1]

I always get mixed up with when to use . and when to use !
Sometimes they seem interchangeable.

Spud33 said:
Thank You for all your comments,

I have a main form lets call it Form A that has nothing attributed to it, it is basically a dumb form for the purpose of being able to review the data presented by the 5 x sub forms, all of which belong to separate tables which are fed in by the originators by excel documents which are imported into the backend to form the tables.

I then have 5 Sub Forms that I have embedded into "Form A" lets call these:

Form A1
Form A2
Form A3
Form A4
Form A5

All have a common field value but the field name is different on each of the 5 subforms\tables but allow me to interrogate the data running a query against a particular field.

I have created seperate query against each Sub form to pull up the data I require:

Form A1 Query
Form A2 Query
Form A3 Query
Form A4 Query
Form A5 Query

At this stage all works well, I am receiving a prompt for the relevant criteria agianst each query as expected as the Sub Forms opens which runs its respective query, but having to enter the value in 5 times. All the queries need the same value entering i.e. "Ducks".

I then created a seperate form called "FormQueryState" which has an unbound field (I guess I am expecting this to be where I enter "Ducks" to be aplied across all 5 queries).

I then added the following criteria to each of the 5 queries against the qualifiying field

[Forms]![FormQueryState].[txtEnterState]

When I run any of the forms or queries I get a pop up box:

Enter Parameter value
Forms!FormQueryState.txtEnterState

Form "FormQueryState" appears not to be loading and it looks as though it is still taking values directly from each queries. It will do this 5 times then present data as expected.

I am positive I am missng something really obvious, in relation to setting the form to feed the queries, but are not seeing the wood for the trees at the moment, I will probbaly kick myself

pbaldy · May 28, 2017

>>Form "FormQueryState" appears not to be loading

This is why I asked if it was open. It will not open itself, you have to open it, enter your value and then open the other form, typically with a button on the criteria form.

Spud33 · May 28, 2017

Thanks Paul I have managed to sort having looked at what I was expecting it to do and you were right it would not have been able to run the query criteria if I had not already specified.

pbaldy · May 28, 2017

Happy to help.

Using queries on sub forms (1 Viewer)

Spud33

New member

pbaldy

Wino Moderator

Spud33

New member

pbaldy

Wino Moderator

moke123

AWF VIP

Spud33

New member

Uncle Gizmo

Nifty Access Guy

Gasman

Enthusiastic Amateur

pbaldy

Wino Moderator

Spud33

New member

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread