Solved

User input - Bucket number continuation

Posted on 2006-06-09
12
320 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now