Build a report of form in datasheet view from vb code based on a filtered recordset.
Posted on 2006-07-21
I posted this earlier, but it seems to be sitting there. I really need to get this resolved. Can someone help me with the code to do what the title states? This is what I have so far in the module:
Private Sub cmdSubmit_Click()
Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset
rec.ActiveConnection = CurrentProject.Connection
rec.CursorType = adOpenKeyset
rec.LockType = adLockOptimistic
rec.Open "Select * from OpenClaimDetail"
Dim intCount As Integer
Dim intCount5 As Integer
DoCmd.Hourglass True (Thank you rockiroads!)
rec.Filter = "ClaimReceiptDate >= #" & Me!txtBeginDate & "# and ClaimReceiptDate<= #" & Me!txtEndDate & "#"
.....and then some more code. Somewhere in this area I'd like to print out the recordset after this filter (and some more filters that I still need to develop). I'd like to learn how to create a report (print preview) - giving the user the option to print it if they want to; or to send it to a form in datasheet view. I don't know how to write the code for this. I looked at the DoCmd.OutTo, etc., but couldn't get it. Thank you.
I did get the following response, but do not understand what to do:
if you only need it displayed on a form then you can bind the form directly to your recordset object
Set Me.Recordset = rec
but you can't do that for a report :-(
so you will need to either build the complete SQL and modify the query def the report is bound to or populate a temp table that your report is bound to.
Comment from c9k9h
Date: 07/20/2006 11:11AM PDT
I still don't understand. Where does the "Set Me.Recordset = rec" go? I don't have a form built. Can I generate one from within the code and reference the filtered "rec?"