Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I'm trying to update a field on one form to a field on another form, but am receiving a VB error. I'm using the following code in the 'After_Update' section of the field:

    Private Sub DeptAIP_AfterUpdate()
    Forms![ProgramMovements].LatestAIPDate = Me.DeptAIP
    End Sub

    I have the same code in a different sub-form which works. This sub-form is part of the 'ProgramMovements' form, so I'm not sure if that's why that code works, but this code in a separate form doesn't work.

    Basically, when I enter data in to the 'DeptAIP' field and then click out of the field, I get a VB error:
    "Microsoft Office Access can't find the form 'ProgramMovements'......"

    Any help would be appreciated.

    Cheers,
    Jason

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In order to update a field on a form, that form must be open. The error message suggests it is not. If the subform is on that form, then the code would work, because the form has to be open in order for the subform to be open. If that is not the case, let us know and we'll try to help resolve the issue.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='794494' date='23-Sep-2009 13:06']In order to update a field on a form, that form must be open. The error message suggests it is not. If the subform is on that form, then the code would work, because the form has to be open in order for the subform to be open. If that is not the case, let us know and we'll try to help resolve the issue.[/quote]

    Hi Wendel,

    Yes, that is the case - the subform is on that form, which is why that code works. Is there another way around this, or should I just update the underlying field in the table itself?

    Cheers,
    Jason

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Can you tell us exactly where the controls DeptAIP and LatestAIPDate are, i.e. on which form/subform?

  5. #5
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794503' date='23-Sep-2009 15:35']Can you tell us exactly where the controls DeptAIP and LatestAIPDate are, i.e. on which form/subform?[/quote]

    Hi Hans,

    DeptAIP is in form "BidDataEnter" and LatestAIPDate is in form "ProgramMovements"

    Cheers,
    Jason

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    And what is the relationship of BidDataCenter and ProgramMovements? Is BidDataCenter a subform of ProgramMovements or the other way round or neither?

  7. #7
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794635' date='24-Sep-2009 09:14']And what is the relationship of BidDataCenter and ProgramMovements? Is BidDataCenter a subform of ProgramMovements or the other way round or neither?[/quote]

    There is no relationship between BidDataEnter and ProgramMovements.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You have made mention of subforms earlier in this thread.

    When you want to refer to a control on a subform, you need to use a different syntax than if you refer to a control on a form that is not a subform.
    The Forms collection includes all open forms, but not open subforms.

    So is the control you want to update on a form or a subform?
    Regards
    John



  9. #9
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='794647' date='24-Sep-2009 13:40']You have made mention of subforms earlier in this thread.

    When you want to refer to a control on a subform, you need to use a different syntax than if you refer to a control on a form that is not a subform.
    The Forms collection includes all open forms, but not open subforms.

    So is the control you want to update on a form or a subform?[/quote]

    Hi John,

    The control I want to update is on a form - the field is called 'LatestAIPDate' and it is on the 'ProgramMovements' form.

    I mentioned a sub-form earlier because the example code I used works in a sub-form that is on the 'ProgramMovements' form. Wendell mentioned that the same code doesn't work on another form because the 'ProgramMovements' form isn't open when I try to run the code.

    Cheers,
    Jason

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    So you are trying to update a control on a form that is not open? You cannot do that.
    If the form is not open, just update the value in the underlying field.
    Regards
    John



  11. #11
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='794650' date='24-Sep-2009 14:16']So you are trying to update a control on a form that is not open? You cannot do that.
    If the form is not open, just update the value in the underlying field.[/quote]

    Thanks John, I think I asked that question myself in my first reply. Are you able to help with code that would let me update the field in the underlying table, as I'm not sure how to do this. The table name is tblBill and the field is AIP_LatestAIPDate

    Cheers,
    Jason

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You did indeed ask that question at the start. But if the form had been open, that would have been easier.

    You need to be able to identify which record you want the field updated for.
    So is there something on the currently open form that identifies which record? Or is there some other way ?

    Usually you would need to specify a value for the key field in the table you want updated. What is the Key? What type of data is it?
    And what type of data is AIP_LatestAIPDate - presumably a Date field.
    Regards
    John



  13. #13
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='794655' date='24-Sep-2009 15:04']You did indeed ask that question at the start. But if the form had been open, that would have been easier.

    You need to be able to identify which record you want the field updated for.
    So is there something on the currently open form that identifies which record? Or is there some other way ?

    Usually you would need to specify a value for the key field in the table you want updated. What is the Key? What type of data is it?
    And what type of data is AIP_LatestAIPDate - presumably a Date field.[/quote]

    The 'BillBidInceptionNo' identifies the record. This is an AutoNumber.

    AIP_LatestAIPDate is a Date/Time field.

    Cheers,
    Jason

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The easiest option is probably to create an Update Query, that uses the BillBidInceptionNo from the open form as a parameter.

    Do you know how to create update queries?

    The After Update event would then just run the query.

    Docmd.OpenQuery "qryYourQuery"
    Regards
    John



  15. #15
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='794784' date='25-Sep-2009 07:53']The easiest option is probably to create an Update Query, that uses the BillBidInceptionNo from the open form as a parameter.

    Do you know how to create update queries?

    The After Update event would then just run the query.

    Docmd.OpenQuery "qryYourQuery"[/quote]

    Hi John,

    I've worked out how to create a new query and then change it to an 'update query', but not sure how to modify this query so that it accepts data from the field on the form.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •