Update opened form

[Gismo]

Hi all,

This must sound like a silly question but I have a form open
When the record I need is not available, I click on a button to open the relevant form to add the record
When I close the second form, I need to select the record just added but I need to refresh the form first to be able to select the record added

I am not sure how to accomplish this
I dont want to close the form and open again
I tried form event "On Got Focus" Refresh
I tried double click event on the control refresh

Nothing seems to work correctly
Please could you advise the correct way of doing this

 

[bastanu]

You can try to add code to the first form's Activate event to do that, but I would suggest actually doing it from the second form as it closes; use its Unload event to check if the first form is loaded and if it is you need to Requery it, Refresh will not bring in the newly added record. The problem with Requery is that you will end up at the first record so you need to use the form's recordset(clone) to find the record just added and set the form's bookmark equal to that of the recordset.

Post back with the code you come up with if you get stuck.

Cheers,

 

[Gismo]

The second form is a stand alone form and can be opened from the menu and also from other forms so it does not only open from the first form
I will not be able to code it to refresh the fist form

Sorry I meant to refer to requery in my first post, not refresh

I have a few forms where i can open another form to add a record and to return to the first to select newly added record

 

[arnelgp]

do you have Autonumber field in your "add" form?

 

[Gismo]

do you have Autonumber field in your "add" form?

No I dont

 

[arnelgp]

No I dont

then What do you have (fieldname, type) to Uniquely identify your record on the "new record" form (which is also on the first form)?

 

[Gismo]

then What do you have (fieldname, type) to Uniquely identify your record on the "new record" form (which is also on the first form)?

I have a Part number or a supplied code or a customer number on the first form
All the other forms relevant to this will have the same part number or code which I create to select in the first form

 

[oleronesoftwares]

When I close the second form, I need to select the record just added but I need to refresh the form first to be able to select the record added

Private Sub Form_GotFocus()
            Me.bookname.Requery
End Sub

On the Got focus of the form write the code above , assuming bookname is name of combo box that is the look up field, but in the select statement retrieving the records, you will need to sort descending by a primary key, this will ensure that the last record added in the other form, will be the first value that is shown by default in the combo box.

 

[Minty]

@oleronesoftwares Have you tested that? Generally speaking an actual form itself can't receive focus unless it has no controls on it. Only a control on the form will get the focus.
You should use the form activate property, but that won't solve the problem of the requery forcing the records back to the first record.

 

[oleronesoftwares]

Have you tested that? Generally speaking an actual form itself can't receive focus unless it has no controls on it. Only a control on the form will get the focus.

Yes i have tested it, if am allowed to post video into this thread, i will demonstrate it via video and upload.

 

[oleronesoftwares]

but that won't solve the problem of the requery forcing the records back to the first record

The order by desc will handle this

 

[CJ_London]

when you open the form, pass the name of the controlling form and the name of the combo separated with a comma as the openarg

me.name & "," & me.comboname

in the newly opened form close event put

with forms(split(openargs,",")(0))!split(openargs,",")(1))
     .requery
     .value=me.partno
end with

 

[Gasman]

You can try to add code to the first form's Activate event to do that, but I would suggest actually doing it from the second form as it closes; use its Unload event to check if the first form is loaded and if it is you need to Requery it, Refresh will not bring in the newly added record. The problem with Requery is that you will end up at the first record so you need to use the form's recordset(clone) to find the record just added and set the form's bookmark equal to that of the recordset.

Post back with the code you come up with if you get stuck.

Cheers,

Isn't there a trick where you requery the form's recordset and the record pointer is left where it was?

 

[bastanu]

It is, but he doesn't want it where it was, he wants it to load the new record (that's why Arnel asked about how one would identify the newly created record).

I still think the unload event of the form used to create the new record is the best place for the code as it has the right elements: check if the first form is loaded (https://docs.microsoft.com/en-us/office/vba/api/access.accessobject.isloaded), requery it then use the Recordset.FindFirst to look for the part number just added and finally make the form bookmark=recordsetclone.bookmark to load that record.

I found that the Activate event is somehow unreliable and to use that you would need to save the new part number in some sort of temptvar or global variable.

Cheeers,

 

[bastanu]

@Pat - the second form is not set as modal and according to the OP it can be opened from multiple forms; it can be opened using the acDialog (=3) of the WindowMode argument of the OpenForm to act as a modal form, but you would still have to save the unique identifier of the newly entered record in a tempvar or global variable in order to find it and load it in the calling form. Using the Unload seems easier as you already have that value right there...

 

[bastanu]

Yes, Pat, I agree that we would benefit from more info from the OP regarding his setup, but my main points were that to open a form as modal you have to set its modal property to true in design view and save it. If you use Docmd.OpenForm to open it (or the embedded macro version) you can temporarily open it using the acDialog value of the WindowMode argument and it is the equivalent of having PopUp and Modal set as True.

Form.Modal property (Access)

Access VBA reference

docs.microsoft.com

The main difference in our approach comes to getting to record after it was added in the second form. The OP indeed said that they don't use an autonumber but he also said that the user would know the unique part number or supplier code that would identity this new record, and new is the key here. You cannot continue code on the main form if you do not save the unique part number or supplier code of the new record in a tempvar or global variable that can be referenced when your code continues. You do not know what the user would enter in the new record when you open that form, but you know when you unload it. So why not just do it there?

Cheers,

 

[Gismo]

Hi all

Thank you, plenty of suggestions here, much appreciated

The form to add new records as mention can by accessed from various sections in the DB
I did not want to created additional add only forms as this would be the case for customers, suppliers and products
Unique identifiers would be the customer number, suppliers number and products has a auto ID
I now added an auto number to all the tables

After Adding the new record, below seems to have worked from a 3rd form that look's at any new records created then requeries the first form
Forms!Batch!Recipe.Requery

I have reworked a few forms so I will take it from there, seems to be ok, will be doing a few more test

Thank you for all the help and guidance

 

[Gismo]

I'm sorry that you are using separate "add" forms. That makes extra work and requires duplicating the validation code assuming you bother with any.

You can always reference one specific open form from another but the form running the reference code needs to first determine if the form is open. My suggestion always works.

Hi,

I dont have seperate forms, I was refering to the one signel form for customers, suppliers, which are accesseable from a few menues
only when i raise a purchase order and i need to add a supplier, i open the same suppliers form and when it closes, it needs to auto update the dropdown in the purchases form
When I open the suppliers form anywhere else in the menus, it should not look at the purchase form suppliers drop down

I managed that with the below code

Dim ctlCombo As Control

If CurrentProject.AllForms("Purchase").IsLoaded = True Then
DoCmd.RunCommand acCmdSaveRecord
Set ctlCombo = Forms!Purchase!SupplierID
ctlCombo.Requery
DoCmd.Close , ""
Else
DoCmd.Close , ""

End If