Requery Syntax Using a variable 1

[jmstarbuck]

Hi all,

This is a really basic question, but I have spent 2 hours on it so far and I'm losing it.

I have a control name stored in a string field: ControlName

For instance: Forms!Reports_Form.CmbSignature

I want to requery the field whose name is in the ControlName field.

I have tried every variation of

Docmd.Requery(ControlName)

that I can think of.

Can someone help me out one this?

 

[MajP]

From the helpfile

Use only the name of the control for the controlname argument, not the full syntax.

You can also use a variable declared as a Control data type for this argument:

Dim ctlLstBox As Control
Set ctlLstBox = Forms!Form1!Field3
DoCmd.Requery ctlLstBox.Name

The Requery method of the DoCmd object is different from the Requery method in Visual Basic. The Requery method of the DoCmd object was added to provide backwards compatibility for running the Requery action in Visual Basic code in Microsoft Access 95. If you want to requery a control that's not on the active object, you must use the Requery method in Visual Basic, not the Requery action or its corresponding Requery method of the DoCmd object. The Requery method of a form or control in Visual Basic is faster than the Requery action or the DoCmd.Requery method. In addition, when you use the Requery action or the DoCmd.Requery method, Microsoft Access closes the query and reloads it from the database, but when you use the Requery method, Microsoft Access reruns the query without closing and reloading it.

so if not the active object you would do it something like this.

Public Sub requeryCtl(ctl As Access.Control)
  ctl.Requery
End Sub

If you put the procedure in the forms module and you call it from that forms module then call it like

requeryCtl me.CtlName

if you put it in a standard module you can call it from anywhere like

requeryCtl forms("frmName").ctlName

 

[jmstarbuck]

Thanks for the clairification. Where did you get that help text? I looked for help on Docmd.Requery and did not find that.

I am trying to pass the fieldname of the field that needs to be requeried in the open args of another form. I am able to pass the string of the field name, but I don't think I can pass anything other than a string.

I want the form that is being opened to requery the calling field when it closes. How can I accomplish this?

J

 

[MajP]

I think I got that from the MSDN. The point is that the Docmd.requery has certain limitations.

Yes you can only pass a variable as an arg, not an object. So if you passed the name, and the control is on the form then you could do something like

me.Controls(me.OpenArgs).requery

That is how you could do it, but what are you trying to accomplish in the big picture? It seems strange that you would pass to a form a name of a control on that form to requery on close. What functionality are you looking for?

Also normally when you requery the form all controls requery as well. So there may be a better way.

 

[jmstarbuck]

I have two forms in this scenario.

Form A has the field on it that I want to eventually requery. The field on Form A (a combo box) has a row source that is managed on Form B.

Form B is opened (acdialog) on field on form A's double click event to allow the user to add new choices. When form B closes I want the field on form A to be updated with any changes made in Form B.

Here's what I have finally found to work. I got this idea from your last reply:
Forms(callingform).Controls(callingcontrol).Requery

I parse the openargs into the callingform and callingcontrol variables on the open of Form B and then use them in the close of Form B.

Thanks so much for your assistance!

J

 

[MajP]

Yeah, I thought so. You are making it too complicated. This is the standard way to do that. The trick is when form A opens form B in dialog, the code stops in form A from the point where it opened form B. It resumes from that spot once form B closes. So simply:

from form A:
docmd.openform "FormB",,,acdialog
'Code stops here until B closes
'It then returns here after B closes so simply
me.combo1.requery

No need to pass any string or variable.

if you do not open it dialog it will not work. Form B would open and then the requery would immediately occur because the code in A continues to run.

 

[jmstarbuck]

Believe it or not, that is where I started. Here's my code:

Private Sub CmbEstimator_DblClick(Cancel As Integer)
    DoCmd.OpenForm "People_Form", acFormDS, , , , acDialog, nz(Me.ActiveControl.Value, 0) & constseparator & "Reviewer" & constseparator & Me.Name & constseparator & Me.ActiveControl.Name
    Me.ActiveControl.Requery
End Sub

I see that we have a different number of commas between the form name and the windowmode. However, I just typed one in again with the suggestions telling me where to put what and it came out the same.

I tried it with a less commas and got type mismatch errors until I was back where I started.

When I execute the above code, it opens the new form and then immediately does the requery.

Do you see something in my code that could explain this?

J

 

[MajP]

Sorry about the commas, I was trying to do it by memory.

DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs

DoCmd.OpenForm "People_Form", acFormDS, , , , acDialog, nz(Me.ActiveControl.Value, 0) & constseparator & "Reviewer" & constseparator & Me.Name & constseparator & Me.ActiveControl.Name

Looks like your arguments line up. Correctly.

How do you know that it immediately requeries? You can test that by putting a msgbox after you open the form.

docmd.openform....
msgbox "Should not show Unitl after form B closes"

Verify that this message shows.

 

[jmstarbuck]

It shows up right away, while B is still open. I wish I could send you a screen shot. I am at a loss.

Is there some kind of setting at the database level that might impact the way modal forms are handled?

Best,
J

 

[MajP]

I tested it, and learned something. It is because you are opening the form as a datasheet. i did not know that made a difference. You could look at a tabular continous form, or put the datasheet into a mainform as a subform and take up a majority of the main form.

 

[jmstarbuck]

Thanks so much for your help.

J