not understanding something.... 1

[Lhuffst]

I have a navigation form that I want to requery the recordset of a subform when returning from a different form. I'm having a difficult time getting the syntax correctly and need something to help.

Mainform with navigation buttons: ShopOpNavigation
Errors button on Mainform: Opens frmUserErrors Form

FrmUserErrors Form has a button: Add New which opens another form frmAddErrors. All this works fine.

When I return from frmAddErrors to frmUserErrors, I need the data to be refreshed and in descending order.

frmUserErrors 

Recordset:  SELECT tblErrors.ErrorId, tblErrors.ErrorDate, tblErrors.ErrorById, tblErrors.ErrorBy, tblErrors.ErrorType, tblErrors.ErrorComments, tblErrors.ErrorPDFLink
                 FROM tblErrors  ORDER BY tblErrors.ErrorDate DESC;

On current and open events:

[Forms]![ShopOpNavigation]![NavigationSubform].[Form].ErrorBy.Requery

If I exit the subform and go back, then the new data that was added is there and in descending order. What am I doing wrong that doesn't requery (refresh) the data in descending order when I return from frmAddErrors

 

[MajP]

That code is pretty incomplete so difficult to see what you are doing. Not sure of the purpose of the navigationsubform.errorby.requery.

If frmUserErrors open formAddErrors, and it is not opened as acdialog then code execution continues.

So if you have
docmd.openform "frmAddErrors"
me.requery

The form frmAddErros opens and the frmUserErrors immediately requeries itself. If however you open it
docmd.openrform "frmAddErrors",,,acdialog (i am guessing which parameter it is so check)
The code stops there until you close the calling form. If the next line is
me.requery
Then it will requery once you close frmAddErrors.

 

[MajP]

Also avoide using bang notation
[Forms]![ShopOpNavigation]![NavigationSubform].[Form].ErrorBy.Requery
Use the following if calling the code from ShopOnNavigation
me.shopOpNavigation.Navigationsubform.form...
Or if calling from another form
Forms("ShopOnNavigation").NavigationSubForm.Form...

You defeat all the benefits of intellisense. You would chase this error [Forms]![ShopOpNavigation]![NavigationSubfrm] forever, and never be able to figure it out. Only time you want to use ! notation is in queries or calculated controls. Only time you have to use [] is in queries or in vba when the name has a space or is a reserved word. The other issue with ! notation is that you cannot use variables to represent the name.

 

[Lhuffst]

Hi Majp
Sorry it took so long to get back
I have been making the changes you suggested and it seems to be working in some places but not others.
I am in the process of moving old databases to newer versions and wanted to try the navigation options which started me down this path.

I am still running into issues and everytime I think I understand it; I get an error.

if I have the following:
Navigation Form: ShopOpNavigation
SubForm1: frmPermitAdd
Subform2: frmMeterAdd

with Subform1 using tblPermits and subform2 using tblMeters

Example: If I want to reference a field on subform1 I would use:
Forms("ShopOpNavigation").frmPermitAdd.form.permitNo.value or
Forms("ShopOpNavigation").NavigationSubForm.Form.PermitNo.value

Now Subform2 actually sits in subform one.
If I need to reference a field on subform 2,

If Forms!ShopOpNavigation!frmmeterAdd.Form!Size.Value <> 3 Then
gets runtime 2465 application defined or object-defined error

I've also tried these
If Forms!frmPermitAdd!frmMeterAdd.Form!frmMeterAdd.Form!Size.Value <> 3 Then
If Forms!ShopOpNavigation!navigationsubform.Form!frmmeterinfoadd.Size.Value <> "3" Then

I know you said don't use bangs but if I didn't I runtime error 2465 (same as above)every time.

My understanding for the path was: Forms.NavigationForm.subform2.Form.subform2control.value or requery etc.
What am I not grasping?
Thanks

 

[MajP]

You can never call a subform directly by name. You have to go through the subform control to reference it. From the main form you cannot call
Forms!ShopOpNavigation...
you have to
me.NameOfShopOpNavigationSubFormControl.Form...

The subform control is the container in which your subform resides. It is not form inside it. However often the container name is the same, but not always. You have to check by clicking on the outside to get the outer box. That is why it is SubFormControlName.Form. That says to return the form within the control.

If it is buried you have to go through the following
me.SubformcontrolName (gets the subform control on the main form)
me.SubformcontrolName.Form (gets the form inside the subform control)
Me.subformControlName.Form.subFormControlName2 (gets the subform control on the first subform)
Me.subformControlName.Form.subFormControlName2.Form (gets the second subform)
Me.subformControlName.Form.subFormControlName2.Form.someControlName (gets the control on the second subform)
So likely

Me.ShopOpNavigation.form.frmmeterAdd.Form.Size.Value
where ShopOpNaviagion and FrmMeterAdd are actually the names of the subform CONTROLS, and not necessarily the subform names
Size is the name of a control on the second subform

Also if your control is called Size that may cause and error since it is a "reserved" word (name of a standard property, method, event). Certain controls have a property called "Size". I would consider renaming. As written the compiler may think you are trying to get the size of the second sub form. Since forms do not have a size property you could get the application defined error.

 

[Lhuffst]

MajP

Thank you very much for all the help. Still struggling somewhat but your explanation was great! Definitely helped me...