Parametric Search Implementation

[cprobertson1]

Hello again folks!

To make a long story short, I want to create a parametric search for tools on the shop floor based on multiple fields [using VBA]

I've had a good search and turned up lots of useful things; but each example insofar has been fairly specific as opposed to a guide on how to implement it in a wider setting.



Let us say, for example, that my database has three fields each with a bound combo box: location; house-type; number of bedrooms

As I type in location, I want it to filter my results in the other boxes to that location (should be a simple case of applying a filter?) - which leaves me with all the house-types and bedroom-counts in that location.

As I type in the house-type, I want it to then narrow down the search again to filter the "location"+"house-type" to leave me with the available number of bedrooms.


What is the best way to implement this, given that I have about 20 fields*, some of which will inevitably be blank: as the user types in (or selects using combo boxes) the data required, it narrows down the search - and finally there's a "search all" button which returns all results matching the current criteria.

When I say "implement" I mean what underlying structure should I use. Is it best to create a query and update the query as items are entered, or would it be better to filter the form that's having the data entered?

Many thanks again!



*the fields are organised sensibly into sub-forms for various characteristics (i.e manufacturing data, materials data, cutting data, etc, etc), don't worry - it's note a huge mess - and the code is all there to pull the data together as required!

 

[pbaldy]

I would use dynamic SQL, as demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

I'm comfortable with code though. This would be another way:

http://access.mvps.org/access/queries/qry0001.htm

but saving it from design view will cause Access to save it into a different format that I find difficult to work with for that many fields.

 

[cprobertson1]

Thanks! I'll give that a go on the morrow, I just this moment got in the house!

I'll let you know how I get on

It might be after lunch that I finally get round to implementing your suggestions though, I have about 100 tools needing etched up with their ID numbers.

 

[pbaldy]

Home already?!? It's not even 9am!

No problem, post back if you get stuck.

 

[cprobertson1]

Hello again!

Home already?!? It's not even 9am!

ah the joys of timezones xD I work the same hours as the CNC operators though: 0730-1600; so I guess I still finish rather early Does mean an 0600 wakeup call though... and I'm the exact opposite of a morning person xD

Had a good look at the links you posted: particularly the VBA one (as the database uses VBA for any coding - decided to not use a mixture of macros and VBA as my other database did (access' button-wizard likes macros)

The problem here is that I'm not fully comfortable with SQA yet - VBA I can manage as I have a background in programming (actually, I have a background in BioChemistry, learned programming in my spare time and then became an engineer. No I can't figure out how it happened either...)

--EDIT-- Looks like my plan to use the combo boxes to update a query with the filters didn't work: >10 combo boxes, but you can only apply <=10 fields to a query - should have realised!

That leaves me to take a crash course in SQL - to the Internet!

 

[jdraw]

Good starting point http://www.w3schools.com/sql/
good luck

 

[cprobertson1]

good luck

Luckily (ha!) it was much simpler than I thought it was going to be!

That site was excellent - spent the last day and a bit getting everything down - and hoping to have it implemented in Access by the end of today!

Thanks very much for that - it's been a major help!

I'll let you know how it turns out (and pester you with questions if I hit a wall somewhere )

 

[cprobertson1]

Right, I *think* I have the SQL under my belt, and I have VBA assembling strings to pass to the SQA...

Which leaves me wondering: what is the most appropriate way to actually execute teh SQA? There looks to be four main methods (as per this link) - but I was running into problems no matter which I used.

--EDIT--

In particular, I was being told I could not use the SQL SAMPLE function: which left me wondering what the best way to do it would be - given that I'm doing my best to keep the infrastructure written entirely in VBA (for the sake of making it less convoluted both for myself and the IT folk who'll no doubt end up modifying it at some point despite not sitting by my side when I programmed it!)

Is running the Querydef Execute Method (as per page 2 of the page I linked) the way to go? Or would I be better setting a query and dynamically updating its parameters with SQL assembled in the VBA side of things?

So many design choices!

--EDIT--

Just found this which looks to be pretty much what I'm trying to do - will let you know how it turns out!

Thanks for all your help btw! It's been very much appreciated - it's great to have a forum where the users aren't snobby or stand-offish to the uninitiated

 

[pbaldy]

The 4 methods are for executing action queries (insert, update, delete...). For what you're doing, I'd probably set the source of a subform or listbox.

 

[cprobertson1]

The 4 methods are for executing action queries (insert, update, delete...). For what you're doing, I'd probably set the source of a subform or listbox.

Oops! Sorry! I meant method in the sense of "a way of doing things" - not in the programming sense - sorry! Should have been less ambiguous

These where what I was referring to
-DoCmd.RunSQL
-DoCmd.OpenQuery
-[Querydef].Execute
-[Database].Execute

I've actually got a test-module working which grabs the values from combo boxes, assembles the SQL and passes that back to a query which is used to filter the results displayed in the other combo boxes: and the "search" button just opens the query with the applied filters

[NB - I'll probably change this in future, it's just a temporary measure as I need something to show the gaffers - and it does technically work, even if it isn't too neat: see "technology debt" for more information on what happens next

It's a bit of cumbersome way to do it though - I could really do with a way of executing the SQL directly as opposed to dumping it in a query and running that!

 

[cprobertson1]

The time was 0734: I had already drank too much coffee; the programming jitters had begun... the morning had passed in a haze; line after line; function after function; debug after debug...

..and then, I solved the problem that was preventing my code from running... a missing quotation mark in line 47...

It was like university all over again: a week's work lost because of a typo - a week of my life forever relegated to the distant past: tempus rex rerum; time devours everyth-the point is it works now.

Yeah, mornings REALLY aren't my thing


I've now got a strange problem that I'm struggling to diagnose: one ONE (and only one) of the pages from my database - whenever I print it (using the parametric search function) - it splatters a debug statement over the printout.

"Error Name: /syntaxerror
Offending command: -null-
Operand Stack:
931
3910
3"

- spaced out with a white background over the printout - obscuring any text and row banding behind it.

Reckon its a problem with the printer or with access? [if it's with access I'll start a new thread xD - I'm struggling to diagnose the source of the issue at the moment xD]

--EDIT--
By printing it to a pdf or oxps file then printing it it still returns the error - I'm assuming it's a printer problem - time to pester the IT dept