422,946 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,946 IT Pros & Developers. It's quick & easy.

Multiple Combo Boxes to setup a report

P: 3
I am not very good with VBA but I will try to get all the details in here. I am trying to use 5 combo boxes and 2 text boxes to look up and generate a report based off of those selections. I want it to generate a report as well once I click on the command button. I have the 2 text boxes setup as from and to date boxes that I pulled off of another site. I included attachments to try to clarify this a little bit.

The Date Box jpg is the code that i pulled from another site and I am hoping to incorporate the rest of the code into that button. Please help, I've been trying to figure this out for months.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Submit_Click()
  2.  
  3. On Error GoTo Err_Handler      
  4.     Dim strReport As String
  5.     Dim strDateField As String
  6.     Dim strWhere As String
  7.     Dim lngView As Long
  8.     Const strcJetDate = "\#mm\/dd\/yyyy\#"  
  9.  
  10.     strReport = "Search"            
  11.     strDateField = "[Date]"       
  12.     lngView = acViewReport             
  13.  
  14.     If IsDate(Me.startdate) Then strWhere = "(" & strDateField & " >= " & Format(Me.startdate, strcJetDate) & ")"
  15.     End If
  16.     If IsDate(Me.enddate) Then
  17.         If strWhere <> vbNullString Then
  18.             strWhere = strWhere & " AND "
  19.         End If
  20.         strWhere = strWhere & "(" & strDateField & " < " & Format(Me.enddate + 1, strcJetDate) & ")"
  21.     End If
  22.  
  23.    If CurrentProject.AllReports(strReport).IsLoaded Then
  24.         DoCmd.Close acReport, strReport
  25.     End If
  26.  
  27. Debug.Print strWhere  
  28.  
  29.     DoCmd.OpenReport strReport, lngView, , strWhere
  30.  
  31. Exit_Handler:
  32.     Exit Sub
  33.  
  34. Err_Handler:
  35.     If Err.Number <> 2501 Then
  36.         MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
  37.     End If
  38.     Resume Exit_Handler
  39.  
  40. End Sub
Attached Images
File Type: jpg Date Box VBA Code.jpg (56.2 KB, 6 views)
File Type: jpg Search Form.jpg (47.8 KB, 7 views)
2 Weeks Ago #1
Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,202
Firstly, welcome to Bytes.

Your query should have been posted under Access, not Visual Basic.

Due to the limitations of the Website, images are not all that good, so code should always be pasted between the "[Code/]" Tags

If the image of the form showed only the form, not the surrounding area, it might be readable.

That said, will ALL the Combo boxes and dates be filled in. In other words, if only the dates are filled in, do you want your report to show ALL the data between those two dates?

Phil
2 Weeks Ago #2

P: 3
I apologize for the post. Not all of the boxes will be filled in, it will only pull from the filled in boxes and display those records. It is for aircraft records so if the dates are filled in as well as the aircraft number I want it to pull every record for that aircraft between those dates.
2 Weeks Ago #3

PhilOfWalton
Expert 100+
P: 1,202
OK, that clarifies things a bit.

I realise that you are new to Bytes, but it essential that you answer questions as accurately as you can, and respond to requests.

I asked for your code and a clearer image of your form, but got neither. We can't help without the requested info.

Phil
2 Weeks Ago #4

P: 3
Hopefully that helps a little bit.
2 Weeks Ago #5

PhilOfWalton
Expert 100+
P: 1,202
Yes, most helpful.

You're om the right track.

All you need to do is check if each Combo box has a value, and if so, add it to the strWhere.

Again you already have the technique of checking whether the strWhere is NOT Null in which case, you add the " AND ", otherwise it starts of with a "WHERE "

Don't forget, if the ComboBox has a string value in it (and I suspect all do other than Document #), then the string needs to be surrounded with quotes.

Something like
Expand|Select|Wrap|Line Numbers
  1. " AND Aircraft = " & Chr$(34) & CboAircraft & Chr$(34)
  2.  
Depending on which column is used to hold the name of the Aircraft, the code may have to say

Expand|Select|Wrap|Line Numbers
  1. " AND Aircraft = " & Chr$(34) & CboAircraft.Column(1) & Chr$(34)
  2.  
One thing you can do to check how you are progressing, is to create a query, and past the strWhere from your Debug Window into the query's Criteria.

Hope that gets you started.

Phil
2 Weeks Ago #6

Post your reply

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