Open current record in report (with subreport)

[Dranoweb]

Hi,

I'm currently designing a database.

I have a main table and a one - to many related table.

The main table (MASTERTABLE) stores details of a group.
The related table (Presenter) stores meeting records.

I'm writing this database in access 2007, but in 2003 format as the end result will be used in 2003 (irritating corporate policies!!).

I have a button to open the current record in a report. Said report (based on "MASTERTABLE" has a sub report to show the "one to many" related record for meeting dates (Presenter).

Now my current code to do this is as follows:

RunCommand acCmdSaveRecord
DoCmd.OpenReport "CFG Extract", acViewPreview, , "id=" & Me![ID]

This works fine in 2007, and is ok in 2003, except for one scenario:

I can open the current record providing there are no record related to it in the subreport.

when there are, access 2003 generates an error about the reference to "ID" and refuses to open the report. 2007 however happily does what it is told.

I had the feeling that the field on the subreport called "ID" (being the key field) may have messed with this, and i renamed it to ID2, only to find earlier today that this did not resolve the issue.

I have tired several "older" ways of calling the current record, all of which work in 2007. Some taken from 2003 tutorials.

any ideas on this conundrum?

I can supply more code or samples from the database if required.

 

[vbaInet]

I think if you're building for 2003 then you should be using Access 2003 instead. There are lots of new code in 2007 that is obviously not available in 2003 so unless you're aware of the common ones, you're better off developing in 2003.

I believe acViewPreview in 2003 is acPreview.

 

[Dranoweb]

That's an obvious statement, however given my current situation, this is not possible.

I do however test at each stage of the project, to ensure these features work. 99% do, except for the above mentioned one, which has been uncovered by extensive pre-testing.

 

[vbaInet]

What is the exact message? Have you tried Me.ID?

 

[Dranoweb]

I believe acViewPreview in 2003 is acPreview.

Acviewpreview appears to work fine in 2003, this is not the issue, however I have seen more crazy things cause errors in access. Will try that.

 

[Dranoweb]

What is the exact message? Have you tried Me.ID?

Unsure of the exact message, and yes I realise this is an issue.

Finding out means a 20 minute trip and an appointment to see my client.

Have not tried me.ID, will add it to the list of things to try.

I'll compile a few test code snippets and comment them out for testing when I am on-site again.

 

[vbaInet]

Ok, what I'm most sure of is if you make sure the name of the control which is bound to the ID field is not called ID. Call it something like txtID. Then you can either use Me![ID] or Me.txtID

 

[Dranoweb]

Ok, what I'm most sure of is if you make sure the name of the control which is bound to the ID field is not called ID. Call it something like txtID. Then you can either use Me![ID] or Me.txtID

Facepalm time...

I have seen this many times before (using wizard generated forms), and had forgotten as 2007 does not seem to suffer from this illness.

Thankyou for the reminder - it appears that the control was indeed named ID.

I can't check it right now, but I'll make sure I drop a line back here and let you know how it goes.

 

[vbaInet]

Yeah, I make it a habit to rename my controls as soon as the form is created.

I hope it works!

 

[Dranoweb]

Yep - works fine now. It's a pain to have to go back to the old ways of doing stuff.

Thanks heaps. I'll have to post a few bits of tricky code I have put together sometime and contribute to the forums myself.

 

[vbaInet]

You're welcome.

Please do. I'm sure some forum users will find them useful.