Refresh - hopefully a simple question!

[branston]

Hi,

I have a form which, when you click on a certain button, opens another small form (with the original form still visible in the background). This small from allows the user to add and change information about the record they selected.
What I want to happen is for when I close the small form, the main form to be updated. At the moment some of the data is populated (the number I actually put in) but I have some dlookups which don't populate until I go out and come back in to the main form.
I thought it would be a simple docmd.requery but I'm not sure where I should put it?!

Any help would be greatly appreciated.

Thank you

 

[vbaInet]

On the UNLOAD event of the pop-up form, set the value of the textbox's on the pop-up form to the respective fields on the main form. Make your pop-up form unbound to avoid write conflicts. So on unload of pop-up form:

Forms!NameOfForm!NameOfField = Me.NameOfControl

 

[branston]

Thanks for the reply... but that doesn't work as the main form is non-updatable. I'll explain a bit more and you might come up with a better way of doing things!

Basically, I have a form which shows every record with some information. The information comes from 2 tables, which are linked in a query - forming the record source of the main form. However, due to the links the query is not updatable. I can change them to dlookups but then the form takes forever to load.
So I found a way around this, by using the non updatable recordsource for the main form, where all the records are shown, and then when you click on a specific record, the small form appears (with the recordsource using dlookups) for that 1 record. With only 1 record it doesn't take too long to load and is updatable.

Does that make sense?

So when I update the updatable table, I need the information to refresh in the non-updatable recordsouce...

 

[vbaInet]

I think you should be looking for a way to make your form's record source updatable. Use subforms to pull the relevant records where necessary so as not to have complex joins which is one of the causes of a read-only recordset. Here's a comprehensive list:

http://allenbrowne.com/ser-61.html

If you dont' want to go down that route then it's either you create an UPDATE query and set the Update To row for each field to the textbox like this:

[Forms]![NameOfForm]![NameOfTextbox]

Substituting the highlighted bits with the relevant names.

Or you run an update in code.

 

[branston]

I don't think I will be able to get the query updatable as it is not a 1-1 link in the query.
Also, I can't use a subform as the form is continuous.

If I went for the last option (update in code) would I put that in the unload of the updatable form?

 

[vbaInet]

You can put a suborm anywhere, continuous or not. You can make it look like just a normal control when the form is viewed in continuous form mode.

You need as many queries as is required to get the tables updated separately. To execute a query you use this command:

DoCmd.SetWarnings False
Docmd.OpenQuery "NameOfQuery"
DoCmd.SetWarnings True

The SetWarnings line turns off the message you get when updating a record just before the update and turns it back on. It's important it's turned back on.

In code, it's a bit more complex because you have to write the SQL statement yourself. Something like:

DoCmd.SetWarnings False
Docmd.RunSQL "UPDATE TableName SET ..."
DoCmd.SetWarnings True

Go for the query option.

 

[branston]

Thank you for your help so far, however I am confused about what you say with subforms being possible in continuous forms. I have heard this a lot but whenever I try it comes up with the message:
"A form with a subform object can't have its DefaultView property set to Continuous Forms"
Am I doing something wrong?! I can think of many places this would be useful so would quite like to understand how to do it!!

 

[missinglinq]

You cannot place a Subform in the Detail Section of a Continuous form, but someone here, maybe Bob Larson, has suggested before that with a Continuous form you can, in fact, use a Subform, but you have to place it in the Header Section of the form.

As to requerying your original form, I've done this in a similar situation by using Me.Requery in the Form_Activate event of the original form.

Linq ;0)>

 

[vbaInet]

That's right Linq. You can use the subform for updating individual records if placed in the header section of the form. That way you don't need to worry about pop-up forms.

 

[branston]

oh, right, so you would have the subform in the header, linked to whichever record was selected... so it would work the same way as the pop up, without there having to be a pop up? I think i get it now.

And missinglinq - the OnActivate works well.

I think i will give the subform in the header a go, but if not I will revert to the OnActivate.

Thank you both for your help!