Query Results from Mainform to Subform. (1 Viewer)

ConnorGiles

Strange Traveller
Local time
Today, 11:49
Joined
Aug 12, 2014
Messages
1,068
Hi Again.

Basic Problem I hope,

My Form is taking its results from a parameter query, and since my subform also has the same control source (the query).

It always prompts me twice, although not a major problem it is a pain!

Is there any way that when I type the search results once the sub form takes the results from the main form?

Thanks in advance.

(P.S. The form asks me for parameters even when printing and saving, is there not a way that this can be stopped too by maybe taking the results off the current page?)

:banghead::banghead::banghead:
 

pr2-eugin

Super Moderator
Local time
Today, 11:49
Joined
Nov 30, 2011
Messages
8,494
What is the SQL of the Query you are using? Why is that prompting in the first place?

To make sure the SubForm gets the Data according to the Main Form, make sure the Parent and Child ID are linked.
 

ConnorGiles

Strange Traveller
Local time
Today, 11:49
Joined
Aug 12, 2014
Messages
1,068
Code:
PARAMETERS [Enter Delivery Note No:] Text ( 255 );
SELECT [Consolidated Orders].Driver, [Consolidated Orders].Customer, [Consolidated Orders].[Date Reqd], [Consolidated Orders].[Plot No], [Consolidated Orders].Product, [Consolidated Orders].[House Type], [Consolidated Orders].Site, [Consolidated Orders].[Del Note], [Consolidated Orders].[Del Date], [Consolidated Orders].[Invoice Date], [Consolidated Orders].[Inv No], [Consolidated Orders].TotalValue, [Consolidated Orders].[Order No], [Consolidated Orders].[Job No], [Consolidated Orders].[Order No (Comments)], [Consolidated Orders].[Account Number]
FROM [Consolidated Orders]
WHERE ((([Consolidated Orders].[Del Note])=[Enter Delivery Note No:]));

Hey Paul,

Hows it going bud?

This is my SQL code.

I'm using a parameter query to search through my main table.

The parameter query is prompting me multiple times when saving, printing and opening.

How would I link the form and subform's parent and child ID?
 

pr2-eugin

Super Moderator
Local time
Today, 11:49
Joined
Nov 30, 2011
Messages
8,494
Since your SQL asks for a parameter, you have three options.

First option, which you are currently doing, entering it manually.

Second option, is to create a Form that will get the Delivery Note Number and your SQL to be modified as,
Code:
SELECT 
    [Consolidated Orders].Driver, 
    [Consolidated Orders].Customer, 
    [Consolidated Orders].[Date Reqd], 
    [Consolidated Orders].[Plot No], 
    [Consolidated Orders].Product, 
    [Consolidated Orders].[House Type], 
    [Consolidated Orders].Site, 
    [Consolidated Orders].[Del Note], 
    [Consolidated Orders].[Del Date], 
    [Consolidated Orders].[Invoice Date], 
    [Consolidated Orders].[Inv No], 
    [Consolidated Orders].TotalValue, 
    [Consolidated Orders].[Order No], 
    [Consolidated Orders].[Job No], 
    [Consolidated Orders].[Order No (Comments)], 
    [Consolidated Orders].[Account Number]
FROM 
    [Consolidated Orders]
WHERE 
    [Consolidated Orders].[Del Note] = Forms!FormNameYouJustCreated!ControlNameToGetTheNumber;
Third option is manipulating the QueryDef object using VBA. The second method is very smooth and will be the easiest solution to implement.
How would I link the form and subform's parent and child ID?
This depends on your Form setup, but basically if they are bound forms, you will have an option in the SubForm (Property Sheet -> Data Tab) Link Master Fields, Link Child Fields. Just set the link.
Hey Paul,

Hows it going bud?
I am alright thanks, been working very hard ;) You alright?
 

ConnorGiles

Strange Traveller
Local time
Today, 11:49
Joined
Aug 12, 2014
Messages
1,068
Since your SQL asks for a parameter, you have three options.

First option, which you are currently doing, entering it manually.

