Dynamic Query dependent on Form being open 1

[philcon]

Hi there,

I am wondering whether I can dynamically update a query (standard access, not SQL) dependendent on a certain form being open.

Essentially if said form is open when the query is processed, I wish to use a value from the form as a criteria. However if the form is not open, I do not wish the criteria to be used or I will get a messagebox asking for the "missing" parameter on the lines of.

I know this can be done using SQL/VBA, but can it be done more simply in the query design tool?

Hope you can help


Philcon

 

[vb6novice]

Philcon,

It can very easily bee done with the query design tool by listing the form and control for the parameter in the criteria row of the desired field, but it is ugly. The input box you will get for the missing parameter will have "Enter Parameter Value" as the title and the label of the text box will be the name of the form and the control that it is looking for (e.g. Forms![FormName]![ctlName]). I don't think there is any way to get a custom input box in this instance.

If you know how to do it in code, stick with it.

 

[philcon]

Thanks VB, but what I was hoping to do was not get the parameter request at all if the form was not open. I understand how to put a field as a criteria, just wish the criteria to be ignored if the form is not open.

Thanks anyway

 

[danvlas]

1. Define the IsLoaded function in a module to return True or False depending on form being loaded or not (it's a very useful function and you should have it in your 'utilities').

Function IsLoaded(frm As String) As Boolean
Dim objFrm As Form
On Error Resume Next
Set objFrm = Forms(frm).Form
If Err.Number = 0 Then
IsLoaded = True
Else
IsLoaded = False
End If
Set objFrm = Nothing
End Function

2. Define a custom function so that the parameter is evaluated in the function and not in the SQL, thus getting rid of the parameter if the form is not loaded:

Function CustomFunction(FormName, ControlName)
If IsLoaded(FormName) Then
CustomFunction = Forms(FormName)(ControlName)
Else
CustomFunction = ""
End If
End Function


3. Build your Where clause in the query as below:

WHERE IIf(IsLoaded("YourFormName&quot, [FieldName] = CustomFunction("YourFormName","TextBoxOnForm&quot, ([FieldName] Is Null) Or ([FieldName] Is Not Null))

YourFormName: the name of the control you want to get the parameter from

TextBoxOnForm: name of the control providing the parameter

FieldName: name of the field in the query for which you set the condition. Depending on the query, uou may need to fully qualify it: [TableName].[FieldName]

(The second function is needed because the IIf in the query would try to evaluate Forms!YourFormName!TextBoxOnForm)


HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[philcon]

Thanks Dan,

I will make a note of that function, I actually got round the problem late last night by incorporating and invisible field in the form that launches the query and holds the other parameters for the query.

Essentially I created an onload subroutine based on the global variable that dictates whether the "additional form" gets opened.

Not sure that explains it particularly well, but it does work.

Thanks once again for responding.

Have a star for the isloaded function.