Multiple forms use one query

[Pyrite]

I have documents from different companies that I mail to engineers.
Periodically these docs change so I record which version I have sent to an
engineer. I have a table with no primary key to hold site name and form
version so I can hold as many different form versions as I need per company
name. I have a query that runs from this table where site name equals the
selection made in a drop down (cboSiteName) on frmFormSent. The next drop
down on frmFormSent is cboVersionSent which is based on above mentioned query
and displays the possible versions of that companies form. This requeries on
got focus to keep the list up to date. So far this all works fine, what I
would like to do is have this same action run on other forms without having
to duplicate the query. All in all I would like it to run when a site name is
selected on any one of 3 forms. I have tried entering each combo box name in
the OR sections of site name on the query but then when I select a site name
on one of the 3 forms I get another parameter box popping up asking me for
the other two site names.

Hope that made some sense.

 

[ceesdatabase]

Hello Pyrite,

It is possible by changing the query with VBA when the form loads and closes.
I tried to "copy" your situation and created 3 forms (frmFormSent_1,
frmFormSent_2, frmFormSent_3 and the query "Query1")

The sample query :
SELECT fieldName
FROM tblTableName
WHERE tblTableName.fieldName = [forms].[frmFormSent_1].[cboSiteName]));

In frmFormSent_2 and frmFormSent_3 add the following procedures :

Private Sub Form_Load()
Dim db As Database
Dim qd As QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs("Query1")

Me.cboVersionSent.RowSource = ""

With qd
.SQL = Replace(.SQL, "frmFormSent_1", Me.Name)
.Close
End With

Me.cboVersionSent.RowSource = "Query1"

End Sub

AND ****************

Private Sub Form_Unload(Cancel As Integer)
Dim db As Database
Dim qd As QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs("Query1")

With qd
.SQL = Replace(.SQL, Me.Name, "frmFormSent_1")
.Close
End With

End Sub

AND *************************

A reference to DAO 3.6 library

I hope my example is understandable

grtz

 

[John Spencer]

If only one of the three forms is open, then you can use a function in the
query to grab the relevant values. Also, you might be able to use
Screen.ActiveForm to get the current form.

UNTESTED off the top of my head AIR CODE
Assumption: all three forms have the combobox named the same

Public Function fGetSiteName()
fGetSiteName = Forms(Screen.ActiveForm.Name).CboSiteName.Value
'Add some error handling to this to take care of no form being open
'or the wrong form being selected.
End Function

In the query, use fGetSiteName() to return the value instead of a direct
reference to the Forms![One of three forms]!cboSiteName

You could also test for any of the three forms being open and then grab the
combobox value from the first one you found open. If none of the three were
open, then do something such as return a default value


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Pyrite]

Thanks both for your suggestions, I have managed to reorder my forms slightly
so the goalposts have moved a little. Sorry. (I have used your suggestions to
cover multiple forms but only two not 3, I have changed the 3rd form instead)

I now need to do a similar thing for the combo boxes on the 3rd form. Let me
explain.

An engineer can be cleared for up to 5 sites at once so I have a form with
cboSiteName1,2,3,4,5 each of which can display a site name that the engineer
is cleared for. This row source comes from a Site Names table. There is also
cboVersionCleared1,2,3,4,5. As each site can have different documentation
versions the second drop down needs to row source from a query. To clarify:

The user selects 'Pyrite' in cboSiteName1 and then clicks on
cboVersionCleared to select which documentation version has been returned to
the company. Each 'Site Name' can have multiple documentation versions unique
to itself so a query runs from tblVersion which has no primary key and has
two fields, site name and version allowing multiple versions to be entered
for the same site name. When the user clicks this second drop down it
requeries itself and provides only the versions available for that site name.
So for 'Pyrite' it may return 'August 2007' and 'August 2008' as options. But
on another comany this may be 'May 2006' and 'May 2008' and 'July 2008' and
so on.

I tried to do this for all 5 drop downs in the form but was presented with
every version for every site in cboVersionCleared because the query was
running on an or basis and returned all results instead of just the results
for the relevant cboSiteName entry.

To get round this temporarily I have created 5 identical queries which just
look to only one of the cboSiteName boxes. Each of the 5 cboVersionCleared
drop downs then row sources from its matching query. Seen as they are all
doing the same thing this seems wasteful but was the only way I could figure
round it for now.

In my head I think the OR shouldn't be in the query, the query should be
told to look at the cboSiteName with the same number as the cboVersionCleared
drop down. E.g. if cboVersionCleared3 is clicked the query should take its
parameter from cboSiteName3. I have no idea if this is possible.

 

[ceesdatabase]

Hello Pyrite,

The problem looks the same but the solution for the forms won't work here.

The easiest way is to work with seperate queries, but it was a challenge to
find
a solution so again I tried to "copy" your situation and you can find a
possible
workaround on my website at
http://www.haaring.com/downloads/demoForPyrite.mdb

The main idea is to pas all the information for the query through a hidden
field <selected> which is colored red on the form and is invisible.

Hopely it's helpfull for your application developement.

grtz