Solved

Build a report of form in datasheet view from vb code based on a filtered recordset.

Posted on 2006-07-21
29
324 Views
Last Modified: 2008-02-01
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.

Steve
 
Comment from c9k9h
Date: 07/20/2006 11:11AM PDT
 Your Comment  


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?"
 
0
Comment
Question by:c9k9h
  • 16
  • 13
29 Comments
 

Author Comment

by:c9k9h
Comment Utility
I think I can better explain what I'm trying to do.  I have a button on a form (Submit).  When a user clicks the button, it generates a recordset based on filters (in which the user entered a date range, choices from list boxes, etc.).  The form has text boxes that are filled in with the result set from the recordset.  For example:

Do Until rec.EOF
    If (dateCalc(DateValue(rec!ClaimReceiptDate), DateValue(rec![1stActTakenDate]))) > 5 Then
        intCount5 = intCount5 + 1
    EndIf
    rec.MoveNext
    intCount = intCount + 1
Loop

PercentActTaken = (intCount - intCount5) / intCount  
NoAction = intCount5

Me!txtActionTaken.Value = PercentActTaken
Me!txtNoAction.Value = NoAction

This is a small sample, but hopefully helps explain.  In addition to this form which shows results, I have check boxes that I would like to use if a user wants to see the records that make up what is behind a value in a text box.   (ie.  Click the check box to see what records make up the value in Me!txtNoActin.Value.)  

I was thinking some possible ways to do this are to generate a datasheet form when the checkbox is activated, or generate a report that lists the records.  If there are any other suggestions, I'm certainly open to hear them.  

Thank you!


0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
maybe not the best way but I did test this and it does work ... you need to create a new form that already has the fields that will be in your resulting recordset.

create a new standard module and add a global variable for your recordset ...
    Public rec As ADODB.Recordset


now in the form with the submit button ...

Private Sub cmdSubmit_Click()
    Set rec = New ADODB.Recordset
    rec.ActiveConnection = CurrentProject.Connection
    rec.CursorType = adOpenKeyset
    rec.LockType = adLockOptimistic
    rec.Open "Select * from Table1"

    DoCmd.OpenForm FormName:="frmNew"
End Sub

and finally in the form you want to display the data ...
Private Sub Form_Open(Cancel As Integer)
    Set Me.Recordset = rec
End Sub
0
 

Author Comment

by:c9k9h
Comment Utility
stevbe, I followed your outline but I'm getting on one row returned with #Name? in every feld.  Below is the code for my Submit button:

Private Sub cmdSubmit_Click()

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 intCountAction5 As Integer
Dim intUndecidedClaims As Integer

'TURN ON THE HOUR GLASS SO USER KNOWS THE APPLICATION IS PROCESSING
DoCmd.Hourglass True
'ADD TEXT TO STATUS LINE AT BOTTOM OF SCREEN
SysCmd acSysCmdSetStatus, "Processing request . . ."

'FILTER OUT THE -OpenClaimDetail- TABLE TO BRING IN THE DATE RANGE A USER ENTERS.
rec.Filter = "ClaimReceiptDate >= #" & Me!txtBeginDate & "# and ClaimReceiptDate<= #" & Me!txtEndDate & "#"

'USING THE FILTERED RECORDSET ABOVE, LOOP THROUGH EACH RECORD TO FIND THOSE THAT MEET THE CRITERIA
'STATED BELOW, UNTIL THE END OF FILE IS REACHED.
Do Until rec.EOF

    'HANDLE NULL VALUES BEING PASSED IN
    If (IsNull(rec![1stActTakenDate])) Then
        rec![1stActTakenDate] = 0
    End If
   
    'RUN FUNCTION TO STRIP OUT THE HOLIDAYS AND WEEKENDS - THEREFORE ONLY COUNTING WORK DAYS
    'PASS IN THE CLAIM DATE AND 1st ACTION TAKEN DATE FOR VARIABLES.
    If (dateCalc(DateValue(rec!ClaimReceiptDate), DateValue(rec![1stActTakenDate]))) > 5 Then
   
        'SET THE COUNTER TO COUNT THOSE RECORDS THAT TOOK MORE THAN 5 DAYS TO GET A 1st ACTION DATE.
        intCountAction5 = intCountAction5 + 1
    End If
   
    'DEBUG PRINT TO SEE RESULTS - MOVE THIS CODE WHEN FINISHED
    Debug.Print rec!ID, DateValue(rec!ClaimReceiptDate), DateValue(rec![1stActTakenDate]), rec!Unit, dateCalc(DateValue(rec!ClaimReceiptDate), DateValue(rec![1stActTakenDate]))
   
    'MOVE TO THE NEXT RECORD
    rec.MoveNext
   
    'SET A COUNTER TO COUNT EACH RECORD AS THE CODE LOOPS THROUGH.
    intCount = intCount + 1
 
