[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Using loops to print records from Command Button

Posted on 1998-12-04
5
Medium Priority
?
291 Views
Last Modified: 2008-03-17
I need to allow a user to select a date range and print records from a table by pressing a command button.

I am having a problem with the code.  Here is a sample of the direction I am going.  I am just trying to get the logic to work, so I don't have the print coding in yet.  I am struggling with how to print a range of records based on criteria provided by the user.  
_________________

Private Sub cmdPrintReportPages_Click()

Dim inFieldjobPMP As Integer
Dim dtDateDataCollected As Date
Dim db As Database
Dim rstAssetCondition As Recordset

inFieldjobPMP = DLookup("FieldjobPMP", "tblAssetCondition")
dtDateDataCollected = DLookup("DateDataCollected", "tblAssetCondition")

DoCmd.RunCommand acCmdSaveRecord
Set db = CurrentDb()
Set rstAssetCondition = db.OpenRecordset("tblAssetCondition")


If Me!PrintFieldJobPMP = 1 Then
   
        Do Until rstAssetCondition.EOF
   
If inFieldjobPMP = 1 And dtDateDataCollected >= Me!PrintPagesBeginDate _  And dtDateDataCollected <= Me!PrintPagesEndDate Then

            Do Until rstAssetCondition.EOF

            DoCmd.Beep ' just using Beep to check logic
           
            rstAssetCondition.MoveNext
            Else
         'Exit Do  
            End If
           
        Exit Do
       
    Loop
   
   
Else

End If

End Sub


Are there any standard conventions for accomplishing this goal?

I am really struggling with using a loop or what.  I can seem to get DO Until or Do While to work.  Any code with working loops would be greatly appreciated.

Cliff
0
Comment
Question by:NothingNew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Accepted Solution

by:
brewdog earned 80 total points
ID: 1970081
Where are you printing these records to? I can think of easier ways to do this, whether you're printing them one at a time or all together on a report, etc.

If you really want to do a loop, printing records one at a time, try something like this (I'm calling my recordset rs for brevity):

rs.MoveFirst
Do Until rs.eof
   If rs("FieldJobPMP") = 1 and rs("DateDataCollected") >= PrintPagesBeginDate and rs("DateDataCollected") <= PrintPagesEndDate Then
      . . . do your printing here
   End If
   rs.MoveNext
Loop

You might be better off, though, creating your recordset first, though, and filtering the records before creating the recordset, instead of checking every record one at a time. Something like this:

Dim strSQl as string
strSQL = "Select * from tblAssetCondition where FieldJobPMP = 1 and DateDataCollected Between #" & PrintPagesDateBegin & "# and #" & PrintPagesDateEnd & "#;"

set rs = currentdb().openrecordset(strSQL)
rs.movefirst   'this causes error 3201 if there are no records
do until rs.eof
  . . . do your print here
  rs.movenext
Loop

That's both simpler to code and faster than having to check every record after pulling the recordset. Hope this helps.

brewdog
0
 
LVL 2

Expert Comment

by:bclark100898
ID: 1970082
How about the OpenReport method?

For example,

Private Sub Command16_Click()
    DoCmd.OpenReport "Inspection Report by Week", acViewPreview, , "[Date] >= #" & [FormStartDate] & "# And [Date] <= #" & [FormFinishDate] & "#"

End Sub

This code is part of a command button.  FormStartDate and FormEndDate are fields on the form that the user fills in.
0
 

Author Comment

by:NothingNew
ID: 1970083
I am using the latter of your suggestions.  I get a Too Few Parameters error on the Set Recordset command.

Please take a look at my code(really your code) and see if you find any errors.  It appears to be getting the stSQL correct.  I can look in debug and see that it evaluates it correctly.

I says that rstAssetCondition = Nothing when I place my cursor over the text in Debug mode.
_________________________________________
Private Sub cmdPrintReportPages_Click()

Dim inFieldjobPMP As Integer
Dim dtPrintPagesBeginDate As Date
Dim dtPrintPagesEndDate As Date
Dim dtDateDataCollected As Date
Dim db As Database
Dim rstAssetCondition As Recordset
Dim stSQL As String


inFieldjobPMP = DLookup("FieldjobPMP", "tblAssetCondition")
dtDateDataCollected = DLookup("DateDataCollected", "tblAssetCondition")
dtPrintPagesBeginDate = DLookup("PrintPagesBeginDate", "tblMainReport")
dtPrintPagesEndDate = DLookup("PrintPagesEndDate", "tblMainReport")



DoCmd.RunCommand acCmdSaveRecord
Set db = CurrentDb()


If Me!PrintFieldJobPMP = 1 Then
   
   stSQL = "Select * from qryConditionReport where FieldjobPMP = 1 and DateDataCollected Between #" & dtPrintPagesBeginDate & "# and #" & dtPrintPagesEndDate & "#;"
   
   Set rstAssetCondition = db.OpenRecordset(stSQL)<<Problem here
   rstAssetCondition.MoveFirst 'this causes error 3201 if no records
   Do Until rstAssetCondition.EOF
   DoCmd.Beep
   rstAssetCondition.MoveNext
   Loop
   
   
Else

End If
   
   
 
0
 
LVL 1

Expert Comment

by:surrealism
ID: 1970084
1. Make sure that you can open qryConditionReport properly. (Just click Open button from Database window)
2. Make sure that you take care dtPrintPageBeginDate and dtPrintPageEndDate in case of value is null.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6875837
GREETINGS!

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.  Also in the Community Support link is a question on how experts can help, if they wish, on the cleaning of old and abandoned questions.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

649 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