Building Query from 2 list boxes

[philsomerville]

Hi

I am really at the end of my teather with this problem so i really hope someone here can find a solution.

I have 2 tables; Client (Client general info, defined by their location), ClientHardware (Info on the hardware a client has and also it's condition).

I need to be able to select one or more clients and display one or more conditions of their hardware, e.g. London, York, Bury + Red, Amber, Green condition.

After the Query is working right i will need to output it to a report through a button on the form.

I attempted adapting This Method but adding another list and query just resulted in the report showing the all the records of the selected client (e.g. london) then all the records with the selected condition (e.g. bad) it would be ok if i could merge the list box selections into one query but right now it looks like this

MyDB.QueryDefs.Delete "qry ClientName"
Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL)

MyDB.QueryDefs.Delete "qry RAGType"
Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL2)

Is it possible to put the variables in strSQL2 into the creation of qry ClientName somehow?

Any help, large or small will be appreciated as I'm really at a dead end with this.


Regards


Phil

 

[jdraw]

Hi

I am really at the end of my teather with this problem so i really hope someone here can find a solution.

I have 2 tables; Client (Client general info, defined by their location), ClientHardware (Info on the hardware a client has and also it's condition).

I need to be able to select one or more clients and display one or more conditions of their hardware, e.g. London, York, Bury + Red, Amber, Green condition.

After the Query is working right i will need to output it to a report through a button on the form.

I attempted adapting This Method but adding another list and query just resulted in the report showing the all the records of the selected client (e.g. london) then all the records with the selected condition (e.g. bad) it would be ok if i could merge the list box selections into one query but right now it looks like this

MyDB.QueryDefs.Delete "qry ClientName"
Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL)

MyDB.QueryDefs.Delete "qry RAGType"
Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL2)

Is it possible to put the variables in strSQL2 into the creation of qry ClientName somehow?

Any help, large or small will be appreciated as I'm really at a dead end with this.


Regards


Phil

Phil,
I think you should look at this site:
http://www.fontstuff.com/siteindex.htm#access

and also this reference to database/ table structure:
http://r937.com/relational.html

Good luck

 

[gemma-the-husky]

can i ask if you are new to access?

i realy think you dont need (or want) to be messing with querydefs.

you should be able to achieve what you are trying to do with simple straightforward stored queries.

 

[philsomerville]

Your right, i'm returning to access after a good few years away and feel like im jumping right in the deep end.

Stored quries is something initially considered but i do not know how to pass the listbox selection fields to the search critera, any suggestions?

 

[neileg]

To refer to a control in a form as a criterion, use this syntax:
[Forms]![MyFormName]![MyControlName]

 

[Jon K]

To use multi select list boxes as query criteria, you have to modify the QueryDef on the fly with code similar to that in your link.

See the attached database.
.

 

[philsomerville]

Jon i can't say how thankful i am for that sample you made, I was able to adapt it to mine in about 10 mins and it works perfectly.

Would you accept a donation for the effort you put into this as a thank you?


Regards


Phil

 

[Jon K]

Phil I'm glad you find the sample helpful.

Thanks for your kind donation offer. It's really not much effort that I put into the sample. Your feedback that it worked is already good enough. Many of our suggestions/solutions just went without feedback or a word of thanks in return.

Jon
.

 

[philsomerville]

I had been searching, asking and trying myself to find a solution to this problem for a good week, so your small effort was a big help. I was just about to give up on it too.

My offer still stands should you change your mind, it's the least i could do.

 

[filfil]

thanks a lot Jon K.. I also found the sample very helpful for my case.