Print combobox search

[Jan]

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

[PC Datasheet]

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",,,,,acDialog
DoCmd.OpenReport "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
re******@pcdatasheet.com
www.pcdatasheet.com
"Jan" <Ja*********@weatherford.com> wrote in message
news:8d**************************@posting.google.c om...

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

[Jan Crocker]

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!

[Jan Crocker]

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!

[PC Datasheet]

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" <ja*********@weatherford.com> wrote in message
news:40*********************@news.frii.net...

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!