Print combobox search

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jan
    #1

    Print combobox search

    I am having problems trying to print a report based on a form. This
    is a search form only, no data input. There is a query that the form
    looks at, but then there are numerous comboxes that you can pick
    information from either one or many and press a search button. See
    code below for the "Case Select" that was used to make up the
    comboboxes. Not a filtered list. My problem is that when I try to
    print, it prints all records before the search. I need it to print
    what I had just searched. This could be one or many records. I am
    learning VB and it can follow it pretty good.

    Private Sub cmdSearch_Click ()
    Dim i As Integer
    Dim stWhere As String
    Dim stDelim As String

    For i = 0 To 12
    Select Case i
    Case 0: stDelim = "'" 'text data type
    Case 1: stDelim = "'" 'text data type
    Case 2: stDelim = "'" ' Text data type
    Case 3: stDelim = vbNullString ' numeric data type
    Case 4: stDelim = vbNullString ' numberic data type
    Case 5: stDelim = "'" 'text data type
    Case 6: stDelim = "'" ' text data type
    Case 7: stDelim = vbNullString 'numeric data type
    Case 8: stDelim = vbNullString 'numeric data type
    Case 9: stDelim = "@" ' currency data type
    Case 10: stDelim = "'" 'text data type
    Case 11: stDelim = "'" 'text data type
    Case 12: stDelim = "'" 'Text data type

    End Select
    If Nz(Me("Criteria " & i), vbNullString) <> vbNullString Then
    If Me("Criteria" & i) <> "<All>" Then
    stWhere = stWhere & " AND " & Me("Criteria" & i).Tag &
    " = " & stDelim & Me("Criteria" & i) & stDelim
    End If
    End If
    Next i
    If stWhere <> vbNullString Then
    stWhere = Mid$(stWhere, 6)
    Me.Filter = stWhere
    Me.FilterOn = True
    Else
    MsgBox "Please enter some criteria.", vbExclamation, "No
    Criteria Entered"
    End If
    End Sub

    Any help would be great!
    Thanks, Jan
    Tags: None
    • PC Datasheet
      #2
      Re: Print combobox search

      Jan,

      The problem is that all your search results go out of scope when the procedure,
      Private Sub cmdSearch_Click (), is done; meaning they are no longer available to
      the query your report is based on. Put the following code in the Click event of
      a button to do what you want:
      (This is pseudocode which you can use to create the actual code!)

      DoCmd.OpenForm "YourSearchForm ",,,,,acDia log
      DoCmd.OpenRepor t "YourReport ", acPreview

      On your search form you need a button with the following code in the Click
      event:
      Me.Visible = False

      On your search form, you have to remove all the ways the form can be closed. The
      user has to click on the above button to get out of the search form.

      Put the following code in the Close event of your report:
      DoCmd.Close acForm, "YourSearchForm "

      --
      PC Datasheet
      Your Resource For Help With Access, Excel And Word Applications
      resource@pcdata sheet.com
      https://www.pcdatasheet.com



      "Jan" <Jan.crocker@we atherford.com> wrote in message
      news:8dffdd80.0 404271130.31b17 662@posting.goo gle.com...[color=blue]
      > I am having problems trying to print a report based on a form. This
      > is a search form only, no data input. There is a query that the form
      > looks at, but then there are numerous comboxes that you can pick
      > information from either one or many and press a search button. See
      > code below for the "Case Select" that was used to make up the
      > comboboxes. Not a filtered list. My problem is that when I try to
      > print, it prints all records before the search. I need it to print
      > what I had just searched. This could be one or many records. I am
      > learning VB and it can follow it pretty good.
      >
      > Private Sub cmdSearch_Click ()
      > Dim i As Integer
      > Dim stWhere As String
      > Dim stDelim As String
      >
      > For i = 0 To 12
      > Select Case i
      > Case 0: stDelim = "'" 'text data type
      > Case 1: stDelim = "'" 'text data type
      > Case 2: stDelim = "'" ' Text data type
      > Case 3: stDelim = vbNullString ' numeric data type
      > Case 4: stDelim = vbNullString ' numberic data type
      > Case 5: stDelim = "'" 'text data type
      > Case 6: stDelim = "'" ' text data type
      > Case 7: stDelim = vbNullString 'numeric data type
      > Case 8: stDelim = vbNullString 'numeric data type
      > Case 9: stDelim = "@" ' currency data type
      > Case 10: stDelim = "'" 'text data type
      > Case 11: stDelim = "'" 'text data type
      > Case 12: stDelim = "'" 'Text data type
      >
      > End Select
      > If Nz(Me("Criteria " & i), vbNullString) <> vbNullString Then
      > If Me("Criteria" & i) <> "<All>" Then
      > stWhere = stWhere & " AND " & Me("Criteria" & i).Tag &
      > " = " & stDelim & Me("Criteria" & i) & stDelim
      > End If
      > End If
      > Next i
      > If stWhere <> vbNullString Then
      > stWhere = Mid$(stWhere, 6)
      > Me.Filter = stWhere
      > Me.FilterOn = True
      > Else
      > MsgBox "Please enter some criteria.", vbExclamation, "No
      > Criteria Entered"
      > End If
      > End Sub
      >
      > Any help would be great!
      > Thanks, Jan[/color]


        Comment

        • Jan Crocker
          #3
          Re: Print combobox search



          Thank you so much! Great information. I am learning so much. You are
          the only forum who would help me.

          Jan
          Houston, TX

          *** Sent via Developersdex http://www.developersdex.com ***
          Don't just participate in USENET...get rewarded for it!

            Comment

            • Jan Crocker
              #4
              Re: Print combobox search


              Wait, I am still only getting what happened before the search. Did I
              need to put the DoCmds on the Search button or the print button?

              Jan


              *** Sent via Developersdex http://www.developersdex.com ***
              Don't just participate in USENET...get rewarded for it!

                Comment

                • PC Datasheet
                  #5
                  Re: Print combobox search

                  The Docmds go on the Print button. When you click that button, your search form
                  opens and you set all your search criteria in the comboboxes. What you are
                  calling the Search button runs the one line of code, Me.Visible = False.

                  The acDialog causes the code to pause until your Search form either closes or
                  becomes not visible. When it does, the code resumes and opens your report. At
                  the point your report opens, your Search form needs to be open (but not visible)
                  so your report query can get its criteria. Your Search form needs to stay open
                  so when you print the report the report can run the report query again. That's
                  why you don't close the Search form until the report closes.

                  Steve
                  PC Datasheet



                  "Jan Crocker" <jan.crocker@we atherford.com> wrote in message
                  news:408ecdd5$0 $203$75868355@n ews.frii.net...[color=blue]
                  >
                  > Wait, I am still only getting what happened before the search. Did I
                  > need to put the DoCmds on the Search button or the print button?
                  >
                  > Jan
                  >
                  >
                  > *** Sent via Developersdex http://www.developersdex.com ***
                  > Don't just participate in USENET...get rewarded for it![/color]


                    Comment

                    Previous template Next
                    Working...