Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access Training and help » Reports

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Reports

Resolved · Medium Priority · Version 2007

Reply Wed 1 Apr 2009, 16:41Delegate Hanan said...

Hanan has attended:
Access VBA course

Reports

Recently, when running a report, it shows fine on print preview. But when we try to print it, a message comes up saying:

Error 3014
Reached the limit on number of tables that can be opened at 1 time.

It was fine before, no work has been done to this report.

Thanks

For upcoming training course dates see: Pricing & availability

Reply Wed 8 Apr 2009, 06:40Trainer Rajeev said...

RE: Reports

Dear Hanan

Thank you for attending Access VBA course!! I hope you enjoyed the course and benefited from it.

Please refer to the following link which should be able to provide you more info about this error and also possible solution for it.

http://www.mvps.org/access/bugs/bugs0010.htm

I hope this has answered your query.

If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!

Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000 and 2003
MCAS Master Instructor 2007
MCT

Reply Wed 8 Apr 2009, 09:15Delegate Hanan said...

RE: Reports

Sorry, I have run the jet35Upd.exe, but it still does not work. still giving the same message.

Reply Wed 8 Apr 2009, 16:15Trainer Rajeev said...

RE: Reports

Dear Hanan

Thank you for your response.

This seems to be more of a VBA query so I'll pass on this post to one of my colleague who has more experience in using VBA and I am sure they would be able to help you in resolving your query!!

Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000 and 2003
MCAS Master Instructor 2007
MCT

Reply Wed 22 Apr 2009, 12:12Delegate Hanan said...

RE: Reports

It is a large report that looks at a selection of menu option e.g. what ethnicity, borough, age etc then the user selects which report then previews it. When this particular report is selected the user can preview it, but is unable to print it, the message "cannot open any more tables" keeps popping up. Then the report is printed with some data is missing.

Case 21 'All live Clients between dates
If Not IsNull(Me![txtDtStart]) And Not IsNull(Me![txtDtEnd]) And Me![txtDtEnd] > Me![txtDtStart] Then
stDocName = "repDatesTagOverAll"
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelClientStatus"
DoCmd.OpenQuery "qappCTypASC"
DoCmd.OpenQuery "qappCTypCOCGrp"
DoCmd.OpenQuery "qappCTypASO"
DoCmd.OpenQuery "qappCTypCOCInd"
DoCmd.OpenQuery "qappCTypNCCGrp"
DoCmd.OpenQuery "qappCTypNCCInd"
DoCmd.OpenQuery "qappCTypNEC"
DoCmd.SetWarnings True
ElseIf IsNull(Me![txtDtStart]) Or IsNull(Me![txtDtEnd]) Then
DoCmd.GoToControl "txtDtStart"
DoCmd.OpenForm "frmMsgBox", acNormal
Forms![frmMsgBox]![txt1] = "Before you can run the report ... "
Forms![frmMsgBox]![txt2] = "You must enter Start and End dates!"
Exit Sub
ElseIf Me![txtDtStart] >= Me![txtDtEnd] Then
DoCmd.GoToControl "txtDtStart"
DoCmd.OpenForm "frmMsgBox", acNormal
Forms![frmMsgBox]![txt1] = "Data Error ... "
Forms![frmMsgBox]![txt2] = "The Start Date must be smaller than the End Date!"
Exit Sub
End If

This case statement call up the Report "repDatesTagOverAll" which inturn calls up alot of other reports, which opens up other queries etc....

Please I would also like to know how to create a function that opens and closes the tables and queries which uses recordsets.

Thank you

Reply Wed 22 Apr 2009, 13:04Trainer Stephen said...

RE: Reports

Hi Hanan

Thanks for that

I suggest that instead of opening your queries with the DoCmd object, you use the data access object (DAO) model. These techniques were covered on the VBA Access course that you attended. These would also be used to open and close tables and queries.

In summary, you declare a DAO database variable and set it to the current database, and then declare recordset variables for each query. You can then populate the report from the recordsets.

I suggest typing DAO into the help control in the VBE for further information

Regards

Stephen

Reply Wed 22 Apr 2009, 10:29Trainer Stephen said...

RE: Reports

Hi Hanan

Thank you for your question.

There are a number of possible reasons for this error. The first question is how is the report being run?

If it is from a sub procedure, then it would be helpful if I could see the code behind it. IF this is the case, could you copy and paste the code into a text document and email it to me at Stephen and I will investigate

Regards

Stephen

Tue 28 Apr 2009: Automatically marked as resolved.

Access articles

· Use Split Form View In Access For Simultaneous Views
· Microsoft Access Flaws and Ways to Overcome Them
· Find Out About The Join Property In An Access Query
· So what's new in Access 2007?
· How Access Can Provide The Key To Successful Stock Management

Please browse our web site to find out more about
training for microsoft access and other Microsoft training courses.

Access tip:

Deleting duplicate records from a table

You cannot delete records tables where there duplicate records. A way around this is to create a new table which wont hold the duplicates. and then deleting the old table.

1. Use a make-table query based on this table only. IMPORTANT - Ensure that you include all of the fields from your original table or you may loose data.

2. Open the query's property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values property to Yes

3. Because you have selected the Unique Values to Yes when you run the query, Access creates a new table without duplicate records. You can now delete the old table and rename the new one.

View all Access hints and tips