Loop

'ASSIGN VARIABLES
PercentActTaken = (intCount - intCountAction5) / intCount
NoAction = intCountAction5
Debug.Print intCount, NoAction, PercentActTaken

'ASSIGN VALUE OF VARIABLES TO TEXT BOXES ON FORM
Me!txtActionTaken.Value = PercentActTaken
Me!txtNoAction.Value = NoAction

'CLOSE THE RECORDSET
rec.Close
Set rec = Nothing

'PRINT OUT NEW FORM IN DATASHEET VIEW TO SHOW RECORDS FROM ABOVE RESULT SET,
'IF THE CHECK BOX ON ORIGINAL FORM HAS BEEN SELECTED
If chkActionTaken = True Then
    DoCmd.OpenForm "frm_ActionTaken", acFormDS
End If

DoCmd.Hourglass False
SysCmd acSysCmdClearStatus

End Sub

Do the records that meet the criteria and need to be printed need to be captured with the loop and sent out to the new form somehow?  
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
don't close and set to nothing the rec variable until you get to the close event of the frm_ActionTaken form.

Steve
0
 

Author Comment

by:c9k9h
Comment Utility
Whoops! That certainly makes sense!.

I'm now getting a run-time error on the middle line of this code:

Private Sub Form_Open(Cancel As Integer)
    Set Me.Recordset = rec
End Sub

Run-time error '5': Invalid procedure call or argument

This is making me crazy.
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
what version of Access?

I am running that exact code fine on 2003 and I am confident it should work with XP, not so sure about 2000 and know it won't work in 97 or less.

Steve
0
 

Author Comment

by:c9k9h
Comment Utility
FYI, this is what the Immediate window looks like when the Debug.Print is executed: (small sample)
.........
36999    7/1/2006    7/7/2006       VWB1     3
38068    7/3/2006    7/717/2006    VWB2     9
.......
98                    3            0.969387755102041

If it can print to the immediate window, is it possible to print to some sort of object?  A form or report?
0
 

Author Comment

by:c9k9h
Comment Utility
I'm running 2003, as well.   When debugging, I can see that the value of 'Me.Recordset in:  'Set Me.Recordset = rec', is 'Nothing.'  I have the RecordSource on the new form (frm_ActionTaken) set to the OpenClaimDetail table.  Is that correct?  I did try it without it, but got the same error.  I don't understand how the new form knows what rec is?
0
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
Comment Utility
because you declare rec in a seperate module as Public that means that any other procedure in your entire application can get to it ... Access will find rec for you ... just don't make a variable with that same name in a procedure where you want to use the public var in ... the short part is ... the way you have the code should work ...
did you move ...

'CLOSE THE RECORDSET
rec.Close
Set rec = Nothing


from
Private Sub cmdSubmit_Click() on the submit form

and put it into the close event of the frm_ActionTaken form?
0
 

Author Comment

by:c9k9h
Comment Utility
Hi Steve,

I had moved the close recordset code below when I called for the form to open, but I had not put it in the close event of the frm_ActionTaken.  I just did that.  I then got an error of 'Object Required.'

Thank you for your explanation about the Public declaration.  I actually did think of that earlier.  If you notice the code above for the Submit command button - it is Private.  I changed it (hoping that would miraculously fix everything!).

I found a similar thread at this address: http://www.experts-exchange.com/Databases/MS_Access/Q_21297812.html, where it talks about DAO vs ADO and some of the problems.  Do you think that might be an issue here?

Thank you,
Karen
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
Hi Karen,

    1. Do you have Option Explicit at the very top of all your form / modules?
    2. Can you Compile without error?

Steve
0
 

Author Comment

by:c9k9h
Comment Utility
1.  Option Comapre Database is at the top of all my forms/modules.  I just added Option Explict.
2.  No, I'm getting a Compile error:  Variable not defined  on the Set Me.Recordset = rec line.
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
ok ... that will be the problem ...

