469,299 Members | 2,050 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

Compile error - invalid use of propery

Hi
Self taught user of access and I have the following problem in Access 2003.

I have a form with 2 continous subforms. All the data has been added and all information is correctly linking.

I can re-open the form and add additional information in the the 2 subforms with no problem.

However when I re-open the form and try to edit a record on the main form I get the following error when I try to move on the the subform or save the record:

- compile error - invalid use of property.

This then open the VBA window and highlights the following VBA code:

Private Sub Form_AfterUpdate()


Form [Mileage Expense Form].Form![SubFMileageInfo].Requery


End Sub

This error message does not appear when the main form record is initially created and details are input into both subforms.

The changes made to the main form are in fact still there but I do not know how to alter or add to the code to allow changes to the main form and remove the error.

The main form holds employee details.

1st subform - SubFSiteInfo - hold site details [link field is employee ID to main form]

2nd subform - SubFMileageInfo - holds employee travel information relative to the specific sites they are on. [Link fields are to EEID on main form, and SiteContractNo. on 1st subform]


Any advice would be much appreciated.

Thanks
Shona
Feb 27 '11 #1

✓ answered by TheSmileyCoder

Assuming that you copy pasted your code directly and its not simply a typo, I believe the syntax is off (Though im not 100% sure, since there is usually different ways to reference objects, and I could simply be unfamiliar with this one).

When you place a form (SubFMileageInfo) into another form ( [Mileage Expense Form])as a subform, access will create a control with the same name as the subform to contain the subform. The object contained in the control is known as the SourceObject, while the name of the control is given in the Control's Name property.

To reference the subform another approach could be:
Expand|Select|Wrap|Line Numbers
  1. Forms("[Mileage Expense Form]").SubFMileageInfo.Requery
or
Expand|Select|Wrap|Line Numbers
  1. Forms("[Mileage Expense Form]").SubFMileageInfo.Form.Requery
Both work, because both controls and forms have a requery method. If you wanted to access a control tb_Miles on your subform, you need to use:
Expand|Select|Wrap|Line Numbers
  1. Forms("[Mileage Expense Form]").SubFMileageInfo.Form.tb_Miles.Requery
This is where the difference starts to matter. In the first of the 3 above code bits, we are requering the CONTROL which just happens to contain a form (thus requerying the form). In the second case, we access the Form within the control directly, and ask the form to requery. In the third case, we we access the control stored on the subform, stored in the control.




Now to always make my own code clearer, I rename all controls so the distinction between the control and the content is clear. The same thing goes for simple textbox controls, where the textbox is usually auto-named after the recordsource field bound to the control. I also rename those textboxes.

4 3394
TheSmileyCoder
2,321 Expert Mod 2GB
I dont see why it would only happen when editing a previous record. As far as I know it should happen both when adding a new record and when editing an existing record. The form's afterupdate event gets run in both cases.

You could try replacing the code with:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.   Me.SubFMileageInfo.Requery
  3. End Sub
Feb 28 '11 #2
That worked thank you very much not sure why though - can you tell me what the difference is between the codes that would allow your reply to work and not my original code?
Feb 28 '11 #3
TheSmileyCoder
2,321 Expert Mod 2GB
Assuming that you copy pasted your code directly and its not simply a typo, I believe the syntax is off (Though im not 100% sure, since there is usually different ways to reference objects, and I could simply be unfamiliar with this one).

When you place a form (SubFMileageInfo) into another form ( [Mileage Expense Form])as a subform, access will create a control with the same name as the subform to contain the subform. The object contained in the control is known as the SourceObject, while the name of the control is given in the Control's Name property.

To reference the subform another approach could be:
Expand|Select|Wrap|Line Numbers
  1. Forms("[Mileage Expense Form]").SubFMileageInfo.Requery
or
Expand|Select|Wrap|Line Numbers
  1. Forms("[Mileage Expense Form]").SubFMileageInfo.Form.Requery
Both work, because both controls and forms have a requery method. If you wanted to access a control tb_Miles on your subform, you need to use:
Expand|Select|Wrap|Line Numbers
  1. Forms("[Mileage Expense Form]").SubFMileageInfo.Form.tb_Miles.Requery
This is where the difference starts to matter. In the first of the 3 above code bits, we are requering the CONTROL which just happens to contain a form (thus requerying the form). In the second case, we access the Form within the control directly, and ask the form to requery. In the third case, we we access the control stored on the subform, stored in the control.




Now to always make my own code clearer, I rename all controls so the distinction between the control and the content is clear. The same thing goes for simple textbox controls, where the textbox is usually auto-named after the recordsource field bound to the control. I also rename those textboxes.
Feb 28 '11 #4
Thanks again for your help it is very much appreciated
Shona
Mar 1 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Peter | last post: by
3 posts views Thread by Wayne Allen | last post: by
7 posts views Thread by The|Godfather | last post: by
1 post views Thread by Sister Ray | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.