Subform current view won't print in Report

[crvan29]

Hi everyone,

I am new here and am having trouble getting the current subform view to print. To start let me explain my database and things I've tried. I have a linked table called 'Clients' and another table called 'Medical Tracking' to track every medical appointment a client has had. The relationship I created is one-many (so 1 client to many appointments) by 'Client ID.' 'Client ID' is neither of the tables Primary Key if that matters (the PK for the Client table is 'ID' and for the Med Tracking table is 'Appointment ID').

I created a form using Form Wizard which has my Main form (Clients) and my Subform (Med Tracking) on it. I also created a Report with this information. I am able to print all the med appointments for an individual client right now using a Command Button on my form with the Actions GoToRecord Previous, GoToRecord Next, and OpenReport Normal in Print Preview. I also used Query Builder in my report itself to define the criteria in the Client ID field to state [Forms]![Form - Medical Tracking]![Client ID]. Since the Appointment ID is unique to my Subform and that's the record I want to print, I also tried putting in the Appointment ID criteria field [Forms]![Subform – medical tracking]![Appointment ID]. This kind of does what I want except it pulls up a 'Enter Parameter Value' box with that expression so I enter the appointment ID I want to print and it successfully shows that one record. I also tried changing the syntax to:[Forms]!Form - Medical Tracking.[Subform
– medical tracking].Form![Appointment ID], but this syntax isn't valid it says. What I need is for the Command Print Button to automatically bring up the print preview to make it easier for my users without having to use the Parameter Value box. Is there a simple way to change my criteria (or add to it) to make this happen?

I have also copied several different codes from various forums to put behind a button on the form that would allow the record that is currently being viewed to print, to no avail. There is usually a problem with my syntax and I don't understand what's wrong. I hope all of this makes sense and any advice or help is appreciated.

Thank you!

[Micron]

If it's not too late, I would read up on how to name (or not) your objects, save this db as a backup and start over. You should NOT have spaces or special characters in your object names and never start them with numbers or you will perpetually have problems with missing square brackets and/or quotation marks. Access looks at your form name and basically says, "I wonder what Form minus Medical Tracking is??" You must also refer to a subform using the hierarchy that it is placed in the order of things that are nested, like subforms and subreports. For example, to reference a subform property (e.g. recordset property such as Recordcount):
Forms("MainFormName").Controls("subformControlName ").Form.Recordset.Recordcount
NOTE: subformControlName IS NOT THE SUBFORM NAME, it is the name of the control that contains the subform

Here's a reading list that you should go over (and do not use lookup fields or multi value fields in your tables)
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/index.php/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

EDIT: Sorry, forgot to welcome you to the forum!

[Micron]

CROSS POSTED HERE http://www.utteraccess.com/forum/lof.../t2041781.html
crvan, please review this page
http://www.excelguru.ca/content.php?184

[crvan29]

Hi Micron and thank you for the quick reply. Sorry for the cross post, I was trying to get multiple ideas from users. I will include my first post if I do that again.

As for your reply, thank you for all the valuable articles. I have a question - when creating a relationship, do I need to have the main table (in my case Clients) linked to my Medical Tracking table by the Primary Key? Right now my tables are linked by the Client ID which is a number data type in both tables. I'm just curious if all linked tables need to come from a tables Primary Key.

I'm bummed that I mis-named my forms...there's no way to fix this by re-naming them correct? Is it better to start over from scratch? I also have 2 lookup fields in my Medical Tracking table so users have a combo box of the type of appointment and the name of the provider. This is something I need for my form, so where should I place these instead?

Thanks again for the help!

[Micron]

The last link should answer your pk question, which is not clear to me since you're not saying which (or both tables) for the pk. In my own practice, I don't always use autonumber pk's since in some cases I don't see the difference between Dept as a pk and having a unique index on that field as well as an autonumber pk. The autonumber basically becomes useless since instead of repeating the autonumber as a foreign key in some other table, the dept name gets repeated. There is not enough difference in the disk space required for one versus the other that means anything anymore. When a huge drive was 40 MB, then ok, it made sense. It also makes it easier to follow the bread crumbs when I see MACH for a fk value in the related table instead of 33 (the autonumber). I'm more convinced of my personal preference ever since I learned that due to corruption/program bugs, autonumbers can not only get duplicated, but can become negative. Do read up on the link for them.

Yes you can rename everything, which is what I meant and should have said, but you want to have a backup in case you need to go back to review what worked if you break something. And you shouldn't be adding/editing records in tables, so a lookup field is not required if you follow that maxim (and is generally considered to be poor practice). If you need a list of values as options for a field, a form combo box is the way to go. If there's only a few, you might use a value list in the control, but a table of lookup values provides that without ever having to open the form to redesign to add more options. Lookup tables are seldom part of table relationships.

