[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

What's wrong here

Hi, can you correct this

Public Function RecordObjectPrint()
On Error GoTo Err_RecordObjectPrint
Dim dbCurr As Database
Dim strsql As String
Set dbCurr = CurrentDb()
Dim recStudentPrintsRead As Recordset
Dim recStudentPrintsWrite As Recordset
strsql = Reports(Application.CurrentObjectName).RecordSource
Set recStudentPrintsRead = dbCurr.OpenRecordset(strsql)
Set recStudentPrintsWrite = dbCurr.OpenRecordset("usysQopPrints", dbOpenDynaset)

    While Not recStudentPrintsRead.EOF
    With recStudentPrintsWrite
        .AddNew
        !UserName = CurrentUser()
        !PrintDateTime = Now()
        !ObjectPrinted = ObjectNamePrint()
        !SelectedDate = !ClassDate
        !StudentID = !StudentID
        !Class = !Class
        !Period = !Period
        .Update
    End With
    Wend
    DoCmd.PrintOut
   
Exit_RecordObjectPrint:
Exit Function

Err_RecordObjectPrint:
    Resume Exit_RecordObjectPrint
End Function
0
RonOsborne
Asked:
RonOsborne
  • 14
  • 7
  • 7
  • +2
3 Solutions
 
flavoCommented:
Hi RonOsborne,
What's not working / what's erroring out?
Dave :-)
0
 
flavoCommented:
What are you trying to do?  
Dave
0
 
nico5038Commented:
Try to read the next record like in:

    While Not recStudentPrintsRead.EOF
    With recStudentPrintsWrite
        .AddNew
        !UserName = CurrentUser()
        !PrintDateTime = Now()
        !ObjectPrinted = ObjectNamePrint()
        !SelectedDate = !ClassDate
        !StudentID = !StudentID
        !Class = !Class
        !Period = !Period
        .Update
    End With
   recStudentPrintsRead.MoveNext
    Wend

Nic;o)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
peter57rCommented:
Hi RonOsborne,
What's wrong with it? (error message?)

Pete
0
 
RonOsborneAuthor Commented:
Error for this is 3061, Too few parameters. Expect 1

Dim dbCurr As Database
Dim strsql As String
Set dbCurr = CurrentDb()
Dim recStudentPrintsRead As Recordset
Dim recStudentPrintsWrite As Recordset
strsql = Reports(Application.CurrentObjectName).RecordSource
Set recStudentPrintsRead = dbCurr.OpenRecordset(strsql)
Set recStudentPrintsWrite = dbCurr.OpenRecordset("usysQopPrints", dbOpenDynaset)

    While Not recStudentPrintsRead.EOF
        With recStudentPrintsWrite
            .AddNew
            !UserName = CurrentUser()
            !PrintDateTime = Now()
            !ObjectPrinted = ObjectNamePrint()
            !SelectedDate = !ClassDate
            !StudentID = !StudentID
            !Class = !Class
            !Period = !Period
            .Update
        End With
        recStudentPrintsRead.MoveNext
    Wend
    DoCmd.PrintOut
 
0
 
flavoCommented:
Either Reports(Application.CurrentObjectName).RecordSource or usysQopPrints are prompting you for a parameter or you have a mis-spelt field name.  Can you open the in the Query Builder, do they prompt you for anything?
Dave
0
 
RonOsborneAuthor Commented:
This function is called from a button on the reports toolbar
It uses the RecordSource of the report
0
 
RonOsborneAuthor Commented:
Flavo
I just put a breakoint in the function and stepped through it
Reports(Application.CurrentObjectName).RecordSource returns only the report name and not the whole RecordSource
0
 
Arthur_WoodCommented:
surely your inner loop :

  With recStudentPrintsWrite
        .AddNew
        !UserName = CurrentUser()
        !PrintDateTime = Now()
        !ObjectPrinted = ObjectNamePrint()
        !SelectedDate = !ClassDate
        !StudentID = !StudentID
        !Class = !Class
        !Period = !Period
        .Update
  End With

is wrong.  You appear to be COPYING the fields CLassDate, StudentID, Class and Period from each record into the same fields in the SAME RECORD in the SAME RECORDSET!!!!  I suspect what you intended was:

  With recStudentPrintsWrite
        .AddNew
        !UserName = CurrentUser()
        !PrintDateTime = Now()
        !ObjectPrinted = ObjectNamePrint()
        !SelectedDate = recStudentPrintsRead.ClassDate
        !StudentID = recStudentPrintsRead.StudentID
        !Class = recStudentPrintsRead.Class
        !Period = recStudentPrintsRead.Period
        .Update
   End With

Thereby copying the values from the StudentsPrintRead recordset to the StudentsPrintWrite recordset.

AW
0
 
RonOsborneAuthor Commented:
Hi Arthur_Wood
It may look like that, but it just happens that some of the field names are the same in each recordset, ie StudentID, Class and Period

I think the syntax can be
!SelectedDate = recStudentPrintsRead!ClassDate or !SelectedDate = !ClassDate

But I still have the original error of 3061, Too few parameters. Expect 1
which is caused by this line
strsql = Reports(Application.CurrentObjectName).RecordSource
0
 
