Update opened form (1 Viewer)

Gismo

Registered User.
Local time
Today, 10:23
Joined
Jun 12, 2017
Messages
1,298
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

AWF VIP
Local time
Today, 01:23
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Today, 10:23
Joined
Jun 12, 2017
Messages
1,298
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
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:23
Joined
May 7, 2009
Messages
19,094
do you have Autonumber field in your "add" form?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:23
Joined
May 7, 2009
Messages
19,094
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

Registered User.
Local time
Today, 10:23
Joined
Jun 12, 2017
Messages
1,298
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

Passionate Learner
Local time
Today, 01:23
Joined
Sep 22, 2014
Messages
1,159
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
Code:
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

AWF VIP
Local time
Today, 08:23
Joined
Jul 26, 2013
Messages
10,346
@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

Passionate Learner
Local time
Today, 01:23
Joined
Sep 22, 2014
Messages
1,159
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:23
Joined
Feb 19, 2013
Messages
16,521
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

Code:
me.name & "," & me.comboname

in the newly opened form close event put

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

Gasman

Enthusiastic Amateur
Local time
Today, 08:23
Joined
Sep 21, 2011
Messages
13,964
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

AWF VIP
Local time
Today, 01:23
Joined
Apr 13, 2010
Messages
1,401
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,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:23
Joined
Feb 19, 2002
Messages
42,872
If you open the popup form as model, all code in the calling form stops when you open the popup. Therefore on the line AFTER the OpenForm, you can requery the current form.
 

bastanu

AWF VIP
Local time
Today, 01:23
Joined
Apr 13, 2010
Messages
1,401
@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...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:23
Joined
Feb 19, 2002
Messages
42,872
1. I understand that the form can be opened in multiple places. It doesn't matter.
2. I don't care if it is not now opened as model. Opening it as model will solve the problem because that stops the form's code from continuing as soon as the popup form is opened. And that gives you a place where you can do something when the popup is done because your code picks up where it left off.
3. The OP said knows what record he needs. That means he is not using an autonumber to identify it. This may or may not be true but it is what he said.
4. Requerying the form/combo, will include the newly added record in the base form's RecordSource. That will allow the user to "find" the record he is looking for.

We are all making assumptions because we don't have the database. My assumption is - it makes no sense to have multiple different forms to add/update/view the same data. Therefore, the problem is most likely regarding picking something from a combo and we're dealing with a misunderstanding regarding the use of the Not in List event.

If the OP has created a separate form for adding records and that is what is causing the problem, then THAT is the problem we need to address but I don't think anyone actually asked.

At some point @Gismo will come back and clarify for us.
 

bastanu

AWF VIP
Local time
Today, 01:23
Joined
Apr 13, 2010
Messages
1,401
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.

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,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:23
Joined
Feb 19, 2002
Messages
42,872
You can use the arguments in the OpenForm Method to open the form modal. It is the Windows Mode property = acDialog that does it.

Either the user will type the code in again (unlikely) or after requering the combo, he will be able to select it from a combo (more likely).

I am not in any way recommending this method since I firmly believe it is really poor practice to have different forms for add and view/update. I am also against allowing users to enter items in combos on the fly except in rare circumstances. I am just explaining how to simply do what the user requested. I told you what my assumption was - that it is some confusion with the Not in list event processing rather than the user choosing a poor design with separate "add" forms. I'm just listening to what he said he wanted to do and telling him how to accomplish just that. My opinion on how the user chose to design the app is irrelevant. We do not know with certainty whether in fact the OP has actually created a separate for that does "add" only.

Maybe someday @Gismo will come back and clarify.
 

Gismo

Registered User.
Local time
Today, 10:23
Joined
Jun 12, 2017
Messages
1,298
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
 

Users who are viewing this thread

Top Bottom