Code that will generate a Forms Name?

  • Thread starter Thread starter Bill Mitchell
  • Start date Start date
B

Bill Mitchell

I have created a search query that uses the fields of a
subform as its parameters. I would like this subform to
appear in different places within my database; however,
the parameter fields will have a different names
depending upon where the subform is located. I would
prefer not to have to rewrite my query over and over
again for each instance of the subform using the correct
subform location.

Is it possible to create a function which will return the
full name of the location of my search parameter fields
no matter where the subform is located?

For instance, say the subform is located on the "MAIN"
form and I have called the subform "MAIN: Sub". Assume
also that my search parameter field is called "Parameter".

Ok, the name of my field I would use in my select query
for this would be Forms![MAIN]![MAIN: Sub].form!
[Parameter].

This would work fine. However, if I used the same form
as a subform on some other form which had a different
name (for instance "MAIN2" and I named the subform 'MAIN:
Sub2"), this original select query would no longer work
because the names would be wrong.

I want to come up with a Function that will return the
correct location (e.g., 'Forms![MAIN]![MAIN: Sub].form!')
of the search parameter fields no matter where they are
located and then use that function in my query so one
query works everywhere.

So, for instance, if I created a Function called
FormLocation() that represented wherever my search
parameter fields are located, I could us FormLocation()
in my search query like FormLocation()&""&[Parameter]&"

Logically, I think this should be possible, but I can't
figure out how to make FormLocation return the name I
want.

Any help out there? Thanks in advance. I hope all that
made sense.

Bill
 
Bill-

You're on the right track, but don't have the function return the "name" of
the parameter -- have it return the filter value.

For example, if the WHERE clause of your query looks like:

WHERE CustomerID = [Forms]!![MAIN]![MAIN: Sub].form![Parameter]

Then just change it to call your function:

WHERE CustomerID = FindCustParameter()

Now, make your function return the value:

Public Function FindCustParameter() As Long
' Using the IsLoaded function from Northwind
If IsLoaded("Main") Then
' return the value from the Main form
FindCustParameter = Forms!Main![MAIN: Sub].Form![Parameter]
Else
' return the value from the Main2 form
FindCustParameter = Forms!Main2![MAIN: Sub].Form![Parameter]
End If
End Function

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Here is a question for you:

What do you do with the query results?

If you are sending the query results to a listbox, a form, a report, or even
another sub-form, then I would dump the use of ANY QUERY that has any hard
coded form reference.

I could probably write a book on the number of problems you encounter when
you hard code a query to a particular form. If you think about this, it
means that the query is complete attached to a particular form. If that form
is closed (by accident, or on purpose), then the query will not work. Worse,
is you spend all this time developing and designed a good query,and then
can't use in more the one place in the application, because the sql has a
stupid form reference. Further, if you ever migrate to sql server, or
another system, then queries don't have silly form references. If you are
new to development, then those query parameters with a form reference seem
quite cool. However, as you become a seasoned developer, you will avoid such
pitfalls like the plague. Worse, is not does a query become attached to a
form, but the reverse is also true. As general rule, you can certainly get
away using queries with parameters, but when those parameters start becoming
form references, you are shooting your foot off.

The sot I use is simple:
Grab the sql text, but build your own "where" clause in code. Further,
since 99% of the time that sql is going to be used for a another form or
report that you open, then you can use the built-in sql "where" clause.
Further, building your own "where" clause is far clearer for the cases where
YOU DO NOT want to prompt for certain parameters. Often we have a sales
report, and we can select what sales rep. However, maybe we want to display
all salesman. With parameter query's, it is a pain to deal with this
problem, since the query is still looking for a condition attached to a
search field that you did not set.

All in all, your development costs will go up, and your flexibility go down
when you hard code queries to forms. In fact, the whole basis of your
question is a result of this short coming.

So, if you are opening another form, then just dump all the parameters, and
build your own "where" clause like:

dim strWhere as string

strWhere = "LastName like '" & txtSLastName & "*'"

for additnal fields, you can go:

if isnull(txtSalesDate) = false
' then user entered a sales date for searching also
strWhere = strWhere & " and SalesDate = #" & txtSalesDate & "#"
end if

docmd.OpenReport "myreprot",acViewPreivew,,strWhere

Or, if a form, then use :
docmd.OpenForm "myform",,,strWhere

If the results are to be put into the current form, then you can use the
filter property of the form, but I prefer to grab the actual sql text (there
is a example of this later in this message).

If you look at the following screen shots, you will see all kinds of prompts
for reports, but I don't use one parameter query. I use the where clause.

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

Notice how in the above, I OFTEN has the instructions "blank = all". this is
nice, since often you don't want a parameter, and this is also a pain to
handle when you use a forms reference in the query.

I would say the only draw back of using "code" to make the where clause is
that you do have to write a bit of code, but not much! The end result to
this bit of code is that you would not even have been asking for a solution
to this problem!

If your results are not going to a form, or not to a report, but a
reocrdset, then you CAN continue to use query parms, but don't hard code
forms references in this case.

You can use:

dim qryBusList as query

Set qryBusList = CurrentDb.QueryDefs("qryTourBusListB")
qryBusList.Parameters(0) = TourId

Set rstBusList = qryBusList.OpenRecordset

The above used the Parms collection, and could have been

qryBusList.Parameters("[enter tour id]") = TourId

(the (0) = the first parm, so you can use a number, or the actual parm
text)

Of course, the above example is only good when you are creating recordsets,
you can't use the above when the query is a source for a form, or report, or
listbox.

If the results need to be sent to a listbox, or perhaps another sub-form,
then you can use:

dim MySql as string
dim MyWhere as string

MySql = CurrentDB.QueryDefs("qryGrabTourOptionsB").SQL
MySql = Left(MySql, InStr(MySql, ";") - 1)

The above removes the ";" from the sql text. Then

MyWhere = " WHERE tblTourOptions.OptionType <> 1" & _
" AND tblBooking.tour_id = " & MyTour.TourId & _
" AND tblBgroup.bus_id = " & frmBus.m_BusId & _
" AND tblBookOptions.BusList = True"

me.MyListBox.RowSource = mysql & MyWhere

And, if using a filter on a form, then you don't even need the sql if the
form is bound, you can go:

me.Filter = MyWhere
me.FilterON

(however, I perfer to stuff the whole sql right into the form in place of
the filter idea).

So, you have tons of options here, and none require that you use a query
with hard coding into a form. Like everything, if you just have one form,
and one query, then the using of a forms referance in the query is not a big
deal. As your application grows in complexity, then problems of
maintainability and simple re-use begin to show up, and that is when you
want to start avoiding queries with forms references.
 
Albert-

Good points all!

I showed Bill a way to solve the problem, but I should have questioned the
original premise.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top