Dynamic query/form field calling 2

[Triacona]

Dear all,

Please could you help me with the following problem.

I have a lot of queries using text from a form in their constraints.

between [forms]![MainSreen].[txtStartDate] and [forms]![MainSreen].[txtEndDate]

I have created another form BuildingControl with the same text fields:
txtStartDate
txtEndDate
But obviously it is another form...so therefore to call it

between [forms]![MainScreen].[txtStartDate] and [forms]![MainScreen].[txtEndDate]

Now we come to what I want to do...

I want to create within the query a dynamic or variable field call.

So instead of the form being listed, just

[txtStartDate] and [txtEndDate]

or

between [forms]![*].[txtStartDate] and [forms]![*].[txtEndDate]

So it gets the text field data from any form open that has those fields.

I.e.

between [forms]![any form open].[txtStartDate] and [forms]![any form open].[txtEndDate]

Is there a way to do this?

Please help, it will be greatly appreciated!

Kind regards
Triacona

 

[MajP]

The request seems a little strange but it could be done. Although it is probably more efficient to do it the way you show for flexibility and reuses I never write form references in the query. I find it difficult and time consuming.

Instead I use a user defined function. I would change

between [forms]![MainSreen].[txtStartDate] and [forms]![MainSreen].[txtEndDate]

to

between getStartDate() and getEndDate()

And build functions like

public function getStartDate() as date
  getStartDate = nz([forms]![MainSreen].[txtStartDate],date())
end function

So taking that to the next step. The following would work

Public Function getStartDate() As Date
  Dim frm As Access.Form
  Dim ctl As Access.Control
  For Each frm In Forms
    For Each ctl In frm.Controls
      If ctl.name = "txtStartDate" Then
       getStartDate = ctl.Value
      End If
    Next ctl
  Next frm
End Function

 

[Triacona]

Dear Majp,

Thank you so much for your help!!!!

It works!!

I have implemented you solution as such...

Option Compare Database

Public Function getStartDate() As Date
  Dim frm As Access.Form
  Dim ctl As Access.Control
  For Each frm In Forms
    For Each ctl In frm.Controls
      If ctl.Name = "txtStartDate" Then
       getStartDate = ctl.Value
      End If
    Next ctl
  Next frm
End Function

Public Function getEndDate() As Date
  Dim frm As Access.Form
  Dim ctl As Access.Control
  For Each frm In Forms
    For Each ctl In frm.Controls
      If ctl.Name = "txtEndDate" Then
       getEndDate = ctl.Value
      End If
    Next ctl
  Next frm
End Function

I have put

getStartDate() And getEndDate()

in
the queries.

Thanks so much MajP!! It works!!

Kind regards

Triacona

 

[MajP]

The function is a little expensive because it loops all open forms and all controls. That was because my assumption was that you had many different forms open and the date form my not be the active form. But if the date form is always the active form when you run the query then you can make the code more streamlined by simply taking out all the loops and
...
getStartDate = Screen.Activeform.txtStartDate
...

If it is not necessarily the active form, but it can be a known set of forms you could check to see which form is loaded using the allforms collection.

if currentproject.allForms("formA").isloaded then
getStartDate = forms("formA").txtStartDate
elseif currentproject.allForms("formB").isloaded then
getStartDate = forms("formB").txtStartDate
elseif ....

end if

Both of these are much more efficient since you not looping everything. This could make a big difference if using the function in a select statement for each record in a very big query.

 

[PHV]

big difference if using the function in a select statement for each record in a very big query
I don't think so as the functions have no argument and thus will be evaluated only once.

 

[MajP]

PHV, good point. I know that is the case for parameterless queries, is it the same for a non changing parmeter?
I know this runs for each record
select someFunction([fieldName]) as something
But does this run once or for each record?
select someFunction("someNonChangingString") as s

In back of my mind I was assuming the real use for this would be the ability to pull a start date, end date from different types of date fields. Maybe the start date is the shipped date, end date is the received date. Other cases the start could be received and end disposed date. That would make a little more sense to me, but require a non changing parameter.

