Pass 2 different Search Options to same Report

[kalesb]

Just got in. I'm looking at your dbase now.....


Thanks

I'll provide an update shortly to see if I can take the same approach in my app...

 

[kalesb]

This is nice. I believe I can convert to my program if you can provide some additional information. This would help me understand your code and reduce my research time in understanding your code. Any assistance would be greatly appreciated.

1. Is CurrentProject and Allforms reserved words? Please explain.

2. Is Controls a reserved word? I don't see it on frmRecords.

3. Case 1 uses option 1 which user fills in sdate and edate blocks. If value is null is it's going to load calendar date 1/1/1900? Is that a pretty good understanding of Case 1?

4. Case 2 uses option 2 which prompts user with a InputBox for start and end date. If len = 0 then load start and end values with 1/1/1900. Is that correct?

5. I should be able to use your "where clause" as is. Is that correct?

6. If I missed something, can I ask more questions?

 

[vbaInet]

This would help me understand your code and reduce my research time in understanding your code.

Now do you think I would want to reduce your research time after I've spent time doing it

1. Is CurrentProject and Allforms reserved words? Please explain.
2. Is Controls a reserved word? I don't see it on frmRecords.

This doesn't need changing so if you want to understand what these are just do some research Look at the Help available in the VBA side of Access. Once you've done some research you can ask for clarification.

3. Case 1 uses option 1 which user fills in sdate and edate blocks. If value is null is it's going to load calendar date 1/1/1900? Is that a pretty good understanding of Case 1?

Yep!

4. Case 2 uses option 2 which prompts user with a InputBox for start and end date. If len = 0 then load start and end values with 1/1/1900. Is that correct?

Yup!

5. I should be able to use your "where clause" as is. Is that correct?

Yap! As long as you name all the controls exactly the same as what I have. You don't have to name your form or report the same but make sure to change the code.

6. If I missed something, can I ask more questions?

No Just kidding! Yep!

 

[kalesb]

I hit a brick wall due to my knowledge of VBA/Form but I am learning.....

I took some of your suggestions and then tried a simpler approach. I could not get the module to work with Form attached due to different approach.

See Form in attachment

When I click OK, I run the following...

Private Sub Command8_Click()
 
output:
On Error GoTo no_data
Select Case Me.Option3 = On_Focus
    Case 1
        DoCmd.OpenReport "Rpt_CompletedA", acViewPreview, , "(((ECP_T.F_ECP_DTE) Between [Forms]![Frm_ECP_CompletedAB]![SDate] And [Forms]![Frm_ECP_CompletedAB]![EDate]))"
    Case 2
        DoCmd.OpenReport "Rpt_CompletedA", acViewPreview, , "(((ECP_T.F_ECP_DTE) Between [Enter Start Date:] And [Enter End Date:]))"
    Case Else
 '       DoCmd.OpenReport "Rpt_CompletedA", acViewPreview, , "notnull"
 Dim tempDate As Variant
 
                        tempDate = InputBox("Error Not Found:")
    End Select
Exit Sub
local_err:
rst.Close
' no reservations found
no_data:
Resume exit_ok
exit_ok:
End Sub

I did not get an error but did not get the rpt to display. If I can get this to work it should reduce my 10 reports to 1....

I insert InputBox to aid in debugging but InputBox was not displayed.

 

[CBrighton]

I've not looked at the example database (not on my Office 2007 machine so accdb is a no-no) but I can see a few things I would flag up in that code.

Select Case Me.Option3 = On_Focus

