refresh a subform while not losing focus in another subform

[gbullo]

Access 2003

I have an input form that is based on a single table with combo boxes and the selection of those boxes creates a requisition with a auto number ID. Then I click the "add line" button to open (simply make visible) a sub form to allow users to add individual line items which all ties to the requisition header tied to the requsiition ID # (rID).

When I click this add line button, it makes my line item input form appear (visible = yes)
I add a line item then click a button called "Next" or "Submit"

When the Next button is clicked, it opens (makes visible a second subform) another subform in datasheet vew that shows a summary of all line that have been added in a non updating format. All these items are a result of a query based on the rID.

Everything works find except in order to see the summarized details in the datasheet veiw subfirm that were just added, I have to manually press F9 to refresh.

How can I get the datasheet subform to refresh/re-query while still maintaining focus in the add line item subform? Everything works except the real time refresh. I have for force it with manual F9. I've tried calling it out in a macro but being a subform and not in focus, it thinks the form isn't open.

Any suggestions?

[pbaldy]

Try this at the appropriate point in code:

Forms!MainFormName.SubformControlName.Requery

[gbullo]

Thanks for information but where would I place this code? The main form, add line subform or the summary of all lines subform

George

[pbaldy]

Sounds like the "Next" button, but my actual answer is "at the appropriate point(s) in time where you want the subform requeried". It should work from anywhere, as long as that form/subform is open.

[gbullo]

Didn't seem to work.
I tried using "runCode" in a macro, placing an expression in the function name property then placed that macro on the "onclick" properties of a button that is clicked each time to add the next line item.

I received the following error
"The object you referenced in the Visual Basic procedure as an OLE object isn't an OLE object"

Here are my form names
Main Form name = Frm New Req
SubForm name = Requisition_line_item subform

This is what the expression looks like that I put in the RunCode property (function name) of the macro
Forms!Frm_New_Req.Requisition_line_item_subform.Re query

[pbaldy]

That would not work. The RunCode macro action requires the name of a function, not actual code. I would use code, but if you want to use a macro, use the Requery action with the same form reference. Using the Build feature will help ensure the correct syntax.

[gbullo]

It seems to be a loosing battle. I tried using code in the after update event in the subform and I get an error that the form I am calling out can't be found. I think it's a focus issue but I am not sure.

Thanks for all your help. I really appreciate it but this seems to be too far over my head. Taking instructions and examples are good but when they don't work, I don't understand the logic behind them and I can't even troubleshoot.

Thanks again for all your time.

Regards,

George

[pbaldy]

No problem. If you want to post the db, or a sample of it, we can probably figure the error out.

[gbullo]

Pbaldy,

Where would I place this code? On the properties of the "main form", the "add line subform" or the "results subform"? The results subform is what I want refreshed everytime I add another line via the "add line subform".
I've tried placing that code in the "results subform" after update properties and I get an error saying the mainform can not be found. I beleive it's because the focus never leaves the "add line subform". If I press F9 or go to the menu and refresh it works fine but I beleive that action changes focus and updates the main form and and the "results subform" updates as a result.

My initial action is creating a requisition header on the main form using combo boxes to populate the fields required. Then I click a button called "add line items" and the subform becomes visible ready for data entry associated with the requisition header just created. When I begin entering line items directly into the "add line subform" my focus is now on the "add line subform".

After I create the first line item, I click a button called "add next line" (this button is on the "add line subform"). When the button is clicked, the "add line subform" is refreshed and ready for the next line item entry. Also, when the button is clicked, the "results subform" becomes visible and I want that subform to show the line item just entered. I want this action repeated each time thereafter until I am done adding line items. Then I will hit a Submit button (this button is on the "add line subform") and it will print my requisition and close the form returning me to a control panel.

Since I have focus in the "add line subform" and wanting to get the "results subform" to refresh automaticly, it seems as if there is an issue of the command not operating properly. That is my guess at the problem and I can't seem to over come it with simple code.

George

[pbaldy]

It sounds like the after update event of the "add line subform" is where you'd want it. Did you change all the names to yours? Here's a handy reference on syntax:

Forms Refer to Form and Subform properties and controls

Again, if you can post the db it would help.

[gbullo]

Pbaldy,

Attached is a slimmed down copy of the database.

It should start up with a control panel.
1.) Click "Create New Requisition" (will open main form ready to create requisition header)
2.) Add an item to each drop down box
3.) Click "Add Line Item Details" (will make visible the add subform)
4.) Type in a description, Qty, price, UM
5.) Click "Next Line" (will refresh Add Line Subform and make visible "results subform")
This is where it's not working.
6.) Hit F9 to refresh and you will see the line you entered. Go ahead and add another new line and hit F9 again and you will now see two lines, and so on.....

I want it to automaticly refresh everytime I click "Next Line" (or "Update Requisition" I haven't made this button active yet. Much more work to do)

George

[pbaldy]

I'm getting an error in your macro, but this line in code works:

Forms![frm new req].[Requisition_line_item subform].Requery

But at this point the after update code isn't actually associated with the event, and you have an incorrect reference to the subform. Go into the form's properties and click on the ellipsis on the After Update line to properly associate them.

[gbullo]

I think you've lost me entirely.

Just to make sure I understand correclty,
You are asking that I go into the main form (frm new req) properties, click on the ellpsis on the After Update line to properly associate them?

When I click on the ellipsis, I have to choose expression, macro or code builder so I beleive I need to chose code builder.

Do I simply place that code that you said works (Forms![frm new req].[Requisition_line_item subform].Requery) in between Private Sub and End Sub to get this association?

George

[pbaldy]

Not the main form, the form for adding records. Code builder is correct, and that should take you to the code that's already there, which you'll need to modify as above.

[gbullo]

Pbaldy,

It works!!!!!! And it was so simple once it was placed in the correct area.

Thank you very much. Now on to the design of the rest of it and I may be sending another post or two during the design of this thing.

once again, thank you very much.

George