If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combine combo boxes with date range
Hi,
I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub Thank you very much in advance, Kay |
#2
|
|||
|
|||
Combine combo boxes with date range
Kay -
To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub Thank you very much in advance, Kay |
#3
|
|||
|
|||
Combine combo boxes with date range
Thanks Daryl. I’m very new to vba. I replaced what I thought was the
made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub Thank you very much in advance, Kay |
#4
|
|||
|
|||
Combine combo boxes with date range
AccessKay -
Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub Thank you very much in advance, Kay |
#5
|
|||
|
|||
Combine combo boxes with date range
Daryl,
VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub Thank you very much in advance, Kay |
#6
|
|||
|
|||
Combine combo boxes with date range
AccessKay -
There are two ways to do the If/Then statements. If there is only one statement to execute in the 'true' and optional 'else' clauses, then you can put them on one line, without an End If: If test Then result Else else result The more common way allows for multiple statements to be executed in the 'true' case and in the optional 'else' case. This format requires the End If (so it knows when the block of statements ends). If test Then result block Else else block End If Your statement could be either If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If '(Error message) or (this would be all on one line - the copy/paste may not show it that way) If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" -- Daryl S "AccessKay" wrote: Daryl, VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub Thank you very much in advance, Kay |
#7
|
|||
|
|||
Combine combo boxes with date range
Daryl,
I got rid of the End If and put it all on one line…no more red. I appreciate the additional information. I think I understand but maybe not because I now have a new error message but I have no more red in my vba code and the bugger is not popping up so I’m not sure. The error message caption is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the field “l” referred to in your expression. I looked all around for an “l” but didn’t see one. I REALLY appreciate your patience. I just have to be close to getting this to work. Any ideas about how to fix this error? Many, many thanks, Kay "Daryl S" wrote: AccessKay - There are two ways to do the If/Then statements. If there is only one statement to execute in the 'true' and optional 'else' clauses, then you can put them on one line, without an End If: If test Then result Else else result The more common way allows for multiple statements to be executed in the 'true' case and in the optional 'else' case. This format requires the End If (so it knows when the block of statements ends). If test Then result block Else else block End If Your statement could be either If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If '(Error message) or (this would be all on one line - the copy/paste may not show it that way) If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" -- Daryl S "AccessKay" wrote: Daryl, VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub Thank you very much in advance, Kay |
#8
|
|||
|
|||
Combine combo boxes with date range
AccessKay -
Can you run the report by itself (rptLaborODC)? If not, then work on the report until it works by itself. Then work on the strWhere. It should be in your immediate window if you have the code open while you run the report from the form. There could be a typo or other issue there. If you can't figure it out, then post both the strWhere (copy/paste from the immediate window), and the SQL for your report's record source. -- Daryl S "AccessKay" wrote: Daryl, I got rid of the End If and put it all on one line…no more red. I appreciate the additional information. I think I understand but maybe not because I now have a new error message but I have no more red in my vba code and the bugger is not popping up so I’m not sure. The error message caption is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the field “l” referred to in your expression. I looked all around for an “l” but didn’t see one. I REALLY appreciate your patience. I just have to be close to getting this to work. Any ideas about how to fix this error? Many, many thanks, Kay "Daryl S" wrote: AccessKay - There are two ways to do the If/Then statements. If there is only one statement to execute in the 'true' and optional 'else' clauses, then you can put them on one line, without an End If: If test Then result Else else result The more common way allows for multiple statements to be executed in the 'true' case and in the optional 'else' case. This format requires the End If (so it knows when the block of statements ends). If test Then result block Else else block End If Your statement could be either If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If '(Error message) or (this would be all on one line - the copy/paste may not show it that way) If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" -- Daryl S "AccessKay" wrote: Daryl, VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub Thank you very much in advance, Kay |
#9
|
|||
|
|||
Combine combo boxes with date range
Daryl,
The report runs okay if I have the form open. If I don’t, then it asks for parameters. I have this feeling that it has something to do with the first strWhere. This is what I have right now: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = " & Forms!frmDialogBox!cboGroup.column(0) & "'" I noticed I had a missing ' after [Group] = from the version you sent me. If I put this in, the text turns red and I get a syntex error. I'm thinking I'm missing something else, but I can't tell what it is. Also, the last "'" at the end is green. I can send what you requested last time but I was hoping you could identify my error here. Thanks, Kay "Daryl S" wrote: AccessKay - Can you run the report by itself (rptLaborODC)? If not, then work on the report until it works by itself. Then work on the strWhere. It should be in your immediate window if you have the code open while you run the report from the form. There could be a typo or other issue there. If you can't figure it out, then post both the strWhere (copy/paste from the immediate window), and the SQL for your report's record source. -- Daryl S "AccessKay" wrote: Daryl, I got rid of the End If and put it all on one line…no more red. I appreciate the additional information. I think I understand but maybe not because I now have a new error message but I have no more red in my vba code and the bugger is not popping up so I’m not sure. The error message caption is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the field “l” referred to in your expression. I looked all around for an “l” but didn’t see one. I REALLY appreciate your patience. I just have to be close to getting this to work. Any ideas about how to fix this error? Many, many thanks, Kay "Daryl S" wrote: AccessKay - There are two ways to do the If/Then statements. If there is only one statement to execute in the 'true' and optional 'else' clauses, then you can put them on one line, without an End If: If test Then result Else else result The more common way allows for multiple statements to be executed in the 'true' case and in the optional 'else' case. This format requires the End If (so it knows when the block of statements ends). If test Then result block Else else block End If Your statement could be either If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If '(Error message) or (this would be all on one line - the copy/paste may not show it that way) If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" -- Daryl S "AccessKay" wrote: Daryl, VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, |
#10
|
|||
|
|||
Combine combo boxes with date range
AccessKay -
If the query is pulling information from the form (as yours is), then the form must be open for the query to no ask for the values. I put the missing single-quote in on the second line below - if you get green text in an Access code window, then it is treated as a comment and ignored by the compiler. You usually put comments in by putting a single quote and then the comment. If the single quote is not a comment, but part of the line, then there is probably something missing (as you said, the matching single quote). strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & Forms!frmDialogBox!cboGroup.column(0) & "'" Hope this helps! -- Daryl S "AccessKay" wrote: Daryl, The report runs okay if I have the form open. If I don’t, then it asks for parameters. I have this feeling that it has something to do with the first strWhere. This is what I have right now: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = " & Forms!frmDialogBox!cboGroup.column(0) & "'" I noticed I had a missing ' after [Group] = from the version you sent me. If I put this in, the text turns red and I get a syntex error. I'm thinking I'm missing something else, but I can't tell what it is. Also, the last "'" at the end is green. I can send what you requested last time but I was hoping you could identify my error here. Thanks, Kay "Daryl S" wrote: AccessKay - Can you run the report by itself (rptLaborODC)? If not, then work on the report until it works by itself. Then work on the strWhere. It should be in your immediate window if you have the code open while you run the report from the form. There could be a typo or other issue there. If you can't figure it out, then post both the strWhere (copy/paste from the immediate window), and the SQL for your report's record source. -- Daryl S "AccessKay" wrote: Daryl, I got rid of the End If and put it all on one line…no more red. I appreciate the additional information. I think I understand but maybe not because I now have a new error message but I have no more red in my vba code and the bugger is not popping up so I’m not sure. The error message caption is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the field “l” referred to in your expression. I looked all around for an “l” but didn’t see one. I REALLY appreciate your patience. I just have to be close to getting this to work. Any ideas about how to fix this error? Many, many thanks, Kay "Daryl S" wrote: AccessKay - There are two ways to do the If/Then statements. If there is only one statement to execute in the 'true' and optional 'else' clauses, then you can put them on one line, without an End If: If test Then result Else else result The more common way allows for multiple statements to be executed in the 'true' case and in the optional 'else' case. This format requires the End If (so it knows when the block of statements ends). If test Then result block Else else block End If Your statement could be either If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If '(Error message) or (this would be all on one line - the copy/paste may not show it that way) If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" -- Daryl S "AccessKay" wrote: Daryl, VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date range” and followed them which were very good because I almost got it to work. It’s asking me for parameters related to the above dialog box for cboCategory and cboGroup. I’d like to combine the two. Is this possible with what I’ve already created or should I be doing something else? Can anyone help me with this please? Here is Allen’s code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview If IsDate(Me.txtStartDate) Then |
|
Thread Tools | |
Display Modes | |
|
|