query producing parameter value -set defualt

[crossy5575]

Hi there,

I have a form, which has a subform which is tabbed
The tabbed subform has the same query on each page, however filters is based on the tab number. (this was the easiest way of of showing lots of data)

The query runs and the query changes for each tab, however on the form loading it asks for the parameter value for the query - obviously as the query loads first before the tabbed section.

I want to put into the query sql a setting to show if the tab value is "" then just make it 1 so that the form loads without error.

I have got this far

SELECT tblproductdb.PC_Code AS Code, tblproductdb.PC_Page AS Page, tblproductdb.PC_desc AS [Desc], tblproductdb.size AS [Size], tblproductgroup.Purchasepr AS Price, tblorder1.quantity AS Quantity, [purchasepr]*[quantity] AS Total, tblproductgroup.grouping_code AS GC1, tblproductgroup.PC_group AS GC1a, tblproductgroup.[2ndgrouping] AS GC2, tblprodgroup2.Group AS GC2a

FROM (tblprodgroup2 INNER JOIN tblproductgroup ON tblprodgroup2.ID = tblproductgroup.[2ndgrouping]) INNER JOIN (tblproductdb LEFT JOIN tblorder1 ON tblproductdb.PC_Code = tblorder1.itemno) ON tblproductgroup.grouping_code = tblproductdb.grouping_code

WHERE (((tblproductgroup.[2ndgrouping])=IIf(IsNull([Form]![prodtab].[value]),1) Or (tblproductgroup.[2ndgrouping])=Nz([Form]![prodtab].[value],1)));

where form!prodtab is the subform

I dont want to put the mainform name in, as the query is used on another form.... Help please its driving me mad!!!

 

[Uncle Gizmo]

Please post your db with some sample data.

 

[arnelgp]

use the Enter event of your subform.
first set all subforms recordsource to blank, so when the form load it is blank.

then on the Enter event of your subform, set the recordsource:

Private sub subform_Enter()

If Me.yoursubformName.Form.RecordSource = "" then
Me.yoursubformName.Form.RecordSource = "SELECT tblproductdb.PC_Code AS Code, tblproductdb.PC_Page AS Page, tblproductdb.PC_desc AS [Desc], tblproductdb.size AS [Size], tblproductgroup.Purchasepr AS Price, tblorder1.quantity AS Quantity, [purchasepr]*[quantity] AS Total, tblproductgroup.grouping_code AS GC1, tblproductgroup.PC_group AS GC1a, tblproductgroup.[2ndgrouping] AS GC2, tblprodgroup2.Group AS GC2a

FROM (tblprodgroup2 INNER JOIN tblproductgroup ON tblprodgroup2.ID = tblproductgroup.[2ndgrouping]) INNER JOIN (tblproductdb LEFT JOIN tblorder1 ON tblproductdb.PC_Code = tblorder1.itemno) ON tblproductgroup.grouping_code = tblproductdb.grouping_code
WHERE (((tblproductgroup.[2ndgrouping])=IIf(IsNull([Form]![prodtab].[value]),1) Or (tblproductgroup.[2ndgrouping])=Nz([Form]![prodtab].[value],1)));"

End If
End Sub

 

[crossy5575]

Uncle Gizmo - obviously happy to post the DB on the website, however it is quite large do you have a suggestion to cut it down - ie just copy out the relevant data into a new DB?

Thanks Arnel

will give it a go! will feed back soon!

 

[Uncle Gizmo]

First check to see if compact on close is selected.

 

[crossy5575]

View attachment testinfo.accdb

Hi there,

here is the database with the issue with on it.

I dont really understand about setting the recordsource to zero, as it is coming directly from the query?

regards

Simon

 

[Uncle Gizmo]

I dont really understand about setting the recordsource to zero, as it is coming directly from the query?

I don't see any mention of this in previous replies in this thread? ---- puzzled ----

However it comes from the Idea that most tables have an autonumber field. Most auto number fields start at 1. In other words, there is never a 0 "zero" record. If you design a query which asks for the record 0, then, no records are returned. It's a good way of making sure that your form only returns an empty set. It is very light, in other words no data in it. In other words it speeds things up; supposedly!

 

[MarkK]

I think what's happening here is that your subform is loading before the main form. If you put a MsgBox in the open even of both a subform and its mainform host, you'll notice that the subform's Open event runs before the main form's!!!

So your subform is trying to reference a form that isn't open yet. That's the problem. There are a few workarounds, but what I usually do is just load the subform programmatically after the main form opens, so in design view of the main form, delete the text from the SourceObject property of the subform control. That will be the name of the subform.

Then, in Form_Open() of the main form, set the SourceObject property of the subform control, like . . .

private sub Form_Open(cnacel as integer)
   me.mysubformcontrol.sourceobject = "mySubformName"
end sub

That way the main form is open for sure before the subform tries to load its records.

Hope this helps,

 

[crossy5575]

Thanks Markk

I agree with what you said, I thought that too - I have adapted the script with your suggestion but still not working.

the msg box comes up with the form loading, but it doesnt show on the screen and still askes for the form!prodtab.value

btw i am opening the frm02purchaseorder form, and the subform is loading the query orderform2 directly as a query as opposed to a subform

I feel that i need to indroduce on the query
if isnull / isblank form!prodtab.value for selecting it which i cant seem to passthrough either on the form or on the query?

here is the View attachment testinfo.accdb

 

[MarkK]

I can't edit or view objects in design view in your database, I only have version 2007, but you aren't running subforms either, those are queries you are loading into the child controls. So my earlier comments probably don't apply.