flavoCommented:
Report name?  Doesn't display the table / query that the report is bound too??
0
 
RonOsborneAuthor Commented:
What does give me the report recordsource ?
0
 
flavoCommented:
>What does give me the report recordsource ?

Reports(Application.CurrentObjectName).RecordSource

Are you sure Application.CurrentObjectName is returning the right report name?  

Add this after this line:

strsql = Reports(Application.CurrentObjectName).RecordSource
debug.print strSQL
debug.print Application.CurrentObjectName

And post back the results from the immediate window (hit ctrl + g to open it)
0
 
RonOsborneAuthor Commented:
Hi Flavo

The line
strsql = Reports(Application.CurrentObjectName).RecordSource
is the problem, as it returns the module name that the function is in and not the report RecordSource

I'll explain as much as I can
This is a public function that is called by a toolbar button of various reports
Each of these reports has similar RecordSources which contain the data I need to record
Basically it records which records are printed, by CurrentUser, Date and Report name printed

Hope this makes things a bit clearer than mud

PS can't use debug.print as it errors on the strsql line before getting to the debug.print strSQL
0
 
flavoCommented:
There's your problem.

Don;'t use Application.CurrentObjectName.. Can you hard code the report name like this?

Reports("rptMyReport").RecordSource

Does the report's RecordSource even change?? Can you just hard code the value of strSQL?

What are you even trying to do here?!?
0
 
RonOsborneAuthor Commented:
Just tried

strsql = Reports(Application.CurrentObjectName).RecordSource
MsgBox strsql
GoTo Exit_RecordObjectPrint

msgbox = report name
but not the report RecordSource
0
 
peter57rCommented:
My guess would be that your report has the same name as its recordsource (in other words your recordsource is the name of a table or query).

Pete
0
 
RonOsborneAuthor Commented:
Hi All

Sorry I was wrong
strsql = Reports(Application.CurrentObjectName).RecordSource
is returning the report RecordSource (too late at night to read correctly)
but it still gives the error 3061, Too few parameters. Expect 1

Some examples of report names and RecordSources

report = Truancy Letter 06 to 09, RecordSource = QryTruancyLetter-06-07-08-09
report = Truancy Letter 10 to 12, RecordSource = QryTruancyLetter-10-11-12
0
 
peter57rCommented:
You need to run the query on its own to see if you have the same problem.
If not, then there is probably an issue with parameters - are you referring to form- based values when the form is not open?

Pete
0
 
flavoCommented:
If you open QryTruancyLetter-06-07-08-09 with the query builder does it prompt you for a parameter?
0
 
RonOsborneAuthor Commented:
Yes the QryTruancyLetter-06-07-08-09 refers to a form for the date
0
 
peter57rCommented:
Then the form must be open for the query and report to run.

Pete
0
 
RonOsborneAuthor Commented:
The referenced form is always open in the background
I just tested the function without the reference to the form for the date and it worked fine
It's appears to be the reference to the form for the date that causes the problem with the function
Could it be that the report is locking the query ?
0
 
peter57rCommented:
If it's a date parameter make sure the parameter is defined as such in the Parameters dialog in the Query menu (with the query open in design view).

Pete

0
 
RonOsborneAuthor Commented:
I have criteria in the query of [Forms]![usysFrmMain]![txtRollDate] in the ClassDate column
But are you refering to placing that in the parameters dialog box
If so I have no idea how to write that into the parameters dialog box
What is the syntax for that
0
 
peter57rCommented:
Copy the criteria expression
[Forms]![usysFrmMain]![txtRollDate]
and then use Ctrl+V to paste it into the Query Parameters dialog.
Set the datatype to datetime.

Pete
0
 
RonOsborneAuthor Commented:
Just tried that, same error err.number 3061, Too few parameters. Expect 1
It errors on the following line
Set recStudentPrintsRead = dbCurr.OpenRecordset(strsql)
But this works fine if I remove the date citeria or hard code a date in the criteria

When I remove the parameter and criteria all works good (apart from all records being in the query)

In the query(s) I tried [Forms]![usysFrmMain]![txtRollDate] as the criteria, then as a parameter (datatype datetime) and then as the criteria and parameter at the same time, no joy
0
 
peter57rCommented:
Right. I see.

I thought you were telling us that the error occurred when you opened the report. My misinterpreatation perhaps.
This error is due to the fact that the reference to the form cannot be resolved within the Openrecordset statement.
To use an external parameter you need something like

strsql = " Select * from mytable where mydatefield = #" & forms!myform!mydate & "#"

currentdb.openrecordset(strsql)

This resolves the parameter value in the strsql assignment, so that it is already known when the openrecordset statement is executed.

Pete
0
 
RonOsborneAuthor Commented:
OK, thanks, that has answered my question, but it doesn't solve my problem, I'll have to try another method to do the whole thing
I'll post another question for that, I'll place a link to the new question here later if you areinterested in it
0
 
RonOsborneAuthor Commented:
Had any experience with the treeview object in MS Access?
Try this question http://www.experts-exchange.com/Databases/MS_Access/Q_21789613.html

And thanks guys

I think I'll use a method that uses the Reports(Application.CurrentObjectName).RecordSource as a variable in an append query to record the records
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 14
  • 7
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now