Second option, is to create a Form that will get the Delivery Note Number and your SQL to be modified as,
Code:
SELECT 
    [Consolidated Orders].Driver, 
    [Consolidated Orders].Customer, 
    [Consolidated Orders].[Date Reqd], 
    [Consolidated Orders].[Plot No], 
    [Consolidated Orders].Product, 
    [Consolidated Orders].[House Type], 
    [Consolidated Orders].Site, 
    [Consolidated Orders].[Del Note], 
    [Consolidated Orders].[Del Date], 
    [Consolidated Orders].[Invoice Date], 
    [Consolidated Orders].[Inv No], 
    [Consolidated Orders].TotalValue, 
    [Consolidated Orders].[Order No], 
    [Consolidated Orders].[Job No], 
    [Consolidated Orders].[Order No (Comments)], 
    [Consolidated Orders].[Account Number]
FROM 
    [Consolidated Orders]
WHERE 
    [Consolidated Orders].[Del Note] = Forms!FormNameYouJustCreated!ControlNameToGetTheNumber;
Third option is manipulating the QueryDef object using VBA. The second method is very smooth and will be the easiest solution to implement.

I would need to enter it manually Paul as each time I use this, it would be different.

Was just wondering if there was a simple way to just get the search results from the form and implement them to the subform without re-entering the parameters.

This depends on your Form setup, but basically if they are bound forms, you will have an option in the SubForm (Property Sheet -> Data Tab) Link Master Fields, Link Child Fields. Just set the link.
Done that, didn't seem to do anything :confused:

Sorry when it comes to untouched territory I turn into a dunce!


I am alright thanks, been working very hard ;) You alright?

Glad to hear! Still in that job? Been very busy myself! Darn the start of the year! so much to do!
 

pr2-eugin

Super Moderator
Local time
Today, 11:49
Joined
Nov 30, 2011
Messages
8,494
I am a bit cloudy with your requirement. Could you explain the Form setup? I have strange feeling the solution you need is the second option I mentioned.

As you can enter the Number here anytime, and the Query will work the same way as you have currently, except it will not ask for the information so many times.

Glad to hear! Still in that job? Been very busy myself! Darn the start of the year! so much to do!
Yup, still here, thought I told you this earlier.
 

ConnorGiles

Strange Traveller
Local time
Today, 11:49
Joined
Aug 12, 2014
Messages
1,068
Basically what happens is,

Form loads and asks you for delivery note number from the table.

The delivery note is then processed and turned into a invoice.

The parameter pops up twice.

and pops up twice even when saving and printing.

I wish to just be able to save and print without needing to type parameters. and without the need to type it twice :p

and you probably did, my memory is quite poor! :p
 

RainLover

VIP From a land downunder
Local time
Today, 22:49
Joined
Jan 5, 2009
Messages
5,041
Without looking at the actual DataBase I cannot offer a solution.

But it would help if we knew what was what.

Like what is [Consolidated Orders]

Is it a shared query or perhaps a Table. Why are you using spaces. Not wrong but it would be better without them.

What has printing and saving got to do with all this.

Your problem most likely lies with the opening. The Sub opens first but there is nothing for it to find.

Just thinking out loud.

Paul back to you.
 

ConnorGiles

Strange Traveller
Local time
Today, 11:49
Joined
Aug 12, 2014
Messages
1,068
Consolidated Orders is the main table.

I used spaces due to the fact the company wanted it named the same as the excel sheet, which was also called Consolidated Orders.

(My project was to swap it from excel into access entirely) worked so far.
 

RainLover

VIP From a land downunder
Local time
Today, 22:49
Joined
Jan 5, 2009
Messages
5,041
I used spaces due to the fact the company wanted it named the same as the excel sheet, which was also called Consolidated Orders.
This has little to nothing to do with the name of the field. Use proper names for the fields then when you create Forms and Reports you can change the name of the label to anything you want. In your case you would use the names as instructed.

This way you can Code much easier and still retain the original names for viewing.

The user should never see any of the Fields when looking at a form, This is particularly true once you lock the Database down.

I hope I have explained this properly.
 

Users who are viewing this thread

Top Bottom