Solved

User input - Bucket number continuation

Posted on 2006-06-09
12
338 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

786 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