INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

not understanding something....

not understanding something....

(OP)
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.

CODE

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

RE: not understanding something....

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.

RE: not understanding something....

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.

RE: not understanding something....

(OP)
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,

CODE

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

RE: not understanding something....

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.

RE: not understanding something....

(OP)
MajP

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close