Report Within a Form

[HLTAYLOR]

On my form, I want to update a field, and have a report within the form update. Specifically, I want to enter a Ticket# and when I tab, I want the report to pull in Ticket# info from a table that is not the source table for the form. I've joined the two tables, I've written a query, I've created a subreport and placed it in the form, but it just doesn't work. I MUCH prefer to use macros when possible. Any help out there?

[June7]

That's because reports are not intended to be interactive. Once open, little can be done to modify report, especially in PrintPreview (ReportView does allow some filtering and command buttons are active). The report must be rerun so its RecordSource will requery and the various sections can be re-rendered.

[HLTAYLOR]

Is a subFORM what I need? Thanks!

[June7]

A form would be easy to refresh. It might not be able to organize the data same as report.

[HLTAYLOR]

Could I use a subreport, and program an entry into a field to trigger a parameter query for THAT field value?

[Cheryl R]

In my humble opinion, what you are trying to accomplish will work better as a form with subform, not form with report. In this way, each time you change the field on your form, the subform will automatically change as long as you link the parent/child fields correcty. You can make the subform un-editable if you are worried about people changing data.

And don't be afraid of vba- to me, it's actually easier than macros because you can do so much more with it.

[HLTAYLOR]

I tried it with a subform, but it did not update. I think I have the parent/child form linked correctly, but maybe I didn't get the event set up right. Can you tell me how to set it up so that when the parent field is filled out, the subform will import what I need? THANKS!

[Cheryl R]

You need it to requery the form after you update this field. This will refresh the subform data.
This goes into the After Update field on the properties sheet.

In case you haven't worked with vba before, the steps are:
1. Click on the field you will update on the main form
2. Open the properties sheet
3. On the Event tab, click into After Update
3. Click on the three little buttons to the right- this should take you to the vba screen where you will see "Private Sub YourFieldName_AfterUpdate()and Exit Sub
4. Between those, type in Me.Form.Requery
5. Save and exit vba

Hope this helps!

[HLTAYLOR]

I'm still doing something wrong! I put in the vba above. When I update the parent field, it goes back to record #1 (which DOES have the info imported). I want the subform to update while I am filling out the form for a new record. URG!!

[Cheryl R]

What do you mean "it goes back to record #1" That sounds like your form and subform are not linked correctly.
Something to remember- the subform (child) needs to have the field that matches the form (parent) even if you make it not visible. (I have read this is not necessary but others insist that it is.) In my db- I have an order form with the order details in a subform. Even though I don't want to see the Order # in the subform, it needs to be on the subform. When I enter an order #, the subform shows me the items on that order because the OrderID on the parent and child are the same.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post. Be sure to identify the objects involved in issue.

The foreign key should not have to be bound to textbox for the subform to synchronize with main form. It does need to be in the form RecordSource query.

Report/subreport act a little different, reports can require field to be bound to textbox for synchronization and for code to find any referenced field.