Normally, I don't follow up on threads once I know they've been cross posted since I'm too lazy to go back and forth between forums to see if it's been solved or if my idea has already been suggested elsewhere. Forums don't pay enough for that.

P.S. when cross posting, declare that in all forums, not just in the first, second or whatever.

[crvan29]

Thanks Micron. I will try replacing my current table relationship with a Primary Key on one end. I will also try re-naming my tables and see what happens. I'll get back to you when I complete this! Thanks for the help again.

[crvan29]

Hi again Micron,

So, I changed my relationships to include a PK on one end. I also re-named my form "frmmedicaltracking" and my subform "fsubmedicaltracking." Unfortunately I still get the 'Enter Parameter Value' prompt when I click on my Print command button on my form. The syntax underneath this prompt is what I have entered in Query Builder in my report for the field 'Appointment ID' which reads: Forms!fsubmedicaltracking!appointment ID. I know you mentioned up above that I need to reference my subform using the hierarchy in which it's nested....so should I change my syntax to something else? Or should I remove this criteria and just use the 'Where condition' in my Print command button? I know there's something I need to change in my syntax...but I'm out of ideas. Any help is appreciated. Thanks

[Micron]

Forms!fsubmedicaltracking!appointment ID doesn't look right. Lines 5 and 6 of my first post show how to reference a subform. You would replace Recordset.Recordcount (a property of the subform) with the name of the subform control that contains the parameter value.

[crvan29]

I tried using the syntax from your first post so it looks like this: Forms("frmMedicalTracking").Controls("fsubMedicalT racking").Form.Appointment ID. I get a message saying the expression I entered has an invalid .(dot) or ! operator or invalid parentheses.

I replaced Recordset.Recordcount with the name of the field on my subform that asks for that parameter value which is Appointment ID. Is there another expression I'm supposed to have there since you have 2?

Also the name of my subform and the name of the control that contains the subform is the same name: fsubMedicalTracking.

[Micron]

I have 2 links I usually recommend for a naming convention. I'm surprised to see that the one I posted says nothing about spaces in names, which looks to be the issue. Either that, or it may be the lack of quotes around your control name. Maybe swap or add this link about names because it is specifically mentioned:
https://access-programmers.co.uk/forums/showthread.php?t=225837

Regardless, if you adding the missing quotes I cannot say if the space in Appointment ID will still pose a problem (square brackets are sometimes required to compensate for spaces) because I never use them, so one tends to forget all the idiosyncracies by simply avoiding the issues they present. Also, this site automatically inserts a space after 50 contiguous characters if you don't use code tags, so that is likely the reason for "fsubMedicalT racking"

It might have worked had you been able to write

Forms("frmMedicalTracking").Controls("fsubMedicalTracking").Form.AppointmentID

but then again, I have found that the syntax I posted for a subform property has given me issues in the past when attempting to use it against subform objects.
For that, I have had more luck with

 [Forms]![Main form name]![subform control name].[Form]![control name on subform]

The square brackets would likely eliminate the issue with spaces in your control names. Sorry if that goofed you up

BTW, I normally don't keep up with cross posted threads since I don't wish to keep going back to the other thread to see what's been suggested there. Can you imagine writing all this only to discover someone suggested this already somewhere else? If you're going to concentrate on this forum for this issue, no problem. If not, I'd like to know.
Thanks.

[crvan29]

Success! The code: [Forms]![Main form name]![subform control name].[Form]![control name on subform] worked! I tried this same code with my old form names (i.e. Form - Medical Tracking) and the parameter prompt appeared again. It seems indeed that it was that '-' messing things up. I tried this code also with another old form name I had and it worked as well (even with spaces in the form names). So, thank you for the help!!!!

And, yes, I was only going to focus on this forum. If something had been suggested on the other forum, I would have mentioned it here as well.

THANKS AGAIN!!!

[Micron]

You're welcome.

It seems indeed that it was that '-' messing things up. I tried this code also with another old form name I had and it worked as well (even with spaces in the form names).

Likely not the dash character alone. Spaces would present the same problem, which requires [square brackets] when an object name contains special characters or spaces.
This [Form - Medical Tracking] would probably be OK.
This Form - Medical Tracking is not. As a favor to those following your other post, you should post a link to this thread because it contains your solution, and mark this one as solved. Can't say if the other forum has a 'solved' tag there or not.