User input - Bucket number continuation

additional question to the previous question.  I want to be able to show all of the bucket numbers if the user doesn't input any number
MeilaniZamoraAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
this code, what did u enter to get all? it works by not entering anything
do u want to check for something?
Ive put more validation in


Private Sub OpenSlabsRPT_Click()

    Dim sWhere As String

    sWhere = ""

    sBucketNumber = Nz(InputBox("Please enter Bucket Number"), "")

    'If value entered
    If IsNull(sBucketNumber) = False Then
        If IsNumeric(sBucketNumber) = True Then
            sWhere = "BucketNumber = " & sBucketNumber
            Msgbox "Where Clause Set to " & sWhere
         End If
     End if

    DoCmd.OpenReport "rpt_Consignment_PET_Slabs", acPreview, , sWhere

End Sub
0
 
harfangCommented:
Hello MeilaniZamora

Use this as criteria in your query:

    Field:      BucketNumber
    Criteria:  [Enter bucket number:] Or [Enter bucken number:] Is Null

Good luck!
(°v°)
0
 
harfangCommented:
Ah, sorry, you went for the inputbox method...

    strAnswer = InputBox("please enter a number",,"all")
    if strAnswer = "" Then Exit Sub   ' user pressed [Cancel]
    if strAnswer = "all" Then   ' user pressed [OK]
        DoCmd.OpenReport "rptName", acViewPreview
    elseif isnumeric(strAnswer) then
        DoCmd.openReport "rptName", acVeiwPreview, WhereCondition="BucketNumber=" & stranswer
    else
        msgbox "huh?"
    end if

That sort of things?

(°v°)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
rockiroadsCommented:
Harfang's idea is to use a default value of All. Ensure then that the user understands that, change the prompt.

If u want to check for no bucket numbers entered then do this


    Dim sWhere as String


    sWhere = ""
    If IsNull(txtBucketNumber) = False Then sWhere = "BucketNumber = " & txtBucketNumber.Value
    DoCmd.OpenReport "rpt_Consignment_PET_Slabs", , , sWhere
   


Basically we set the where clause for the report based on what the user enters only
If nothing specified then we run for all bucket numbers

0
 
MeilaniZamoraAuthor Commented:
Am I putting in all of the above instructions "Dim sWhere as String" before

sbucketnumber = Nz(InputBox("Please enter Bucket Number"), "")
If sbucketnumber <> "" Then
    DoCmd.OpenReport "rpt_Consignment_PET_Slabs", acPreview, , "BucketNumber = " & sbucketnumber
0
 
rockiroadsCommented:
sWhere is the definition so u put that at the start of your procedure

when is this code being run?, is it OpenSlabs?

Private Sub OpenSlabsRPT_Click()

    Dim sWhere as String
    Dim sBucketNumber as String


    sWhere = ""
    sBucketNumber = Nz(InputBox("Please enter Bucket Number"), "")

    If sBucketNumber = "" Then sWhere = "BucketNumber = " & sBucketNumber

    DoCmd.OpenReport "rpt_Consignment_PET_Slabs", , , sWhere

End Sub
0
 
MeilaniZamoraAuthor Commented:
I have the following


Private Sub OpenSlabsRPT_Click()

Dim sWhere As String

sWhere = ""
sBucketNumber = Nz(InputBox("Please enter Bucket Number"), "")
If IsNull(sBucketNumber) = False Then sWhere = "BucketNumber = " & sBucketNumber
DoCmd.OpenReport "rpt_Consignment_PET_Slabs", acPreview, , sWhere

End Sub

When I click on the button and type in a bucket number it's fine but once I just enter as if I'm selecting all of the buckets to be previewed in the form I get the following error

Run-time error '3075'
Extra ) in query expression '(BucketNumber = )'

Help?
0
 
MeilaniZamoraAuthor Commented:
Private Sub OpenSlabsRPT_Click()


Dim sBucketNumber As String

strAnswer = InputBox("please enter a number", , "all")
If strAnswer = "" Then Exit Sub 'user pressed [Cancel]
If strAnswer = "all" Then 'user pressed [Ok]
    DoCmd.OpenReport "rpt_Consignment_PET_Slabs", acViewPreview
ElseIf IsNumeric(strAnswer) Then
    DoCmd.OpenReport "rpt_Consignment_PET_Slabs", acViewPreview, wherecondition = "BucketNumber=" & strAnswer
    Else
        MsgBox "pardon?"
   
    End If
   
   
   
End Sub

I tried the following code and it shows all of the bucket numbers fine when the input is 'all' but when I enter a number like 1 or 2 it still shows all of the bucket numbers rather than just showing the bucket numbers I had typed in.  Any help would be greatly appreciated - I need to get this thing done before the end of the day.  Thank you.
0
 
MeilaniZamoraAuthor Commented:
that is PERFECT!!!!  Thank you SO MUCH!!!!
0
 
rockiroadsCommented:
I just realised, because I used NZ to wrap, u do not need to check for IsNull

it's better of saying

If sBucketNumber <> "" then

instead of

If IsNull(sBucketNumber) = False Then

0
 
MeilaniZamoraAuthor Commented:
great!!!  I've changed it as instructed
0
 
harfangCommented:
MeilaniZamora

I'm glad you have a working solution, and I apologize for the slight error in my first post. I used this:

    .... WhereCondition="BucketNumber=" & stranswer

Instead of

    .... WhereCondition:="BucketNumber=" & stranswer

Doesn't look like much, but it entirely changes the meaning of the expression...

Anyway, good luck with  your project!
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.