Creating Preview button with variable

[Debbiedo]

I have the follow code that builds a seach query in a search form. How
do I create a Print Preview button within this form to display the
results returned from this query? I can only get all the records to
display using the wizard.

Report name is CIT Report. Student_ID is the primary key used in both
the search query form and the CIT report.

Thanks for any and all help

Deb

-----------------------Code starts here------------

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long


'Build Student ID criteria expresion
If Not IsNull(Me.txtFilterStudentID) Then
strWhere = strWhere & "([Student_ID] = """ &
Me.txtFilterStudentID & """) AND "
End If

'Build Last Name criteria expresion
If Not IsNull(Me.txtFilterLastName) Then
strWhere = strWhere & "([Last_Name] Like ""*" &
Me.txtFilterLastName & "*"") AND "
End If

'Build First Name criteria expresion
If Not IsNull(Me.txtFilterFirstName) Then
strWhere = strWhere & "([First_Name] Like ""*" &
Me.txtFilterFirstName & "*"") AND "
End If

' Build School Name criteria expression
If Len(Me.cboFilterSchool.Value & "") > 0 Then
strWhere = strWhere & "School_Name='" &
Me.cboFilterSchool.Value & "' And "
End If

' ... build "Approval" criteria expression
Select Case Me.frameApproval.Value
Case 1 ' Approved
strWhere = strWhere & "SPNDSBUS='X' And "
Case 2 ' Not Approved
strWhere = strWhere & "(Len(SPNDSBUS & '') = 0 ) And "
Case 3 ' Both
strWhere = strWhere & "([SPNDSBUS]OR(Len(SPNDSBUS & '') = 0))
And "
End Select


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing here"
Else
strWhere = Left$(strWhere, lngLen)

Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[Allen Browne]

At the end of the procedure, replace:
Me.Filter = strWhere
Me.FilterOn = True
with:
DoCmd.OpenReport "CIT Report", acViewPreview, , strWhere

That assumes the report has the same fields your search form does.

 

[Debbiedo]

At the end of the procedure, replace:
        Me.Filter = strWhere
        Me.FilterOn = True
with:
    DoCmd.OpenReport "CIT Report", acViewPreview, , strWhere

That assumes the report has the same fields your search form does.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have the follow code that builds a seach query in a search form. How
do I create a Print Preview button within this form to display the
results returned from this query? I can only get all the records to
display using the wizard.

Report name is CIT Report. Student_ID is the primary key used in both
the search query form and the CIT report.

Thanks for any and all help

-----------------------Code starts here------------

Private Sub cmdFilter_Click()
       Dim strWhere As String
   Dim lngLen As Long

   'Build Student ID criteria expresion
   If Not IsNull(Me.txtFilterStudentID) Then
       strWhere = strWhere & "([Student_ID] = """ &
Me.txtFilterStudentID & """) AND "
   End If

   'Build Last Name criteria expresion
   If Not IsNull(Me.txtFilterLastName) Then
       strWhere = strWhere & "([Last_Name] Like ""*" &
Me.txtFilterLastName & "*"") AND "
   End If

   'Build First Name criteria expresion
   If Not IsNull(Me.txtFilterFirstName) Then
       strWhere = strWhere & "([First_Name] Like ""*" &
Me.txtFilterFirstName & "*"") AND "
   End If

  ' Build School Name criteria expression
   If Len(Me.cboFilterSchool.Value & "") > 0 Then
       strWhere = strWhere & "School_Name='" &
Me.cboFilterSchool.Value & "' And "
   End If

   ' ... build "Approval" criteria expression
Select Case Me.frameApproval.Value
   Case 1  '  Approved
       strWhere = strWhere & "SPNDSBUS='X' And "
   Case 2  '  Not Approved
       strWhere = strWhere & "(Len(SPNDSBUS & '') = 0 ) And"
   Case 3  '  Both
       strWhere = strWhere & "([SPNDSBUS]OR(Len(SPNDSBUS & '') = 0))
And "
End Select

   lngLen = Len(strWhere) - 5
   If lngLen <= 0 Then
       MsgBox "No criteria", vbInformation, "Nothing here"
   Else
       strWhere = Left$(strWhere, lngLen)

       Debug.Print strWhere

       Me.Filter = strWhere
       Me.FilterOn = True
   End If
End Sub- Hide quoted text -

- Show quoted text -

How does this become a command button? I will be creating a Print
Preview button , an Open Form button, a Search button and Clear
button. If I put just this code under the OnClick properties of the
Print Preview button, all records are returned, not just the filtered
ones. Below is the code I have for my Print Preview button. What am I
missing?

--------------------Code--------------------------

Private Sub Print_Preview_Click()
On Error GoTo Err_Print_Preview_Click
Dim strWhere As String

DoCmd.OpenReport "CIT Report", acViewPreview, , strWhere
Exit_Print_Preview_Click:
Exit Sub

Err_Print_Preview_Click:
MsgBox Err.Description
Resume Exit_Print_Preview_Click

End Sub

 

[Allen Browne]

Between these 2 lines:
Dim strWhere ...
DoCmd Openreport ...
you need the same code as you have in your filter button.

It's the same code, except for the lines incicated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

At the end of the procedure, replace:
Me.Filter = strWhere
Me.FilterOn = True
with:
DoCmd.OpenReport "CIT Report", acViewPreview, , strWhere

How does this become a command button? I will be creating a Print
Preview button , an Open Form button, a Search button and Clear
button. If I put just this code under the OnClick properties of the
Print Preview button, all records are returned, not just the filtered
ones. Below is the code I have for my Print Preview button. What am I
missing?

--------------------Code--------------------------

Private Sub Print_Preview_Click()
On Error GoTo Err_Print_Preview_Click
Dim strWhere As String

DoCmd.OpenReport "CIT Report", acViewPreview, , strWhere
Exit_Print_Preview_Click:
Exit Sub

Err_Print_Preview_Click:
MsgBox Err.Description
Resume Exit_Print_Preview_Click
End Sub

 

[Debbiedo]

Between these 2 lines:
    Dim strWhere ...
    DoCmd Openreport ...
you need the same code as you have in your filter button.

It's the same code, except for the lines incicated.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




How does this become a command button? I will be creating a Print
Preview button , an Open Form button, a Search button and Clear
button. If I put just this code under the OnClick properties of the
Print Preview button, all records are returned, not just the filtered
ones. Below is the code I have for my Print Preview button. What am I
missing?

--------------------Code--------------------------

Private Sub Print_Preview_Click()
On Error GoTo Err_Print_Preview_Click
    Dim strWhere As String

    DoCmd.OpenReport "CIT Report", acViewPreview, , strWhere
Exit_Print_Preview_Click:
    Exit Sub

Err_Print_Preview_Click:
    MsgBox Err.Description
    Resume Exit_Print_Preview_Click
End Sub

That did the trick. Thank you very much for all your code.

 

[Debbiedo]

That did the trick. Thank you very much for all your code.- Hide quoted text -

- Show quoted text -

I have one more question.

I need to add a button that references a separate record source and
then displays the results in the details section.

To explain, the user links to a nightly download of data (view only).
They need to compare this data (stored in the FormsDataTest table) to
their own table (CIT Table) using the Student_ID to determine which
students are not included in their own table (CIT Table). These
results (derived from the "Not Notified Query") need to be displayed
in the details section. The user then clicks on the record's "edit
record" button and a data entry form (CIT Form) is opened up. This
form contains a main form (FormsDataTest data) and an associated
subform(CIT Table data) linked by "Student_ID". The user populates the
CIT Table with the info they need to document and closes the form. Now
the tables are synched. The user then can then use the search feature
described at the beginning on my postings to find a specific student.
These results are also displayed in the details section. We have over
65,000 students thus this data is extremely dynamic.

So how can I convert the following code, which opens up in a sepatate
table window, to display in the details section when clicked? The
records displayed using the search code will need to be cleared and
replaced by the cmdNotNotified results and vice versa.

Thanks again.

-------------Code---------------------

Private Sub cmdNotNotified_Click()
On Error GoTo Err_cmdNotNotified_Click

Dim stDocName As String

stDocName = "Not Notified Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdNotNotified_Click:
Exit Sub

Err_cmdNotNotified_Click:
MsgBox Err.Description
Resume Exit_cmdNotNotified_Click


End Sub

 

[Allen Browne]

I have one more question.

Debbie, can I suggest you ask this as a new question?
I'm not sure I followed all that.

If you understood what the previous code does (building the WHERE clause,
you can do exactly the same thing, and apply it to OpenForm so it opens
another form filtered to matchinng record(s.) I think that's what you were
trying to achieve.

If you want an explanation of how that code works, there's a downloadable
example and explanation here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html