NothingNew
asked on
Using loops to print records from Command Button
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("tblAsset Condition" )
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
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
DoCmd.RunCommand acCmdSaveRecord
Set db = CurrentDb()
Set rstAssetCondition = db.OpenRecordset("tblAsset
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("PrintPagesBeginDa te", "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)<<P roblem here
rstAssetCondition.MoveFirs t 'this causes error 3201 if no records
Do Until rstAssetCondition.EOF
DoCmd.Beep
rstAssetCondition.MoveNext
Loop
Else
End If
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
dtPrintPagesBeginDate = DLookup("PrintPagesBeginDa
dtPrintPagesEndDate = DLookup("PrintPagesEndDate
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)<<P
rstAssetCondition.MoveFirs
Do Until rstAssetCondition.EOF
DoCmd.Beep
rstAssetCondition.MoveNext
Loop
Else
End If
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.
2. Make sure that you take care dtPrintPageBeginDate and dtPrintPageEndDate in case of value is null.
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.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
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.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
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.