Report Record Source from Form?

[Gary]

Hello all,

I have a form that we use for looking up specific sorts of packaging details
based on a few criteria (Due Date, Type of Packaging and Status of Job)

The details are all pulled from two tables using a custom written SQL
string. Then the subform displays the data that is returned by the custom
SQL recordsource.

However, I'd like to also build a report to print out a hard copy of the
data, but I'm not sure how to call the data recordsource (SQL statement) to
only display those records that were returned when the record source was
updated. The basic flow is this:

User inputs dates for data, as well as Different status codes and packaging
types (if necessary - otherwise default values are used).
User presses button to update the subform, which displays in subform window

I would ideally like to have the report update via this "current" data
recordsource. I tried a variety of ideas, from setting the text boxes equal
to the subform fields to trying to get the report to read the updated
recordsource in the form.

Any thoughts or suggestions greatly appreciated!

Gary

 

[Geof Wyght]

Gary,
Create a query and save it. Make the query the record
source for the subform. Then update the query text via DAO
(easier than ADO in my opinion) whenever you make a
selection change. Make the same query the record source
for your report. That way the report will always show what
you selected in the subform.
Geof.

 

[Gary]

Hi Geof,

Thanks for the lead...not entirely sure I follow the "hows" but I get the
idea of the "whats" (if you follow my meaning...) I used a query originally
but the query doesn't allow the level of 'filtering' that the custom code
does, so the subform's recordsource is now a custom SQL statement instead.
Not too sure how to get the query to "update" to my custom SQL statement?

Gary
P.S. I included the code of my "Process" button below, if it helps?
--
Private Sub cmdProcess_Click()
On Error GoTo err_cmdProcess

Dim SQLstmt As String
Dim strDate As String
Dim strPKDetail As String
Dim strStartStatus, strEndStatus As String

strDate = "between #" & Me.txtStartDate & "# and #" & Me.txtEndDate &
"#"
strPKDetail = Me.txtPackType
strStartStatus = Me.txtStartStatus
strEndStatus = Me.txtEndStatus


SQLstmt = "SELECT DISTINCTROW ORDERS.Order_No, ORDERS.Due_Date,
ORDERS.Billto_Name, ORDERS.Status," & _
" ORDER_DUB_DETAILS.Product_or_Service,
ORDER_DUB_DETAILS.Title, ORDER_DUB_DETAILS.Qty_Ordered" & _
" FROM ORDERS INNER JOIN ORDER_DUB_DETAILS ON" & _
" ORDERS.Order_No = ORDER_DUB_DETAILS.Order_No" & _
" WHERE (((ORDERS.Due_Date) " & strDate & ") AND
((ORDER_DUB_DETAILS.Product_or_Service)" & _
" LIKE '" & strPKDetail & "') AND ((ORDERS.Status) Between '"
& strStartStatus & "'" & _
" And '" & strEndStatus & "')) ORDER BY ORDERS.Order_No DESC;"

Me.sfrmPackDetails.Form.RecordSource = SQLstmt
Me.sfrmPackDetails.Form.Requery

'Query!qryPackDetails.RecordSource = SQLstmt


exit_cmdProcess:
Exit Sub
err_cmdProcess:
MsgBox Err.Number & ": " & Err.Description, , "Order Entry"
Resume exit_cmdProcess

End Sub

 

[Geof Wyght]

Gary,
In more detail, take your sql statement and make a new
query from it and save it. Say it's called QSEL_Orders.
Then when you have an event that changes the query string
or filters, then re-create the string and do the following:
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("QSEL_Orders")
qdf.SQL = SQLstmt
Set qdf = Nothing
Now if you're in a form you probably have to do:
Me.RecorSource="QSEL_Orders".
If you are filtering then get rid of that part because you
have actually changed the record source.
If your report which also has as a record source
QSEL_Orders and if the report is not open already, then
when you open it, you'll see the records you're expecting
to see, no?

Geof.

-----Original Message-----
Hi Geof,

Thanks for the lead...not entirely sure I follow the "hows" but I get the
idea of the "whats" (if you follow my meaning...) I used a query originally
but the query doesn't allow the level of 'filtering' that the custom code
does, so the subform's recordsource is now a custom SQL statement instead.
Not too sure how to get the query to "update" to my custom SQL statement?

Gary
P.S. I included the code of my "Process" button below, if it helps?
--
Private Sub cmdProcess_Click()
On Error GoTo err_cmdProcess

Dim SQLstmt As String
Dim strDate As String
Dim strPKDetail As String
Dim strStartStatus, strEndStatus As String

strDate = "between #" & Me.txtStartDate & "# and #" & Me.txtEndDate &
"#"
strPKDetail = Me.txtPackType
strStartStatus = Me.txtStartStatus
strEndStatus = Me.txtEndStatus


SQLstmt = "SELECT DISTINCTROW ORDERS.Order_No, ORDERS.Due_Date,
ORDERS.Billto_Name, ORDERS.Status," & _
" ORDER_DUB_DETAILS.Product_or_Service,
ORDER_DUB_DETAILS.Title, ORDER_DUB_DETAILS.Qty_Ordered" & _
" FROM ORDERS INNER JOIN ORDER_DUB_DETAILS ON" & _
" ORDERS.Order_No =

ORDER_DUB_DETAILS.Order_No" & _

 

[Gary]

Geof,

Thanks very much! That worked brilliantly! Thanks for taking the time to
answer my question and get me rolling down the right path!

Cheers!
Gary