By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Creating query dynamically in VBA code

IGGI
P: 5
Looks like this is the best place for the right Answers.

Got a database that stores users skills, "Leader", "Investigator","Engineering" ect
in a "personnel" table, A form called "Search Query" with uncontrolled Check boxes namely "Cleader","CInvestigator","CEngineering" and so on. When the users checks some of these boxes, I need to build a query based on the "Personnel" Table, plus all form selections to display all fields that meet the criteria.

Example is If the user selects Cleader and Cengineering from the Form(Search Query), all records that match these two selections will be displayed.

Tried using a standard query but the results aren't what I expect.

Table Personnel
- Leader Y/N checked box
- Investigator Y/N checked box
- Engineering Y/N checked box

Form Search Query - Uncontrolled box with multiply Check boxes called
- Cleader Y/N checked box
- CInvestigator Y/N checked box
- CEngineering Y/N checked box

Running on Access XP, and I'm a Novice VBA writer, but willing to have a go.

Thanks

IGGI....
Jul 19 '07 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 344
Looks like this is the best place for the right Answers.

Got a database that stores users skills, "Leader", "Investigator","Engineering" ect
in a "personnel" table, A form called "Search Query" with uncontrolled Check boxes namely "Cleader","CInvestigator","CEngineering" and so on. When the users checks some of these boxes, I need to build a query based on the "Personnel" Table, plus all form selections to display all fields that meet the criteria.

Example is If the user selects Cleader and Cengineering from the Form(Search Query), all records that match these two selections will be displayed.

Tried using a standard query but the results aren't what I expect.

Table Personnel
- Leader Y/N checked box
- Investigator Y/N checked box
- Engineering Y/N checked box

Form Search Query - Uncontrolled box with multiply Check boxes called
- Cleader Y/N checked box
- CInvestigator Y/N checked box
- CEngineering Y/N checked box

Running on Access XP, and I'm a Novice VBA writer, but willing to have a go.

Thanks

IGGI....
I can do this VBA, and give some hints on how to do it. On your Search Query, have a button that says 'Build Query' say cmdBuild. Go into the on_click event builder for the button and try the following
Expand|Select|Wrap|Line Numbers
  1. dim strSQL as string, strFilter as string
  2. strFilter=""
  3. if CLeader then strFilter="Leader=true"
  4. if CInvestigator then
  5.   if len(strFilter)>0 then strFilter=strFilter & " AND "
  6.   strFilter=strFilter & "Investigator=true"
  7. end if
  8. if CEngineering then
  9.   if len(strFilter)>0 then strFilter=strFilter & " AND "
  10.   strFilter=strFilter & "Engineering =true"
  11. end if
  12. 'repeat for each check box then
  13. if strFilter="" then
  14.   strSQL="select * from [Table Personnel];"
  15. else
  16.   strSQL="select * from [Table Personnel] Where " & strFilter $ ";"
  17. end if
  18.  
  19.  
strSQL now has the query statement you need, you can either make it the record source of another form, or open a recordset based on it, depending what you want to do with the query
Jul 19 '07 #2

IGGI
P: 5
I can do this VBA, and give some hints on how to do it. On your Search Query, have a button that says 'Build Query' say cmdBuild. Go into the on_click event builder for the button and try the following
Expand|Select|Wrap|Line Numbers
  1. dim strSQL as string, strFilter as string
  2. strFilter=""
  3. if CLeader then strFilter="Leader=true"
  4. if CInvestigator then
  5.   if len(strFilter)>0 then strFilter=strFilter & " AND "
  6.   strFilter=strFilter & "Investigator=true"
  7. end if
  8. if CEngineering then
  9.   if len(strFilter)>0 then strFilter=strFilter & " AND "
  10.   strFilter=strFilter & "Engineering =true"
  11. end if
  12. 'repeat for each check box then
  13. if strFilter="" then
  14.   strSQL="select * from [Table Personnel];"
  15. else
  16.   strSQL="select * from [Table Personnel] Where " & strFilter $ ";"
  17. end if
  18.  
  19.  
strSQL now has the query statement you need, you can either make it the record source of another form, or open a recordset based on it, depending what you want to do with the query
Thanks Lysander

Got it going OK, but when I try to Execute the command to open a query based on the results, I get an error message saying

MS cannot find the object '"select * from [Personnel] Where " & strFilter & " ; " line of the code,

This is what I tried to run, probable the wrong statement, want to open a form or report based on the results..(Here I tried to open the Query)


DoCmd.OpenQuery strSQl, acViewNormal

Thanks

IGGI
Jul 20 '07 #3

Expert 100+
P: 344
Thanks Lysander

Got it going OK, but when I try to Execute the command to open a query based on the results, I get an error message saying

MS cannot find the object '"select * from [Personnel] Where " & strFilter & " ; " line of the code,

This is what I tried to run, probable the wrong statement, want to open a form or report based on the results..(Here I tried to open the Query)


DoCmd.OpenQuery strSQl, acViewNormal

Thanks

IGGI
You are correct, that will not work as strSQL is not a query, it is the text that a query would use.

What I can suggest is this.

Design a form based on your query, with or without filters, and get it to display the data. Then put your 'Search Query' check boxes etc in the header of the form. When the user clicks on cmdBuild, use the code as before and when strSQL has been built, use the following
Expand|Select|Wrap|Line Numbers
  1. me.recordsource=strSQL
  2. me.refresh
  3.  
If you want to run an actual query, you would need to create a querydef. I would need to check how to do that, can't remember of the top of my head.

If you want to run a report, then again design the report with NO FILTERS and you can open the report passing strFilter as the criteria
i.e.
Expand|Select|Wrap|Line Numbers
  1.             DoCmd.OpenReport "myreport", acNormal, , strFilter
  2.  
  3.  
Jul 20 '07 #4

IGGI
P: 5
Thanks for all your help Lysander, Still not getting the required results.

I'll try and give the QueryDef ago, to see if i can get the required results.

IGGI





You are correct, that will not work as strSQL is not a query, it is the text that a query would use.

What I can suggest is this.

Design a form based on your query, with or without filters, and get it to display the data. Then put your 'Search Query' check boxes etc in the header of the form. When the user clicks on cmdBuild, use the code as before and when strSQL has been built, use the following
Expand|Select|Wrap|Line Numbers
  1. me.recordsource=strSQL
  2. me.refresh
  3.  
If you want to run an actual query, you would need to create a querydef. I would need to check how to do that, can't remember of the top of my head.

If you want to run a report, then again design the report with NO FILTERS and you can open the report passing strFilter as the criteria
i.e.
Expand|Select|Wrap|Line Numbers
  1.             DoCmd.OpenReport "myreport", acNormal, , strFilter
  2.  
  3.  
Jul 25 '07 #5

Expert 100+
P: 344
Thanks for all your help Lysander, Still not getting the required results.

I'll try and give the QueryDef ago, to see if i can get the required results.

IGGI
Are you trying to open a query, a form or a report with the selected data?
Jul 25 '07 #6

IGGI
P: 5
It doesn't matter which I try the results aren't working. When I do it using the form results, or using the QueryDef, it looks like as the Checkbox or Combo box I now am testing, the query seems to want not to give me what I want..???


Here is a script I wrote: Basic one

strSQL = "SELECT tbl_Personnel.* " & _
"FROM tbl_Personnel " & _
"WHERE tbl_Personnel.Leader= " & Me.CLeader.Value & " " & _
"AND tbl_Personnel.Investigator= " & Me.CInvestigator.Value & " " & _

"AND tbl_Personnel.Specialist= " & Me.CSpecialist.Value & " " & _
"AND tbl_Personnel.[Flight Ops]= " & Me.CFO.Value & " " & _

"AND tbl_Personnel.Engineering= " & Me.CEng.Value & " " & _
"AND tbl_Personnel.Cabin= " & Me.CCab.Value & " " & _
"AND tbl_Personnel.[Site Coordination]= " & Me.CSc.Value & " " & _
"AND tbl_Personnel.Legal= " & Me.CLeg.Value & " " & _
"AND tbl_Personnel.Medical= " & Me.Cme.Value & " " & _
"AND tbl_Personnel.[Human Factors]= " & Me.CHF.Value & " " & _
"AND tbl_Personnel.[Dangerous Goods]= " & Me.CDG.Value & " " & _
"AND tbl_Personnel.Security= " & Me.CSec.Value & " " & _
"AND tbl_Personnel.Admin= " & Me.CAdm.Value & " " & _
"AND tbl_Personnel.IT= " & Me.CIT.Value & " " & _
"AND tbl_Personnel.Media= " & Me.CMedia.Value & " " & _
"AND tbl_Personnel.Interpreter= " & Me.CInt.Value & " " & _
"AND tbl_Personnel.[Aircraft Recovery (QF Eng)]= " & Me.CACR.Value & " " & _
"ORDER BY tbl_Personnel.[Organisational Unit];"

