Refresh / Requery another form

[NickBridgens]

Ok, here's the problem.

I have a customer form A that for various reasons I have not allowed the user to add records on, they click a button, open a new customer form B, create a new record and then click the save / close button. Only if I click the button on form A to requery, can I now see my new record, but how can I get the code that saves the record and closes form B to also requery form A so the user doesn't have to do anything.

On the same lines, the subform of form A is a brief view of the subtable, so to create a new subrecord, the user clicks a button, launches the newvehicle form, creates a new vehicle and then clicks save / close. Only by moving from record A to record B and back again do I see the new contents of the subform.

I tried
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Forms!Customers!CustomerVehicles.Refresh
DoCmd.Close

as the code for saving a new vehicle record from the vehiclenew form (customers is the main form, customervehicles the subform) but it just objected.

Any suggestions welcomed...

Nick Bridgens

 

[edtab]

I think the problem here is that you are "refreshing" the wrong form.

Try "requery" (instead of refresh) and on form A not on form B.

In the On Close property of form B place an event code to requery your subform contained in form A.

Be interested to find out if this works.

edtab

 

[NickBridgens]

No joy.

I know I want to refresh or requery form A (and it's sub form), but the question is how can I do that in code from the closure of form B?

Putting in

Forms!Customers!CustomerVehicles.Requery

in the onclose event of the CustomerNew or VehicleNew forms just says it cannot find the field. Change the CustomerVehicles to CustomerID (a field) stops the error but achieves nothing.

 

[Rich]

Try
Forms!Customers!CustomerVehicles.SetFocus
Forms!Customers!CustomerVehicles.Requery

 

[NickBridgens]

Thanks

One down one to go!

Still got the same "can't find the field" message so changed code to

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Forms!Customers!CustomerID.SetFocus
Forms!Customers!CustomerID.Requery
DoCmd.Close

And it works for creating a new vehicle, but still can't solve the new customer one.

 

[Rich]

Try
Me.Recalc
Forms!Customers.SetFocus
Forms!Customers.Requery
DoCmd.Close

 

[NickBridgens]

Thanks Rich, but wierd!

Your code saved the record, closed the customernew form and then reopened customernew form showing the new record. Couldn't tell if the code was working 'cos opening customers form showed record as there (of course).

Changed the order so it closed the form and then did the requery and it worked.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Recalc
DoCmd.Close
Forms!Customers.SetFocus
Forms!Customers.Requery

Being pedantic however, how can I get the display the show the new record when the focus goes back to customers form?

 

[Oldsoftboss]

Try adding
DoCmd.GoToRecord , , acLast
to the next line of your code
HTH
Dave

 

[NickBridgens]

No joy - the customer records are displayed in alphabetical order, so it just goes to the last one, not the one just created.