Filtering a report from a Combo Box 1

[Xtremlylost]

I have a label printing form that includes two check boxes. If one box is checked it will disable the other check box, enable the Print button and print all the labels for all the Carriers in the Carrier table when the print button is clicked. The other check box when selected disables the other check box,enables the Print button and enables the combo box which will allow a user to choose a single Carrier for printing a label. I have tried several different ways to get the filter to work but cannot seem to get the report to print only the selected Carrier's label. The 1st checkbox works fine and will print labels for all Carriers.

The label report itself was made through the the label wizard.

The combo box is [CarrierLblcmbo]
The labels fields are:
=Trim([CarName])
=Trim([CarAdd])
=Trim([CarCity] & ", " & [CarState] & " " & [CarZip])
="Attn: " & [CarContact]

I am also wondering what to do with the code if a person clicks the print button without selecting a Carrier from the combo box.

Any help would be greatly appreciated
XtremelyLost

 

[neemi]

What fields have you got stored in your combo box?

What fields does your report use.

Is the Labels based on a table or query?

 

[Xtremlylost]

I have not designed the label report from a query since all the relevant info was in a single table. The fields in the label report are above and were not clearly marked. They are: The "=Trim" statement begins the field in the label with the "Attn" field being at the bottom of the label.

The combo box was made using the wizard from the tool box and was set to look up its info from the [Carriertbl]. It is only looking up and displaying the [CarName] or Carriers name.

Thanks for replying. Look forward to some help if possible.

 

[CosmoKramer]

Why not combine the two functions and allow the user to select "All" or individual carriers from a combo box?? The following might look overwhelming, but give it a shot because it does work.

1) Copy and paste the following code into a module.

Function AddAllToList(C As Control, id As Long, Row As Long, Col As Long, Code As Integer) As Variant
On Error GoTo 0
      '***************************************************************
      ' FUNCTION: AddAllToList()
      '
      ' PURPOSE:
      '   Adds "(all)" as the first row of a combo box or list box.
      '
      ' USAGE:
      '   1. Create a combo box or list box that displays the data you
      '      want.
      '
      '   2. Change the RowSourceType property from "Table/Query" to
      '      "AddAllToList."
      '
      '   3. Set the value of the combo box or list box's Tag property to
      '      the column number in which you want "(all)" to appear.
      '
      '   NOTE: Following the column number in the Tag property, you can
      '   enter a semicolon (;) and then any text you want to appear
      '   other than the default "all."
      '
      '         For example
      '
      '             Tag: 2;<None>
      '
      '         displays &quot;<None>&quot; in the second column of the list.
      '
      '***************************************************************
         Static DB As Database, RS As Recordset
         Static DISPLAYID As Long
         Static DISPLAYCOL As Integer
         Static DISPLAYTEXT As String
         Dim Semicolon As Integer

      On Error GoTo Err_AddAllToList

         Select Case Code
            Case LB_INITIALIZE
               ' See if the function is already in use.
               If DISPLAYID <> 0 Then
                  MsgBox &quot;AddAllToList is already in use by another Control! &quot;
                  AddAllToList = False
                  Exit Function
               End If

               ' Parse the display column and display text from the Tag
               ' property.
               DISPLAYCOL = 1
               DISPLAYTEXT = &quot;(All)&quot;
               If Not IsNull(C.Tag) Then
                  Semicolon = InStr(C.Tag, &quot;;&quot;)
                  If Semicolon = 0 Then
                     DISPLAYCOL = Val(C.Tag)
                  Else
                     DISPLAYCOL = Val(Left(C.Tag, Semicolon - 1))
                     DISPLAYTEXT = Mid(C.Tag, Semicolon + 1)
                  End If
               End If

               ' Open the recordset defined in the RowSource property.
               Set DB = DBEngine.Workspaces(0).Databases(0)
               Set RS = DB.OpenRecordset(C.RowSource, DB_OPEN_SNAPSHOT)

               ' Record and return the ID for this function.
               DISPLAYID = Timer
               AddAllToList = DISPLAYID

            Case LB_OPEN
               AddAllToList = DISPLAYID

            Case LB_GETROWCOUNT
               ' Return the number of rows in the recordset.
               RS.MoveLast
               AddAllToList = RS.RecordCount + 1

            Case LB_GETCOLUMNCOUNT
               ' Return the number of fields (columns) in the recordset.
               AddAllToList = RS.Fields.Count

            Case LB_GETCOLUMNWIDTH
               AddAllToList = -1

            Case LB_GETVALUE
               ' Are you requesting the first row?
               If Row = 0 Then
                  ' Should the column display &quot;(All)&quot;?
                  If Col = DISPLAYCOL - 1 Then
                     ' If so, return &quot;(All).&quot;
                     AddAllToList = DISPLAYTEXT
                  Else
                     ' Otherwise, return NULL.
                     AddAllToList = Null
                  End If
               Else
                  ' Grab the record and field for the specified row/column.
                  RS.MoveFirst
                  RS.Move Row - 1
                  AddAllToList = RS(Col)
               End If
            Case LB_END
               DISPLAYID = 0
               RS.Close
         End Select

Bye_AddAllToList:
         Exit Function

Err_AddAllToList:
Beep:          MsgBox Error$, 16, &quot;AddAllToList&quot;
         AddAllToList = False
         Resume Bye_AddAllToList
      End Function

2) Add an unbound text box to your form (txtCarrier) and set the Visible property to No.

3) In the After Update property of the combo box set the value to the text box you just added:

Me.txtCarrier = Me.CarrierLblcmbo.Column(0)

4) Change the RowSourceType property for your combo box from Table/Query to AddAllToList

5) Change the Tag property for the combo box to reflect the column number and the text you want to display for "all" carriers to be selected:

0;--All Carriers--

6) Create a query that has all of the fields from your table and set the criteria for the carrier name field to be something like this:

Like [Forms]![YourFormNameHere]![txtCarrier] & "*"

This will allow for all records if the user selects the first entry of the combo box, or just the individual one.

7) Change the Record Source of the label report to be the query you just created.

Now, when you click on the drop-down, "--All Carriers--" should be the first entry in the combo box, and if that is selected, all carriers will be returned.

Let us know how you make out......

 

[Xtremlylost]

This works in part. Here is what is happening:

The --All Carriers-- is now showing up in the list. When selected however it does not show up in the combo box when the drop down closes. (Perhaps it is not supposed to?) It will however print All the carriers labels.

If, however, I choose a single carriers name I receive (#Error) on all four lines of the label. The label report is using the [Labelqry] as the record source as instructed. I have added the criteria for the [CarName] as suggested in the query and changed the name to the correct form, [LblPrntOptfrm].

I have stepped through the instructions a few times and seem to have everything in its place.

Any ideas?

I do appreciate your time,
XtremelyLost

 

[CosmoKramer]

This could be a few things... Could you send me the db and I can take a look at it for you??

sw3540@yahoo.com

 

[CosmoKramer]

A response is on its way.....

 

[Xtremlylost]

Your changes made all the difference.

Thanks for your help!