Me.C*** is now a combo box with Yes/No text in there

Can you tell me what code I need here to Ignore or insert the like * function if the combo box is empty

Target is to get all say "Leaders" with the result Yes and all other fields that haven't been selected to appear in the results.??

I'm strange or going nuts..?????

IGGI
Jul 25 '07 #7

Expert 100+
P: 344
It doesn't matter which I try the results aren't working. When I do it using the form results, or using the QueryDef, it looks like as the Checkbox or Combo box I now am testing, the query seems to want not to give me what I want..???


Here is a script I wrote: Basic one

strSQL = "SELECT tbl_Personnel.* " & _
"FROM tbl_Personnel " & _
"WHERE tbl_Personnel.Leader= " & Me.CLeader.Value & " " & _
"AND tbl_Personnel.Investigator= " & Me.CInvestigator.Value & " " & _

"AND tbl_Personnel.Specialist= " & Me.CSpecialist.Value & " " & _
"AND tbl_Personnel.[Flight Ops]= " & Me.CFO.Value & " " & _

"AND tbl_Personnel.Engineering= " & Me.CEng.Value & " " & _
"AND tbl_Personnel.Cabin= " & Me.CCab.Value & " " & _
"AND tbl_Personnel.[Site Coordination]= " & Me.CSc.Value & " " & _
"AND tbl_Personnel.Legal= " & Me.CLeg.Value & " " & _
"AND tbl_Personnel.Medical= " & Me.Cme.Value & " " & _
"AND tbl_Personnel.[Human Factors]= " & Me.CHF.Value & " " & _
"AND tbl_Personnel.[Dangerous Goods]= " & Me.CDG.Value & " " & _
"AND tbl_Personnel.Security= " & Me.CSec.Value & " " & _
"AND tbl_Personnel.Admin= " & Me.CAdm.Value & " " & _
"AND tbl_Personnel.IT= " & Me.CIT.Value & " " & _
"AND tbl_Personnel.Media= " & Me.CMedia.Value & " " & _
"AND tbl_Personnel.Interpreter= " & Me.CInt.Value & " " & _
"AND tbl_Personnel.[Aircraft Recovery (QF Eng)]= " & Me.CACR.Value & " " & _
"ORDER BY tbl_Personnel.[Organisational Unit];"

Me.C*** is now a combo box with Yes/No text in there

Can you tell me what code I need here to Ignore or insert the like * function if the combo box is empty

Target is to get all say "Leaders" with the result Yes and all other fields that haven't been selected to appear in the results.??

I'm strange or going nuts..?????

IGGI
You are not alone
I too feel going nuts about another problem of mine that I can't solve. You start blaming Microsoft for a bug in their systems. Anyways, your post raises several issues I will try to address.

First, the null or empty cboBox.

I suggest you use code like I posted in my first reply, i.e. build up the "Where" string field by field.

You can then say, for each cboXXX
Expand|Select|Wrap|Line Numbers
  1. if isNull(cboXXX) then
  2.  'do nothing
  3. else 
  4.  strFilter=strFilter & " AND "  etc
  5. end if
  6.  
Next issue is a helpful way to debug your code.

When you have finished building strSQL, put in the following line
Expand|Select|Wrap|Line Numbers
  1. debug.print strSQL
  2.  
Then click on that line and set a breakpoint. Now open the form in display mode and click on your button. The code window will open, on your debug line. Pressing F8 will run that one line of code and display your SQL statement in the immediate window. You can then cut and paste that into a query and get a better idea of what is going wrong.

Next issue.
You said the combo boxs have Yes/No text, i.e. "YES" and "NO" but the fields in the table I suspect are boolean yes/no, i.e. numbers -1 for yes and 0 for no (I think)

You must make sure that you are comparing like with like. The debug.print idea will let you find out why the SQL is not working.
Jul 25 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.