Using two combo boxes to generate report (1 Viewer)

Stoic

Registered User.
Local time
Today, 14:22
Joined
May 15, 2013
Messages
24
Hi,
I have a database with a table name tblTeachersProfile and a report format based on the table. I also have a form with two combo boxes name cboState and cboCounty base on field from the table and a control button called Get. Now, I would like to make selection from the two combo boxes and generate a report after updating both combo boxes based on my selections.

This is what I have but it is not working:
for my Get button

Code:
DoCmd.OpenReport "rptInServiceIndividualSchoolAndTeachersInformation", acViewReport, , "strCounty = '" & Me.cboState & "'" And "strDistrict = '" & Me.cboRegion & "'";

I will appreciate any assistance.
Thanks,
Stoic
 

Mihail

Registered User.
Local time
Today, 16:22
Joined
Jan 22, 2011
Messages
2,373
Try this:
Code:
Dim Filter As String
     Filter = "strCounty =" & Me.cboState & " And strDistrict =" & Me.cboRegion
     DoCmd.OpenReport "rptInServiceIndividualSchoolAndTeachersInformation", acViewReport, , Filter
By the way: It is strCounty or strCountry ?
 

Stoic

Registered User.
Local time
Today, 14:22
Joined
May 15, 2013
Messages
24
It is strCounty
 

Stoic

Registered User.
Local time
Today, 14:22
Joined
May 15, 2013
Messages
24
It seems to work but requesting a parameter value; normally, this is just simple if it will requst for parameter value, but that is not my intend. I would prefer if it runs the report directly based on the selections.
 

Mihail

Registered User.
Local time
Today, 16:22
Joined
Jan 22, 2011
Messages
2,373
If you will say the name(s) for requested parameter(s)...
 

Stoic

Registered User.
Local time
Today, 14:22
Joined
May 15, 2013
Messages
24
I selected from cboState "Montserrado" and selected from cboRegion "Greater Montoria" and two dialog boxes are requesting for the selected parameters ( Montserrado and Monrovia)
 

Mihail

Registered User.
Local time
Today, 16:22
Joined
Jan 22, 2011
Messages
2,373
Yep. I have forget that this are strings, not numbers. Sorry
Filter = "strCounty = """ & Me.cboState & """ And strDistrict = """ & Me.cboRegion & """"
Hope this time will work. I hate SQL !!!!!!!
 

Stoic

Registered User.
Local time
Today, 14:22
Joined
May 15, 2013
Messages
24
Thanks Mihail, sorry about the SQL thing, but yes it did worked.
Thanks again for your support
 

Mihail

Registered User.
Local time
Today, 16:22
Joined
Jan 22, 2011
Messages
2,373
You are welcome. Is OK with SQL, I must learn it.
 

Stoic

Registered User.
Local time
Today, 14:22
Joined
May 15, 2013
Messages
24
One more thing, does that mean I can add more combo boxes and follow the same partern?
 

Stoic

Registered User.
Local time
Today, 14:22
Joined
May 15, 2013
Messages
24
Thanks again Mihail, my database is getting together by your assistance. I am trying to include an 'If' statement that in the case where only one or tow options(combo box leaving the other combo boxes blank) is selection, the report should run the query for the selected combo items.

Code:
Dim Filter As String
 
If Me.cboState = "" Then
 
Filter = "strCounty = """ & Me.cboState & """ And strDistrict = """ & Me.cboRegion & """"
Hope this time will work. I hate SQL !!!!!!! 
 
Else
Filter = "strCounty = """ & Me.cboState & """ And strDistrict = """ & Me.cboRegion & """"
Hope this time will work. I hate SQL !!!!!!!
EndIf

I am not sure about this but it is not working.
Please assist.
Stoic
 

Mihail

Registered User.
Local time
Today, 16:22
Joined
Jan 22, 2011
Messages
2,373
Try this:
Code:
Dim strCounty as String, strDistrict As String

strCounty = NZ(Me.cboState,"*")
strDistrict = NZ(Me.cboRegion,"*")
  Filter = "strCounty = """ & strCounty  & """ And strDistrict = """ & strDistrict  & """"
... and note, please, that "Hope this time will work. I hate SQL !!!!!!!" is a comment, not a statement :)
 

Stoic

Registered User.
Local time
Today, 14:22
Joined
May 15, 2013
Messages
24
Of course I didn't take it as serious, I know you were just make a statement, and I can't hold you for that.
The code doesn't seem to be working as it is still running as the previous code.
Maybe I am not placing it correctly. I added a couple more lines and they are working as I desire. This what I have:

Code:
Private Sub cmdGet_Click()
Dim strCounty As String, strDistrict As String
Dim Filter As String
 If Me![cboState] = "--select county--" Then Exit Sub
 If IsNull(Me![cboState]) Then Exit Sub
 If Me![cboRegion] = "--select region--" Then Exit Sub
 If IsNull(Me![cboRegion]) Then Exit Sub
 
     strCounty = Nz(Me.cboState, "*")
     strDistrict = Nz(Me.cboRegion, "*")
     Filter = "strCounty = """ & strCounty & """ And strDistrict = """ & strDistrict & """"
     'Filter = "strCounty = """ & Me.cboState & """ And strDistrict = """ & Me.cboRegion & """"
     DoCmd.OpenReport "rptInServiceIndividualSchoolAndTeachersInformation", acViewReport, , Filter
     
     cboState = "--select county--"
     cboRegion = "--select region--"
End Sub

Thanks
 

Mihail

Registered User.
Local time
Today, 16:22
Joined
Jan 22, 2011
Messages
2,373
I hate SQL first of all because need a very fine tune especially when is constructed using VBA.
And because the compiler do not find any mistake and design time.
And because, at run time, the messages sound like that: "I don't like this code. Make changes, please !"
And because....

My practice is to place a Debug.Print SQL after my SQL string (Filter in your case), and a STOP.
This allow me to see the SQL string in the Immediate window and to add/remove quotes, parenthesis, spaces etc.

About your code... if it do the job, is OK.
Cheers !
 

Users who are viewing this thread

Top Bottom