did you create a new standard module and add a global variable for your recordset ...
    Public rec As ADODB.Recordset
0
 

Author Comment

by:c9k9h
Comment Utility
Steve,  I just noticed I did not have the 'Public rec As ADODB.Recordset' line in its own module.  I had moved it while trying something.  Anyhow, I just put it back and I am no longer getting a compile error, however I am getting the Run-time error '5':  Invalid procedure call or argument.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:c9k9h
Comment Utility
You're way ahead of me! :)
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
<Invalid procedure call or argument.>
what line are you getting this error on?
0
 

Author Comment

by:c9k9h
Comment Utility
I'm sorry, I meant to post that.   Set Me.Recordset = rec
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
huh ... I am doing exactly what you are and not getting any errors, I even use similar code in a production app.

Is this an .mdb file?

I have put together a small sample mdb (140 kb) and will see if I can find a place to post it so you can take a look.

Steve
0
 

Author Comment

by:c9k9h
Comment Utility
Can you email it to me?  I think you will need to change the extension or the firewall here won't let it through.  I can change it back once I save it.  krhill@unumprovident.com.   . . . Yes, my file is an .mdb file.
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
unfortunaely EE frowns severly on solving questions through email. I will take a chance ... you have to post back on this question what you found, if anything, that you found helpful.

Steve
0
 

Author Comment

by:c9k9h
Comment Utility
Steve - Not sure if you tried a second time - nothing has come through.  I'm leaving for today but will check again tomorrow.   Thanks!  
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
I sent with my initials as the extension a couple days ago
0
 

Author Comment

by:c9k9h
Comment Utility
Steve,  I got the file . . . thanks!  I don't think we're comparing apples with apples.  Your example calls to use the recordset on the same form.  I need to pass the recordset values to another form:

'Print out a NEW FORM in datasheed view to show records from above result set, if the check box on original form has been selected.

If chkActionTaken = True Then
    DoCmd.OpenForm "frm_ActionTaken", acFormDS
End If

I want the values from the filtered recordset in the ORIGINAL form to be passed to a new form "frm_ActionTaken."

Do you know if that can that be done?
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
yes ...

I simplified the sample to make sure you did not have anything horribley going wrong.

Let me see if I can set up a sample more like what you are doing, I did my test is in a scratch mdb with lots of other junk:-)
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
Hold on ... I set up the example and looked at a few things ...

if frmNew is already open then it's open event will not get triggered so you need to either make sure it is closed.
if you have anything else that is opening frmNew and the rst variable has not been populated you will get an error.

Take a look at those 2 issues and let me me know:-)
0
 

Author Comment

by:c9k9h
Comment Utility
frmNew should never be open.  If I need to, I can add code to the 'submit' button to close it (as soon as the code begins) in case a user does leave it open.  And no, it will not be able to be opened anywhere but from the 'submit' button on the original form.  
I did come up with a sort of 'work-around,' where I execute a query that runs the same code to get the exact resultset.  Then I build a report using that query.  This may be okay, but I'd rather not have to run the same code twice.  Also, it runs SO slow.  Thanks for your perseverance!
0
 

Author Comment

by:c9k9h
Comment Utility
Steve . . . it looks good, and looks like what I need it to do.  Except mine won't do it.  Do you think it's the filter that's throwing it off?  I don't have time right now, but will add a filter to yours later and see if it changes anything.  Thanks for sending!
0
 

Author Comment

by:c9k9h
Comment Utility
WOO HOO!!!  Got it!!!  It was the rec.Filter that caused the problem.  Instead of these two lines:
rec.Open "Select * from OpenClaimDetail"
and
rec.Filter = "ClaimReceiptDate >= #" & Me!txtBeginDate & "# and ClaimReceiptDate<= #" & Me!txtEndDate & "#"

I moved the filter to the rec.Open SQL statement:
rec.Open "Select * from OpenClaimDetail WHERE ClaimReceiptDate >= #" & Me!txtBeginDate & "# and ClaimReceiptDate<= #" & Me!txtEndDate & "#"

and whola...  I am able to generate a datasheet view form from the code for the 'Submit' button of the original form.

Thanks a million for hanging in there and sending me your samples, Steve!!!

0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
I am glad to hear you have it working!

Not sure why setting a filter on the recordset would invalidate the recordset object itself but it looks like you just uncovered a limitation in using in-memory recordset and binding them to forms.

Steve
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now