Creating query dynamically in VBA code

[IGGI]

Looks like this is the best place for the right Answers.

Got a database that stores users skills, "Leader", "Investigator", "Engineerin g" ect
in a "personnel" table, A form called "Search Query" with uncontrolled Check boxes namely "Cleader","CInv estigator","CEn gineering" 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....

[Lysander]

Looks like this is the best place for the right Answers.

Got a database that stores users skills, "Leader", "Investigator", "Engineerin g" ect
in a "personnel" table, A form called "Search Query" with uncontrolled Check boxes namely "Cleader","CInv estigator","CEn gineering" 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
[code=vba]
dim strSQL as string, strFilter as string
strFilter=""
if CLeader then strFilter="Lead er=true"
if CInvestigator then
if len(strFilter)> 0 then strFilter=strFi lter & " AND "
strFilter=strFi lter & "Investigator=t rue"
end if
if CEngineering then
if len(strFilter)> 0 then strFilter=strFi lter & " AND "
strFilter=strFi lter & "Engineerin g =true"
end if
'repeat for each check box then
if strFilter="" then
strSQL="select * from [Table Personnel];"
else
strSQL="select * from [Table Personnel] Where " & strFilter $ ";"
end if

[/code]
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

[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
[code=vba]
dim strSQL as string, strFilter as string
strFilter=""
if CLeader then strFilter="Lead er=true"
if CInvestigator then
if len(strFilter)> 0 then strFilter=strFi lter & " AND "
strFilter=strFi lter & "Investigator=t rue"
end if
if CEngineering then
if len(strFilter)> 0 then strFilter=strFi lter & " AND "
strFilter=strFi lter & "Engineerin g =true"
end if
'repeat for each check box then
if strFilter="" then
strSQL="select * from [Table Personnel];"
else
strSQL="select * from [Table Personnel] Where " & strFilter $ ";"
end if

[/code]
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

[Lysander]

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
[code=vb]
me.recordsource =strSQL
me.refresh
[/code]

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.
[code=vb]
DoCmd.OpenRepor t "myreport", acNormal, , strFilter

[/code]

[IGGI]

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
[code=vb]
me.recordsource =strSQL
me.refresh
[/code]

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.
[code=vb]
DoCmd.OpenRepor t "myreport", acNormal, , strFilter

[/code]

[Lysander]

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?

[IGGI]

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.L eader= " & Me.CLeader.Valu e & " " & _
"AND tbl_Personnel.I nvestigator= " & Me.CInvestigato r.Value & " " & _

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

"AND tbl_Personnel.E ngineering= " & Me.CEng.Value & " " & _
"AND tbl_Personnel.C abin= " & Me.CCab.Value & " " & _
"AND tbl_Personnel.[Site Coordination]= " & Me.CSc.Value & " " & _
"AND tbl_Personnel.L egal= " & Me.CLeg.Value & " " & _
"AND tbl_Personnel.M edical= " & Me.Cme.Value & " " & _
"AND tbl_Personnel.[Human Factors]= " & Me.CHF.Value & " " & _
"AND tbl_Personnel.[Dangerous Goods]= " & Me.CDG.Value & " " & _
"AND tbl_Personnel.S ecurity= " & Me.CSec.Value & " " & _
"AND tbl_Personnel.A dmin= " & Me.CAdm.Value & " " & _
"AND tbl_Personnel.I T= " & Me.CIT.Value & " " & _
"AND tbl_Personnel.M edia= " & Me.CMedia.Value & " " & _
"AND tbl_Personnel.I nterpreter= " & 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

[Lysander]

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.L eader= " & Me.CLeader.Valu e & " " & _
"AND tbl_Personnel.I nvestigator= " & Me.CInvestigato r.Value & " " & _

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

"AND tbl_Personnel.E ngineering= " & Me.CEng.Value & " " & _
"AND tbl_Personnel.C abin= " & Me.CCab.Value & " " & _
"AND tbl_Personnel.[Site Coordination]= " & Me.CSc.Value & " " & _
"AND tbl_Personnel.L egal= " & Me.CLeg.Value & " " & _
"AND tbl_Personnel.M edical= " & Me.Cme.Value & " " & _
"AND tbl_Personnel.[Human Factors]= " & Me.CHF.Value & " " & _
"AND tbl_Personnel.[Dangerous Goods]= " & Me.CDG.Value & " " & _
"AND tbl_Personnel.S ecurity= " & Me.CSec.Value & " " & _
"AND tbl_Personnel.A dmin= " & Me.CAdm.Value & " " & _
"AND tbl_Personnel.I T= " & Me.CIT.Value & " " & _
"AND tbl_Personnel.M edia= " & Me.CMedia.Value & " " & _
"AND tbl_Personnel.I nterpreter= " & 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
[code=vb]
if isNull(cboXXX) then
'do nothing
else
strFilter=strFi lter & " AND " etc
end if
[/code]
Next issue is a helpful way to debug your code.

When you have finished building strSQL, put in the following line
[code=vb]
debug.print strSQL
[/code]

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.