Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    38

    Unanswered: Updating Form Field Based on Contents of a another form

    I have a form that has a drop down at the top which allows me to pick up a store. While you are viewing a store record, you also have the option to create a new store by hitting a command button in the same form which opens a small form on top of the open form. Once I create the new store and close this top form, I would like the drop down on the form below re queried so that newly created store appears in the drop down list. I know can wtite sometinging similar to the following so that I can update the list.
    Forms!FrmSetup.ComboPickStore.Requery
    But the problem is I have to deal with different similar forms created for each region and I don't want a hard reference in the above statement. In other words I want to amend data in a form and based on the changes made, I want a field to be requeried on the underlying (seperate form in its own right) after I close the form.

    Will appreciate some help.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To update a bound text box on another form with values from the form your on....

    Forms!MyFormNameGettingUpdated!FieldName = Forms!MyFormNameSendingUpdates!FieldName

    and you may have to add this line of code after....
    Forms!MyFormNameGettingUpdated.repaint
    or
    Forms!MyFormNameGettingUpdated.setfocus
    refresh
    Forms!MyFormNameSendingUpdates.setfocus

    To requery a field (although the repaint command sometimes handles this), you can try this....

    Forms!MyFormName!FieldToBeQueried.requery (similar to what you have)
    Keep in mind that requerying might send you to another record on the updatingtoform in which you can also try this to get back to your original record...

    dim CID as variant
    CID = me!MyAutonumberIDfieldName
    Forms!ThisFormName.requery
    Forms!MyAutonumberIDFieldName.setfocus (must be enabled but should be locked, must be visible.)
    docmd.findrecord CID
    Forms!SomeOtherFieldName.setfocus

    or you can also try this....
    Forms!MyFormName!FieldToBeQueried.repaint

    You can optionally do any of these events anywhere you want (ie. in the button OnClose event that closes the form BEFORE the docmd.close code.) For the ones where you use the FindRecord CID command, you would need to populate CID with the value BEFORE you closed the form.
    Last edited by pkstormy; 09-22-08 at 21:32.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2006
    Posts
    38
    Thanks a lot. Where would this code sit? On the on close event of the form I am on and closing?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I wouldn't put the code right in the OnClose event of the form but instead I'd put the code right before the docmd.close code in a button on the form to close the form.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You might also want to add this command....

    Refresh

    before this command.
    Forms!MyFormNameGettingUpdated!FieldName = Forms!MyFormNameSendingUpdates!FieldName

    (Just makes sure the data has been updated on the form before passing the value.)

    ALLWAYS make sure your data modifying form is Refresh before sending data to another form.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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