Public Function getDateFromForm(ctrlName as string) As Date
  Dim frm As Access.Form
  Dim ctl As Access.Control
  For Each frm In Forms
    For Each ctl In frm.Controls
      If ctl.name = CtlName Then
       getDateFromForm = ctl.Value
      End If
    Next ctl
  Next frm
End Function

between getDateFromForm("txtShipped") and getDateFromForm("txtReceived")

or more likely you know which form for which query versus randomly searching for a form

Public Function getDateFromForm(frmName as string, ctrlName as string) As Date
  getDateFromForm = forms(frmName).controls(ctrlName)
End Function

 

[PHV]

select someFunction("someNonChangingString") as s
For me, evaluated once by the Jet engine.

 

[MajP]

Thanks, I guess I could have tested too.

 

[Triacona]

Dear MajP and PHV,

Thank you for all your help!

I have another question regarding this...

I have another field that a query calls from the active form.

It is a combo box field called cmbOfficer.
I have created another function...

Public Function getOfficer() As String
  Dim frm As Access.Form
  Dim ctl As Access.Control
  For Each frm In Forms
    For Each ctl In frm.Controls
      If ctl.Name = "cmbOfficer" Then
       getOfficer = ctl.Value
      End If
    Next ctl
  Next frm
End Function

I tried the above and the report is blank, no records, like it is not recognising the input??

Am I doing this correctly, I did try

 ...As ComboBox

in the function that gave me another error

undefined function getOfficer in expression.

Thanks for all your help, please can you help some more.

Kind regards
Triacona

 

[MajP]

Undefined function means it can not find it. Ensure the function is in a standard module not a form's module. Check your spelling. This would give you the error
... = getOficer()

If officer is a string then you need to enclose in single quotes.
where Officer = 'Smith'
Also make sure the combos bound field is the field you want.

Public Function getOfficer() As String
Dim frm As Access.Form
Dim ctl As Access.Control
For Each frm In Forms
For Each ctl In frm.Controls
If ctl.Name = "cmbOfficer" Then
getOfficer = "'" & ctl.Value & "'"
'To help debug use this and then delete after debugging
msgbox getOfficer
End If
Next ctl
Next frm
End Function

 

[Triacona]

Dear MajP,

Sorry I did a bit of debugging...and the following works.

Public Function getOfficer() As String
  Dim frm As Access.Form
  Dim ctl As Access.Control
  For Each frm In Forms
    For Each ctl In frm.Controls
      If ctl.Name = "cmbOfficer" Then
       getOfficer = ctl.Value
      End If
    Next ctl
  Next frm
End Function

BUT NOT IF the field is blank, i.e. I want all the officers.

in my query the criteria is as such.

Like "*" & getOfficer() & "*"

I did this so that even if there was no input the query would continue...
But now it seems to require it as if I run it blank I get the following message.

Run-time error '94' invalid use of Null

I click on debug and

getOfficer = ctl.Value

is highlighted.

Please help...
Thanks for all the help!
Kind regards
Triacona

 

[PHV]

getOfficer = ctl.Value & ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

try
...
getOfficer = nz(ctl.Value,"")
...

 

[MajP]

use phv's it would be more efficient.

 

[MajP]

Untested, but I think you can also do it so as not to use the like which could get you in trouble: John and Johnson.
... someField = getOfficer() or getOfficer() = ''

Public Function getOfficer() As String
Dim frm As Access.Form
Dim ctl As Access.Control
getOfficer = "''"
For Each frm In Forms
For Each ctl In frm.Controls
If ctl.Name = "cmbOfficer" Then
getOfficer = "'" & ctl.Value & "" & "'"
End If
Next ctl
Next frm
End Function

 

[Triacona]

Thank you both so very much!!!!!
It works!

 

[Triacona]

I used

Public Function getOfficer() As String

  Dim frm As Access.Form
  Dim ctl As Access.Control
  For Each frm In Forms
    For Each ctl In frm.Controls
      If ctl.Name = "cmbOfficer" Then
        [b]getOfficer = ctl.Value & ""[/b]
      End If
    Next ctl
  Next frm
  
End Function

This worked quite well
Kind regards
Triacona