Access 2003 .adp Subform problem

Question

Access 2003 .adp Subform problem

PaulSp

SSCrazy

Points: 2499
More actions
December 21, 2006 at 4:55 am

#193547

Hi
I have a subform containing student details. There is a report button within the subform to print any individual record. What I am finding is that each time I print (say print record number 7 of 15), the subform has returned to record 1 when I close the report. It is as if there is a Requery set in vba - which there isn't. This never happened when I used my previous .mdb.
Does anybody have any idea what is going on here?
Many thanks
Paul

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Vic Rauch-303403 SSCommitted Points: 1576 More actions · Answer 1

Paul, I have no idea why you are experiencing this behavior, but a workaround would be to save the bookmark, then return to that bookmark after the report has run.

Hope this helps,

Vic

[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com

PaulSp SSCrazy Points: 2499 More actions · Answer 2

Hi Vic

Thanks for answering. I seem to be having a few problems with this subform. I imported it originally from a legacy adp. When I change the vba, I sometimes click save only to find it has reverted to the original code. It is as if there are two sets of code belonging to the form (pretty wierd). There is a lot of stuff dependent upon it and I'm reluctant to start to reconstruct it. The subform did, However, work just fine in the old application. It compiles fine in this one...

I have tried the following code (which runs from the print button within the subform) as a type of bookmark, I'm not sure I've got this quite right as it comes back 'me.recordset refers to an object which is closed???

Dim rs As ADODB.Recordset

Set rs = Me.Recordset

Dim intID As Variant

intID = Me!Person_ID

Me.Recordset.FindFirst "Person_ID=" & intID

rs.Close

Many thanks for your help

Paul

R. Brush SSCommitted Points: 1669 More actions · Answer 3

Paul,

Access .mdb's and .adp's can get bloated over time when objects are being modified. One thing worth doing from time to time is "Decompile" the database file. Start MS Access with the /Decompile switch. Open your database. Open a code module. Save and Exit the database.

Another thing to try is to save the questionable object as text, delete it, and then reload from text. The SaveAsText method will create a text file of the object as follows:

Application.SaveAsText (ObjectType as acObjectType, ObjectName as String, FileName as String)

Example:

Application.SaveAsText acForm, "frmNamer", "C:\frmName.txt"

Likewise the LoadFromText method will create the Access object from the textfile specification:

Application.LoadFromText (ObjectType as acObjectType, ObjectName as String, FileName as String)

Example:

Application.LoadFromText acForm, "frmName", "C:\frmName.txt"

Vic Rauch-303403 SSCommitted Points: 1576 More actions · Answer 4

Paul,

I don't believe you can work directly with the form's recordset as you have tried. I believe you can only work with a RecordSetClone. Then find the record you want (as you did in your code), and save the Bookmark from the clone recordset, then you can apply that Bookmark to your form's recordset. For a better explanation, lookup RecordSetClone in Access help.

[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com

Perry Harts Ten Centuries Points: 1271 More actions · Answer 5

Paul,

I have the same problem when I use a SQL Stored procedure as the object of the form or subform. I have switched to a SQL Server view and the problem went away.

PaulSp SSCrazy Points: 2499 More actions · Answer 6

Thanks everybody for taking the time to help. I apologise for being so tardy in replying - I've been off for the Chrismas holidays.

Well I tried pretty well everything, to no avail. On further investigation, I found that the subform responded fine from within, but could not be easily referenced by code from without - sometimes it worked, sometimes it came back with invalid reference errors. I did get some joy with:

DoCmd.FindRecord VARIABLE, , True, , True

However, I wasn't happy with the workaround, so I went way back in my saved front ends until I found one which worked - so I'm now reworking everything. The problem turned out to be with the main form - not the subform - and then in no way which could be ascertained. I think an internal bug is the only explanation I can draw from it.

Many thanks again for some excellent suggestions.

Paul