Should this have the " = On_Focus"? I use if statements over case so am not 100% on the syntax, but I would expect "Select Case Me.Option3" to be enough (in fact, I likely wouldn't even use the "Me.")

"(((ECP_T.F_ECP_DTE) Between [Forms]![Frm_ECP_CompletedAB]![SDate] And [Forms]![Frm_ECP_CompletedAB]![EDate]))"

Control references within strings don't work.

The correct way to write that would be: ""(((ECP_T.F_ECP_DTE) Between #" & [Forms]![Frm_ECP_CompletedAB]![SDate] & "# And #" & [Forms]![Frm_ECP_CompletedAB]![EDate] & "#"))""


The final criteria fails to say which field it is checking for null values, and I'm fairly sure "notnull" as a single word isn't going to be valid anyway (it's commented out though may not matter)


Note, the input box is within the Case statement so will only fire if Option3 is something other than 1 or 2.

I don't see tempDate being defined anywhere either.

 

[kalesb]

I am going to change Select Case to:

Select Case Me.Option3

and then insert some more inputboxes at each Case to determine if working...















Select Case Me.Option3

 

[kalesb]

Update:

Select Case Option3.On_Focus (errored)

Compiler Error: method or data member not found

 

[CBrighton]

What's the On_Focus for?

Isn't "Select Case Option3" enough?

I did a quick google for "Select Case On_Focus" and I don't see anything which would explain why it's needed.

Try it without it.

 

[vbaInet]

I've not looked at the example database (not on my Office 2007 machine so accdb is a no-no) but I can see a few things I would flag up in that code.

The code wasn't based on my concept. kalesb just chopped the SELECT CASE statement from it

Just copying code without understanding what it does isn't the way forward but the upside is you get to learn new concepts.

So no you don't = On_Focus. And "notnull" will fail.

 

[kalesb]

Made following change with no errors but program will not display rpt.

I do get display inputbox ahead of Select Case statement.

Private Sub Command8_Click()
Dim tempDate As Variant
tempDate = InputBox("Display InputBox")
output:
On Error GoTo no_data
Select Case Option3
Case 1
DoCmd.OpenReport "Rpt_CompletedA", acViewPreview, , "(((ECP_T.F_ECP_DTE) Between [Forms]![Frm_ECP_CompletedAB]![SDate] And [Forms]![Frm_ECP_CompletedAB]![EDate]))"
tempDate = InputBox("Error Case 1")
Case 2
DoCmd.OpenReport "Rpt_CompletedA", acViewPreview, , "(((ECP_T.F_ECP_DTE) Between [Enter Start Date:] And [Enter End Date:]))"
tempDate = InputBox("Error Case 2")
Case Else
' DoCmd.OpenReport "Rpt_CompletedA", acViewPreview, , "notnull"
tempDate = InputBox("Error Case 3")
 
 
End Select
Exit Sub
local_err:
rst.Close
' no reservations found
no_data:
Resume exit_ok
exit_ok:
End Sub
[\code]

 

[CBrighton]

The problem with self / community taught is that sometimes you pick up enough to think you know what you're doing.

I do it all the time.

 

[CBrighton]

You seem to have missed this:

"(((ECP_T.F_ECP_DTE) Between [Forms]![Frm_ECP_CompletedAB]![SDate] And [Forms]![Frm_ECP_CompletedAB]![EDate]))"

Control references within strings don't work.

The correct way to write that would be: "(((ECP_T.F_ECP_DTE) Between #" & [Forms]![Frm_ECP_CompletedAB]![SDate] & "# And #" & [Forms]![Frm_ECP_CompletedAB]![EDate] & "#"))"

What it nmay be worth doing is using this line of code...

MsgBox Option3

...to make sure the control is outputting the number you expect for each selection.

 

[kalesb]

Option3 is a Option Button (Radio Button)

MsgBox does not like msgBox Option3

and My Case can not figure it out either or want I wanted to do....

with Option3 set: I want to use [EDate] and [SDate] from Form for SQL where clause (see Word attachment from 1st post of the day)

with Option5 set: I want to be prompted to Enter Start and End Date which will in turn be used with SQL where clause

 

[CBrighton]

To be perfectly honest, the only option groups I have are legacy ones. As such I'm not the most helpful person regarding troubleshooting them.

One of the downsides of being self taught is knowing a way to do things, not necessarily the best way.

I'd just go with a couple of checkboxes and set the on click event for each one to set the other one(s) to false. That way it would work exactly the same but all the VBA would have to do is check each control for true (as only one would be true at a given time).

Feel free to either give that a try or wait for someone with more knowledge of option groups to come into the thread.

 

[vbaInet]

If you're using a Tristate option button then Msgbox will bomb when it's in the Null state.

 

[CBrighton]

Would this work:

msgBox Nz(Option3,"No selection")

 

[vbaInet]

Would this work:

msgBox Nz(Option3,"No selection")

It sure will.

 

[kalesb]

Success!!!!

Should be checking optDates instead of option3

Private Sub Command8_Click()
 
    Dim tempDate As Variant
 
    Dim SDate   As Variant
    Dim EDate   As Variant
    On Error GoTo X_Err
    Select Case Me.optDates
        Case 1
            DoCmd.OpenReport "Rpt_ECP_CompletedA", acViewPreview, , "ECP_T.F_ECP_DTE Between #" & Form_Frm_ECP_CompletedAB.SDate & "# And #" & Form_Frm_ECP_CompletedAB.EDate & "#", , "For dates between " & Form_Frm_ECP_CompletedAB.SDate & " And " & Form_Frm_ECP_CompletedAB.EDate
        Case 2
 
Start_Sdate:
            SDate = InputBox("Enter your starting date (m/d/yy)", "Start Date", DateAdd("d", -90, Date))
            If SDate = vbNullString Then Exit Sub
            If Not IsDate(SDate) Then GoTo Start_Sdate
            Debug.Print SDate
 
Start_Edate:
            EDate = InputBox("Enter your starting date (m/d/yy)", "Start Date", Date)
            If EDate = vbNullString Then Exit Sub
            If Not IsDate(EDate) Then GoTo Start_Edate
            Debug.Print SDate
 
            DoCmd.OpenReport "Rpt_ECP_CompletedA", acViewPreview, , "ECP_T.F_ECP_DTE Between #" & SDate & "# And #" & EDate & "#", , "For dates between " & SDate & " And " & EDate
    Case Else
        tempDate = MsgBox("Error Case 3")
 
    End Select

Things seem to be working