Solved

User input - Bucket number continuation

Posted on 2006-06-09
12
360 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Industry Leaders: 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!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

751 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