I think I need to requery, but not sure how or when or where

[Gina K]

I have a form that's rather crowded with controls. In order to save space on
my form and give the user plenty of space to type comments, I use the
following in the On Enter event of a control called "DescOfChange" to open a
pop-up form containing only 2 controls on it, "DescOfChange" and "ECRNumber":

DoCmd.OpenForm "frmDescOfChange", acNormal, , "ECRNumber =
Forms!frmECR!ECRNumber"

When I close the pop-up form and return to the main (crowded) form, I have
to exit the current record and return to it before the comments I entered in
my pop-up form appear on the main form. I think I need some sort of requery
or refresh, but I'm not sure how to go about it.

Any suggestions?
Thank you.

 

[Jeanette Cunningham]

Gina,
There is a way to do it without requery.
Instead of closing the popup form, it can be made invisible, and your main
form can read the data entered and pull it back to show it on the main form.
The code that opens your popup form waits while the user completes the entry
on the popup.

Do.Cmd OpenForm "YourPopup", , , , , acDialog
'after user hits the close button on popup, the popup is still open but
hidden (invisible) and the code back on the main form continues
With Forms!YourPopup
Me.txtChangeDesc = .DescOfChange
End With
DoCmd.Close acForm, "YourPopup"

Replace form, control and field names with your own names.
The above gives the idea. I don't know how ECRNumber fits into your setup,
you may have to include it in the code above or not, depending on your
setup.

Jeanette Cunningham

 

[John W. Vinson]

I have a form that's rather crowded with controls.

Could you consider using a Tab Control? This is a very handy way to manage
screen space; you can have what looks like a filing cabinet with tab folders,
with a subset of the controls on each tab page.

In order to save space on
my form and give the user plenty of space to type comments, I use the
following in the On Enter event of a control called "DescOfChange" to open a
pop-up form containing only 2 controls on it, "DescOfChange" and "ECRNumber":

DoCmd.OpenForm "frmDescOfChange", acNormal, , "ECRNumber =
Forms!frmECR!ECRNumber"

What are the Recordsources of the two forms? If they're the same table, you
may have a conflict since you'll have two different forms simultaneously
trying to edit the same record.

When I close the pop-up form and return to the main (crowded) form, I have
to exit the current record and return to it before the comments I entered in
my pop-up form appear on the main form. I think I need some sort of requery
or refresh, but I'm not sure how to go about it.

I'd requery the textbox displaying the comments in the popup form's Close
event:

Private Sub Form_Close(Cancel as Integer)
Forms!Mainformname!controlname.Requery
End Sub

John W. Vinson [MVP]

 

[Hunter57]

Hi Gina,

Private Sub Form_GotFocus()

Me.Refresh

End Sub

That should do it. When your form gets the focus again your form records
should be refreshed.