is there a way to automatically cancel the parameter message box?

[msmuzila]

is there a way to automatically cancel the parameter message box on a
form open?

Thanks
Matt

 

[BruceM]

Do you mean you want it not to show up at all? Details needed.

 

[msmuzila]

Yes, i want it to cancel to box automatically. So that it appears that
the box never showed

 

[BruceM]

Remove the criteria from the query.

 

[msmuzila]

I need to filter the selection to the specific combo box selection

 

[BruceM]

You need to provide details. I'm not going to guess what you mean. What
selection? What combo box? If the parameter dialog box shows up when the
form opens, the form's record source query has criteria. Eliminate the
criteria and you will eliminate the dialog box.

 

[msmuzila]

this is what i put in the crosstab criteria for the parameter

IIf(IsNull([forms]![Part_Monthly]![Combo36]),1010,[forms]![Part_Monthly]![Combo36])

when i open my form it still asks me for
[forms]![Part_Monthly]![Combo36]

when i hit cancel on this message box, it goes to the correct spot, so
is there a way to automatically cancel the parameter message box?

 

[BruceM]

What is "crosstab criteria for the parameter"? When you view the query in
design view, is there something in the Criteria row? What is
[Forms]![Part_Monthly]![Combo36]? Is it from a form you are using for the
criteria? If so, the form needs to be open.
[Forms]![Part_Monthly]![Combo36] is something the query can't find. What is
it? Only you can answer that.
It may be best if you post the SQL. To do that, open the query, click View

SQL, then copy and paste into a message.

this is what i put in the crosstab criteria for the parameter

IIf(IsNull([forms]![Part_Monthly]![Combo36]),1010,[forms]![Part_Monthly]![Combo36])

when i open my form it still asks me for
[forms]![Part_Monthly]![Combo36]

when i hit cancel on this message box, it goes to the correct spot, so
is there a way to automatically cancel the parameter message box?

 

[msmuzila]

here is the sql

PARAMETERS [forms]![Part_Monthly]![Combo36] Long;
TRANSFORM Sum([QTY_SHIP]*[QUANTITY]) AS [Parts Shipped]
SELECT Month([DATE]) AS Months
FROM (ORDER_DETAILS INNER JOIN PRODUCT_PARTS ON ORDER_DETAILS.PRODUCT =
PRODUCT_PARTS.PRODUCT) INNER JOIN Invoices ON ORDER_DETAILS.ORDER_ID =
Invoices.INV_NO
WHERE (((Year([DATE]))>Year(Now())-6) AND
((PRODUCT_PARTS.PART_ID)=IIf(IsNull([forms]![Part_Monthly]![Combo36]),1010,[forms]![Part_Monthly]![Combo36])))
GROUP BY Month([DATE])
ORDER BY Month([DATE]), Year([DATE])
PIVOT Year([DATE]);

The form is Part_monthly and the Combo box on that form is Combo36,
which is the parameter for the query It works fine once it is open. But
the intial open is the problem because the combo has no value yet.

 

[BruceM]

I had imagined that Part_Monthly was a form from which you opened another
form. Now it appears that you are trying to open Part_Monthly. If so, what
is the point of the criteria? If I am mistaken, and Part_Monthly is a form
from which you are opening another form, it needs to stay open while the
other form is opening.

 

[msmuzila]

Part_Ship_Monthly is the crosstab query that generates the information
for Part_Monthly, a form.

 

[BruceM]

Then you need to eliminate the criteria, or at least change it. Setting the
criteria to something within the criteria is meaningless.

I have suggested several times that you remove the criteria. Go to query
design view, and clear the criteria that references the combo box. Have you
done so?

 

[Danny J. Lesandrini]

I haven't been following this thread closely enough, so maybe I'm off base,
but from what Bruce suggested, I think my idea might help. The issue is this:

* You have a query that is based on values on a form
* Sometimes the query runs when the form is open
* Sometimes not, but you want it to run all the same.

DON'T base the criteria on the form controls! Base it on a function, and
allow the function to look up the value.

Why? Because the function can handle the error of the form not being open.

Instead of criteria like this:
IIf(IsNull([forms]![Part_Monthly]![Combo36]),1010,[forms]![Part_Monthly]![Combo36])

It will look like this:
GetCombo36(1010)

Then, you create a Function to do the logic, like this:

Public Function GetCombo36(ByVal lngID As Long) As Long
On Error Resume Next
Dim lngCombo36 As Long

' You'll need an IsLoaded or equivilent function
If IsLoaded("Part_Monthly") Then
lngCombo36 =[forms]![Part_Monthly]![Combo36]
Else
lngCombo36 =lngID
End If

GetCombo36 = lngID
End Function

If the query parameter is using a function, then it will always work.

 

[msmuzila]

where do i place the function, does it go in a module? or on the form.

 

[Danny J. Lesandrini]

I put these functions in global modules, so they are always available.

 

[msmuzila]

getting a debug error on the isLoaded function, should i use a
different one?

 

[Danny J. Lesandrini]

Yes, I mentioned that you'd need an IsLoaded function. If you're using Access 2002, then
you can use this built-in function ...

CurrentProject.AllForms("frmMyForm").IsLoaded

Otherwise, you may need your own function ...

Public Function IsLoaded(ByVal strFormName As String) As Integer
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function