Splitting sql in vba

[sukhjinder]

Sir,




I have made three option group as a search criterion. No1 and No 2 group is clear and simple with option. for No3 i am using a combo box with dropdown from table 3. On search click it shows the result displayed in the list box based upon the rowsource using all the three criteria. Further the No 3 table is having the records linked other options No1 and No 2. Table no 3 have two column autoid and feild1. In the feild1 column i created an id =1 feild=all so that if i search it shows all the items.

I want that if me.cbooptionselect3=1 then
me.lstbox.rowsource=select feild1,feild2......... & _
where feild3=" & me.cbooptionselect3 & "
sort by -------

is there is any command or rule so that i split the sql into parts like
if me.cbooptionselect3=1 then
me.lstbox.rowsource=select feild1,feild2.........
ABCD
sort by -------

PRIVATE SUB ABCD
abcd=where feild3>52 and feild3<98
END SUB

I wand to use abcd number of times in the VBA.

Request advice on this

[orange]

Suggest you just describe your need in plain, simple English --not quasi-code.

You might consider the Select Case construct

[sukhjinder]

Private Sub cmdsearch_Click()

Select Case True
Case Me.cboskill.Value = 1
Me.lstbox.RowSource = "SELECT tbl_employe.empID_PK, tbl_level.level, tbl_employe.name, tbl_skill.skill, tbl_office.office, tbl_level.levelID_PK, tbl_skill.skillID_PK, tbl_office.officeID_PK " & _
"FROM tbl_skill INNER JOIN (tbl_office INNER JOIN (tbl_level INNER JOIN tbl_employe ON tbl_level.levelID_PK = tbl_employe.levelID_FK) ON tbl_office.officeID_PK = tbl_employe.officeID_FK) ON tbl_skill.skillID_PK = tbl_employe.skillID_FK;"
Case Me.cboskill.Value <> 1
Me.lstbox.RowSource = "SELECT tbl_employe.empID_PK, tbl_level.level, tbl_employe.name, tbl_skill.skill, tbl_office.office, tbl_level.levelID_PK, tbl_skill.skillID_PK, tbl_office.officeID_PK" & _
"FROM tbl_skill INNER JOIN (tbl_office INNER JOIN (tbl_level INNER JOIN tbl_employe ON tbl_level.levelID_PK = tbl_employe.levelID_FK) ON tbl_office.officeID_PK = tbl_employe.officeID_FK) ON tbl_skill.skillID_PK = tbl_employe.skillID_FK" & _
"WHERE ((tbl_skill.skillID_PK)=" & Me.cboskill.Value & ");"
Case Else
MsgBox "ALL THE BEST"
End Select
End Sub



This code is not working where the combo box value is not equal to 1.dborg.zip

[orange]

Tell us in plain English what your database is about. What exactly is the business/purpose of this database?
As someone who doesn't know you or the database, how am I suppose to interpret your form?
What exactly do you want the form user to do?
What should be selected?
What is the expected result?
What is the actual result?
How do expected and actual differ?

[sukhjinder]

Tell us in plain English what your database is about. What exactly is the business/purpose of this database?
As someone who doesn't know you or the database, how am I suppose to interpret your form?
What exactly do you want the form user to do?
What should be selected?
What is the expected result?
What is the actual result?
How do expected and actual differ?

i am using SQL statement as the record source of the list box.
I want that there is a print button on the from on selecting it will open the report.
I am expecting that the newly opened report should have the same record source as the list box of the opened form.
How to pass the parameter of form record source into report record source?.

[orange]

Hmm?So you have a form with a recordsource, and on that form is a listbox.
See this for listbox rowsource info.

Do you plan to select something(s) in the list box and then click the report form?
If so, then the Click event will have to include some code to update/create/modify the record source of the report with the listbox selected item(s).
Do not count on the report's record source to do sorting or grouping--reports have their own sort and group options.

[sukhjinder]

No i have not plan to select something in the list box. i just want that all the items in the list box will move in the report by selecting button print on the form.

[June7]

Build a report and apply the 3 parameters to report when opening. Review http://allenbrowne.com/ser-62.html

The second SQL statement needs a space at the end of each continued line.

[davegri]

Private Sub cmdsearch_Click()

Select Case True
    Case Me.cboskill.Value = 1
        Me.lstbox.RowSource = "SELECT tbl_employe.empID_PK, tbl_level.level, tbl_employe.name, tbl_skill.skill, tbl_office.office, tbl_level.levelID_PK, tbl_skill.skillID_PK, tbl_office.officeID_PK " & _
        "FROM tbl_skill INNER JOIN (tbl_office INNER JOIN (tbl_level INNER JOIN tbl_employe ON tbl_level.levelID_PK = tbl_employe.levelID_FK) ON tbl_office.officeID_PK = tbl_employe.officeID_FK) ON tbl_skill.skillID_PK = tbl_employe.skillID_FK;"
    Case Me.cboskill.Value <> 1
        Me.lstbox.RowSource = "SELECT tbl_employe.empID_PK, tbl_level.level, tbl_employe.name, tbl_skill.skill, tbl_office.office, tbl_level.levelID_PK, tbl_skill.skillID_PK, tbl_office.officeID_PK " & _
        "FROM tbl_skill INNER JOIN (tbl_office INNER JOIN (tbl_level INNER JOIN tbl_employe ON tbl_level.levelID_PK = tbl_employe.levelID_FK) ON tbl_office.officeID_PK = tbl_employe.officeID_FK) ON tbl_skill.skillID_PK = tbl_employe.skillID_FK " & _
        "WHERE ((tbl_skill.skillID_PK)=" & Me.cboskill.Value & ");"
    Case Else
        MsgBox "ALL THE BEST"
End Select
End Sub

Add the spaces at the red...

[sukhjinder]

Build a report and apply the 3 parameters to report when opening.

the report is working fine as a separate identity. I want to pass three argument as tempVar from list box of form to open a report where the rowsource of report after opening will be similar to that of forms!listbox.

[June7]

"No i have not plan to select something in the list box. i just want that all the items in the list box will move in the report by selecting button print on the form."

The report would have RecordSource that is the same unfiltered query or SQL statement as the listbox. Then apply same filter criteria to the report when it opens.

Unless the listbox is used to make selection(s) that will be applied to report filter, it really has no relevance to this issue and is simply confusing anyone reading your question.

I've never utilized TempVars. I also never use dynamic parameterized queries. Method described in Allen Browne link is my preference.

[orange]

sukhjinder,

Why tempVars? If you are selecting from the listbox on a form, could you not take those selections and modify the SQL for the reports record source? Perhaps OpenArgs?
Perhaps you could post a copy of the database.

[sukhjinder]

"No i have not plan to select something in the list box. i just want that all the items in the list box will move in the report by selecting button print on the form."

The report would have RecordSource that is the same unfiltered query or SQL statement as the listbox. Then apply same filter criteria to the report when it opens.

Unless the listbox is used to make selection(s) that will be applied to report filter, it really has no relevance to this issue and is simply confusing anyone reading your question.

I've never utilized TempVars. I also never use dynamic parameterized queries. Method described in Allen Browne link is my preference.

Thanks a lot you can mark it as solved query.