Parameter Queries verses A Matrix Form

[Thales750]

I tend to use saved queries whenever possible. The down side of course is that writing DAO, ADO or SQL is made more difficult, whether from lack of practice or old age.
I can never remember which.

Anyway, I have used Parameter queries in the past and found them cumbersome, with all the making sure that every data type is matched up and all your little special characters are in the right place.

The problem with saved queries that get their data from controls on forms, is that you end up with too many queries that are almost identical, except they reference different forms.

So what do ya’ll think about creating a hidden form that you can push values to controls on the form, and then run saved queries that references that form.

Microsoft used to include that kind of behavior back in the 97 and 2000 days, in some of their wizards.

 

[the_net_2.0]

i would have other ideas Thales, but I'd have to see an example to tell you what I have in mind.

I did not know this about hidden forms previously. You can view the program's pre-forumlated forms in accwiz.mde, but you can't use them. So they used hidden forms to do WHAT, exactly? I'm curious...

 

[DCrake]

Take a look at this Link this may help you reduce you queries significantly.

 

[MarkK]

I have used Parameter queries in the past and found them cumbersome, with all the making sure that every data type is matched up and all your little special characters are in the right place.

I don't really understand this. Here's what I think of as a parameter query and note how you don't have to be concerned about data types or tedious delimiters...

dim qdf as dao.querydef
dim rst as dao.recordset
set qdf = currentdb.createquerydef("", _
  "SELECT * FROM Table " & _
  "WHERE StringField = [prm0] " & _
    "AND DateField = [prm1] " & _
    "AND IntegerField = [prm2];")
with qdf
  .parameters("prm0") = "Sample String"
  .parameters("prm1") = date()
  .parameters("prm2") = 12
  set rst = .openrecordset
end with

And I know this is a religious argument, but I don't believe in global variables. I'll maintain a few global objects on a hidden form; things with names like "ActiveJob" or collections and stuff that are costly to create.

 

[Thales750]

i would have other ideas Thales, but I'd have to see an example to tell you what I have in mind.

I did not know this about hidden forms previously. You can view the program's pre-forumlated forms in accwiz.mde, but you can't use them. So they used hidden forms to do WHAT, exactly? I'm curious...

Actually they were not "hidden", you just couldn't see them because there were no tabs.

They used them to as a place holder of values in their invoice reports.

So a form would pop up and you could edit values. but the form was behind the invoice after it opened.

 

[Thales750]

I don't really understand this. Here's what I think of as a parameter query and note how you don't have to be concerned about data types or tedious delimiters...

dim qdf as dao.querydef
dim rst as dao.recordset
set qdf = currentdb.createquerydef("", _
  "SELECT * FROM Table " & _
  "WHERE StringField = [prm0] " & _
    "AND DateField = [prm1] " & _
    "AND IntegerField = [prm2];")
with qdf
  .parameters("prm0") = "Sample String"
  .parameters("prm1") = date()
  .parameters("prm2") = 12
  set rst = .openrecordset
end with

And I know this is a religious argument, but I don't believe in global variables. I'll maintain a few global objects on a hidden form; things with names like "ActiveJob" or collections and stuff that are costly to create.

I just got around to trying my first public variable as public function in a query. Is ther eany reason I would not want to do this?

Because to me it could not be simpler.

 

[MarkK]

...public variable as public function...?

A public function makes sense. A public variable in a class module, OK.
I'd warn against global public variables because over time it can become very difficult to be sure that the data they contain is correct. Maybe you solve a problem with a global, and six months later you need to solve another problem and you create another global. Over time as you develop your system your global variables propagate and different chunks of undocumented code write to and/or read from globals here and there and it becomes sloppy, fragile and increasingly costly to maintain.
If you can use globals and not let them get out of control, go for it. They're certainly easy to use.
My 2c,
Mark

 

[Thales750]

So far I haven't imagined any use for them other than the criteria for queries.

Some of my systems have a thousand (or close to it) saved queries. I would bet nearly half are total duplicate, and even more of them are partial duplicates.

Some care must be taken though in a tabbed system, you could easily run a query with the wrong criteria. You’ll probably want to avoid that one.

The best lesson here though is analogous to driving to the same place for many years and suddenly discovering a short cut. This will save me a lot of time.

 

[Thales750]

Interestingly enough, I have now created a standard form that populates a text box for each Public Variable at the on activate event.

I use it as an at-a-glance reference for the current value of the variables.

It all comes around full circle.

 

[Thales750]

UPDATE:

Since then I have introduced many new restrictions on how we develop. One of them is to have a little app that creates a list of Variables and then copies it the clipboard.

We use the copied data to populate the Public Variable Module. That way any adhoc public variables are deleted. It makes folks pay attention.

We have dozens and this app makes them all useful.