Solved

User input - Bucket number continuation

Posted on 2006-06-09
12
355 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:MeilaniZamora
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 16874557
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
 
LVL 58

Expert Comment

by:harfang
ID: 16874582
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16883448
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MeilaniZamora
ID: 16886403
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16891956
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
 

Author Comment

by:MeilaniZamora
ID: 16904922
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
 

Author Comment

by:MeilaniZamora
ID: 16906116
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 16906168
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
 

Author Comment

by:MeilaniZamora
ID: 16906219
that is PERFECT!!!!  Thank you SO MUCH!!!!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16906271
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
 

Author Comment

by:MeilaniZamora
ID: 16906388
great!!!  I've changed it as instructed
0
 
LVL 58

Expert Comment

by:harfang
ID: 16907908
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question