Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    10

    Question Unanswered: New user seeking help - option button pass thru to report

    Hi, searching around for alittle bit on here i couldnt find a answer to my question, so ill post it up.

    My work asked me to make up a database where our salesmen can pick options of a items, then hit print and have it print out the spare part #s of the item

    This is my first time using access, i fiqured how to make the form how they want it, but i dont know how to have it when they select a option button to have it pass thru to the report. So only what he selected is check is printed out.

    Thanks Greatly
    Brian

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi
    I suppose the first thing to establish is HOW the data is selected by the user?

    How will the user select data? Will this be by typing in a part name i.e BRAKES and see a list of different BRAKES supplied or will it be more specific so that a PART NUMBER will be used?

    If the PARTS are multiple i.e. user enters "Panaman 4 wheel Drive" and then expects to see ALL the parts for that car that are available. will they expect to output to printer all the parts or just one or even shall we say 5 of 50?

    Will the user see that data on the screen first? If yes how are you doing this ?


    just a few quick questions so that the thought process starts at the right level

    rgds

    gareth

  3. #3
    Join Date
    Mar 2004
    Posts
    10
    Hi i have a form with tabs, and in each tab there are option buttons (the user just clicks the button of the product (pretyped out). Once all the options are selected - the user would pick print and my goal is to have all # parts of the items print out in a useable report

    see attached file
    Attached Thumbnails Attached Thumbnails form.gif  

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    hi
    there are a number of ways that we can achieve the print sequence

    In a model I made previously I used a simple query attached to a report.

    I set the criteria to look at the "TICK BOXES". (You can set a query for each tabbed page and set of tick boxes).

    I then set the criteria with IIF statments that specified that

    IIF (tick boxPart1 = -1,RECORDS for part1,"")
    or
    IIF (tick boxPart2 = -1,RECORDS for part2,"")
    or
    IIF (tick boxPart3 = -1,RECORDS for part3,"")
    etc
    In doing this the query will look for the -1 wich is the tick and ignore the others I think this the best way around the situation for the already developed forms

    and others can be add quickly - this is useful if people dont use SQL.

    I hope this simple work around works for you

    regards

    gareth
    Last edited by garethfx; 03-08-04 at 16:03.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ok ... Once again, here's some code (mine) that examples the construction of a filter ( WHERE clause of a query ) Using different comboinations of text boxes and radiobuttons/option groups:

    Code:
    Function ConstructFilter() As String
        Dim HaveStatusSEL As Boolean, HaveTrackingSEL As Boolean, HaveRequestorSEL As Boolean
        Dim HaveUsingSEL As Boolean, HaveDatesSEL As Boolean
        Dim FilterString As String
        
        HaveStatusSEL = False
        HaveTrackingSEL = False
        HaveRequestorSEL = False
        HaveUsingSEL = False
        HaveDatesSEL = False
        
        ' Validations
        If BeginTrackTxt.Value & "" <> "" And EndTrackTxt.Value & "" <> "" Then
            If CLng(BeginTrackTxt.Value) > CLng(EndTrackTxt.Value) Then
                MsgBox "The starting tracking # cannot exceed the ending tracking #.", vbExclamation, "System Monitor"
                ConstructFilter = ""
                BeginTrackTxt.SetFocus
                Exit Function
            End If
        End If
        If BeginDtTxt.Value & "" <> "" And EndDtTxt.Value & "" <> "" Then
            If CDate(BeginDtTxt.Value) > CDate(EndDtTxt.Value) Then
                MsgBox "The starting date cannot exceed the ending date.", vbExclamation, "System Monitor"
                ConstructFilter = ""
                BeginDtTxt.SetFocus
                Exit Function
            End If
        End If
        
        FilterString = " WHERE ("
        If SelectionGroup.Value <> 0 Then
            FilterString = FilterString & "( [Purchase Orders].Status="
            Select Case SelectionGroup.Value
                Case 1  ' Purchase Requests
                    FilterString = FilterString & PO_Created_STAT
                Case 2  ' In Process Orders
                    FilterString = FilterString & PO_InProcess_STAT
                Case 3  ' Validated Orders
                    FilterString = FilterString & PO_Validated_STAT
                Case 4  ' Outstanding Orders
                    FilterString = FilterString & PO_Outstanding_STAT
                Case 5  ' Fulfilled Orders
                    FilterString = FilterString & PO_Fulfilled_STAT
                Case 6  ' Voided Orders
                    FilterString = FilterString & PO_VOIDED_STAT
            End Select
            FilterString = FilterString & " )"
            HaveStatusSEL = True
        End If
        If BeginTrackTxt.Value & "" <> "" Then
            If HaveStatusSEL Then FilterString = FilterString & " AND "
            FilterString = FilterString & "( [Purchase Orders].[Tracking #]"
            If EndTrackTxt.Value & "" <> "" Then
                FilterString = FilterString & " BETWEEN " & CLng(BeginTrackTxt.Value) & " AND "
            Else
                FilterString = FilterString & "=" & CLng(BeginTrackTxt.Value) & " )"
            End If
            HaveTrackingSEL = True
        End If
        If EndTrackTxt.Value & "" <> "" Then
            If HaveStatusSEL And Not HaveTrackingSEL Then
                FilterString = FilterString & " AND "
            End If
            If Not HaveTrackingSEL Then
                FilterString = FilterString & "( [Purchase Orders].[Tracking #]="
            End If
            FilterString = FilterString & CLng(EndTrackTxt.Value) & " )"
            HaveTrackingSEL = True
        End If
        If RequestedByComboBox.Value & "" <> "" Then
            If HaveStatusSEL Or HaveTrackingSEL Then FilterString = FilterString & " AND "
            FilterString = FilterString & "( [Purchase Orders].RequestedByID='" & RequestedByComboBox.Value & "' )"
            HaveRequestorSEL = True
        End If
        If DateGroup.Value <> 0 Then
            If HaveStatusSEL Or HaveTrackingSEL Or HaveRequestorSEL Then FilterString = FilterString & " AND "
            Select Case DateGroup.Value
                Case 1  ' Required Date
                    FilterString = FilterString & "( [Purchase Orders].RequiredDate"
                Case 2  ' Order Date
                    FilterString = FilterString & "( [Purchase Orders].OrderDate"
                Case 3  ' Last Modified Date
                    FilterString = FilterString & "( [Purchase Orders].LastModified"
            End Select
            If BeginDtTxt.Value & "" <> "" And EndDtTxt.Value & "" <> "" Then
                FilterString = FilterString & " BETWEEN #" & BeginDtTxt.Value & "# AND #" & EndDtTxt.Value & "# )"
            ElseIf BeginDtTxt.Value & "" <> "" Or EndDtTxt.Value & "" <> "" Then
                If BeginDtTxt.Value & "" <> "" Then
                    FilterString = FilterString & "=#" & BeginDtTxt.Value & "# )"
                ElseIf 0 Then
                    FilterString = FilterString & "=#" & EndDtTxt.Value & "# )"
                End If
            End If
            HaveDatesSEL = True
        End If
        If HaveStatusSEL Or HaveTrackingSEL Or HaveRequestorSEL Or HaveDatesSEL Then FilterString = FilterString & ")"
        
        If FilterString = " WHERE ()" Then FilterString = ""
        
        ConstructFilter = FilterString
    End Function

  6. #6
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi GPB

    I looked at the code its nice BUT Im a little concerned that the original question is from someone who has never used access! I have approched this from a different angle from yourself in that the original requestee may not be familier with DB and needs something simplistic to look at first then move on - no disrespect but sadly we are not all as good as you ( I wish i was mate!!!)


    thanks

    gareth

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by garethfx
    Hi GPB

    I looked at the code its nice BUT Im a little concerned that the original question is from someone who has never used access! I have approched this from a different angle from yourself in that the original requestee may not be familier with DB and needs something simplistic to look at first then move on - no disrespect but sadly we are not all as good as you ( I wish i was mate!!!)


    thanks

    gareth
    Gareth,

    Point taken. My apologies for potentially overwhelming a new programmer ...

  8. #8
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Wink

    HI

    never mind the new comer!! ---- It overwhelmed me lol


    nice code - good to see whats done top end with people who deal with Access its ALL a learning curve


    gareth

  9. #9
    Join Date
    Mar 2004
    Posts
    10
    Hi thanks for the quick reply, but now where do i put this in the report section or the form section? build a expression ? And so i learn is there anyway you could decifer the code, so i can understand what the line is doing?

    Thanks veeeeeeeeeeeeeeeeerrry much again
    Brian

    Originally posted by garethfx
    hi
    there are a number of ways that we can achieve the print sequence

    In a model I made previously I used a simple query attached to a report.

    I set the criteria to look at the "TICK BOXES". (You can set a query for each tabbed page and set of tick boxes).

    I then set the criteria with IIF statments that specified that

    IIF (tick boxPart1 = -1,RECORDS for part1,"")
    or
    IIF (tick boxPart2 = -1,RECORDS for part2,"")
    or
    IIF (tick boxPart3 = -1,RECORDS for part3,"")
    etc
    In doing this the query will look for the -1 wich is the tick and ignore the others I think this the best way around the situation for the already developed forms

    and others can be add quickly - this is useful if people dont use SQL.

    I hope this simple work around works for you

    regards

    gareth
    Last edited by slothy; 03-09-04 at 11:24.

  10. #10
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    The IIF statements are put in the CRITERIA part of a query

    If you build a query - set up the IIF statements in a criteria - I am assuming that you may have the part No# in one field ie

    PartNo
    BN1
    BP1
    BU3

    When the check box for BN1 is ticked (=-1) we are saying in the IFF statement look for all parts named BN1 (records for part1) other wise do nothing (,"")

    IIF (tick boxPart1 = -1,RECORDS for part1,"")
    then in the OR line beneath place the next IFF statement which in effect will look to see if the BP1 part number tick box is checked and if it is, look for BP1 records

    hope fully you can start seeing what the iff statement is doing

    rgds

    gareth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •