Report based on Recordset 2

[Rzrbkpk]

I'm wanting to create a report in Access based on the results of the recordset I ran in VBA. Here is the rundown on the necessaries:

The SQL is pulling from a table named “tblINF_Main_Table” based on the data entered into a form named “Inv” with the field “SearchCrit_Store”. The form’s purpose is to allow the user to search for records based on multiple criteria. For this reason, I am hard coding instead of using Access queries. There will eventually be a long list of conditions and keeping up with that many queries would be a hassle. The code does work, but I’m clueless as to how to output the results and create a report on those results.



Function INFsearch()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
strSELECT = "SELECT * FROM tblINF_Main_Table" & " "


If [Forms]![Inv]![SearchCrit_Store] <> "" Then
strWHERE = "WHERE [Store Number] IN" & "(" & [Forms]![Inv]![SearchCrit_Store] & ")"


Else
Exit Function

End If

strSQL = strSELECT & strWHERE
Set rs = db.OpenRecordset(strSQL)

Do While Not rs.EOF
Debug.Print rs.Fields("Record Indicator")
rs.MoveNext
Loop


End Function

 

[MajP]

Public Sub setRs()
Dim rs As DAO.Recordset
dim strSql as string

If [Forms]![Inv]![SearchCrit_Store] <> "" Then
strSql = "SELECT * FROM tblINF_Main_Table"
strSql = strSql & "WHERE [Store Number] IN" & "(" &
[Forms]![Inv]![SearchCrit_Store] & ")"
Set rs = currentdb.OpenRecordset(strSQL)
DoCmd.OpenReport "rptName", acViewDesign
Reports("rptName").RecordSource = rs.Name
DoCmd.OpenReport "rptName", acPreview
End If
End Sub

 

[Rzrbkpk]

Thank you, Thank you, THANK YOU! That did exactly what I wanted. One minor question though. When I close the report, it prompts to save changes. Any suggestions for bypassing that? I don't want the end-user to have to make that decision each time.

 

[Rzrbkpk]

Disregard. I solved it by placing a save command after the design change. See below:

If [Forms]![Inv]![SearchCrit_Store] <> "" Then
strSql = "SELECT * FROM tblINF_Main_Table"
strSql = strSql & "WHERE [Store Number] IN" & "(" &
[Forms]![Inv]![SearchCrit_Store] & ")"
Set rs = currentdb.OpenRecordset(strSQL)
DoCmd.OpenReport "rptName", acViewDesign
Reports("rptName").RecordSource = rs.Name
DoCmd.Save
DoCmd.OpenReport "rptName", acPreview
End If
End Sub