Results 1 to 8 of 8
  1. #1
    123seven is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Location
    Aurora, ON Canada
    Posts
    22

    Set focus to a record on a subSub Form based on value on Main form

    Hey Guys,



    I have a Orders form containing a 'single-form view' subform, containing Customer Addresses off to one side. Parent/child link field is ContactID.

    The subform contains a little contact info, for the purpose of identifying the Customer, and includes their Primary Address Info. At the bottom of this (sub)form is another subform ('continuous-forms view' ), containing all the customer's addresses (including the primary address) .. so mainform, subform with subform.

    The Main form is used to review orders before Delivery. Most customers have only 1 address but some could have as many as 50.

    The goal is to properly code an event so that if the order is being 'Delivered' to a different address other than the primary, it's address record should be brought to focus on the subsubform to confirm it is an actual address belonging to the Customer and not entered by error.

    So all i need is for the subsubform's focus, to be on the record with the same [AddressID] as is on the main form's [DeliveryAddressID]

    I know how to reference stuff on the subsubform - as a test, could make Address1 disappear using the following code on the main form:
    Private Sub Form_Current()
    Forms("frmOrdersAllbyDate").Controls("frmAddressEn try").Form.Controls("frmSubAddresses").Form.Contro ls("Address1").Visible = False

    So not sure how or where to write what i think should be something like this: (though this doesn't work).

    SetFocus Forms("frmOrdersAllbyDate").Controls("frmAddressEn try").Form.Controls("frmSubAddresses").Form.Contro ls("AddressID") = Forms("frmOrdersAllbyDate").Controls("DeliveryAddr essID")

    Not sure if this is the correct Method or the structure is wrong or if I'm writing to the wrong Event..

    Appreciate your help.

    cheers,
    shannon

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't understand need for the subsubform. Is there a field in the Order record for DeliveryAddressID? Is there a combobox for selecting delivery address record? This combobox can display the full address. It can even be managed to list only the addresses associated with the customer.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    123seven is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Location
    Aurora, ON Canada
    Posts
    22
    Thanks June7 for the quick reply.

    Yes there is a field for DeliveryAddressID on the main Order form.. that's what the code i tried to create is intended to reference. I understand everything you have suggested and it all makes sense but for this form, which is just for review, i just want to see in an instant.. what address from the subsubform has already been selected for delivery.

    Can you please just tell me what method is required for the appropriate subsubform record to be brought to focus where it's AddressID is the same as that as the main form's DeliveryAddressID... and what event of the main form (if not On Current), this procedure should be written in.

    thank you,
    s

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Maybe just set the Master/Child Links properties? This should show only the one record that is associated with DeliveryAddressID selected in combobox. Assumes you don't want the subform to list all of the customer addresses. I still recommend the combobox approach to display the full address. Address parts can be concatenated into one string or can be in separate columns. Then Locked textboxes can reference the combobox columns to display the address parts. No VBA needed unless you want to conditionally manage the combobox RowSource list dependent on the customer ID. That code is much, much simpler.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    123seven is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Location
    Aurora, ON Canada
    Posts
    22
    Is there no way to do what i've asked.. it's the fastest, easiest solution for us. Utilizing forms that are already in use.

    This form is not where we place orders but where we review orders, prior to Delivery.
    The subform on this Form is based on a qry that allows us to see the Customer and their Primary Address.

    The subform on that form shows ALL that Customer's addresses.

    On the main form in an "Order Note" txtbox, an order entry person will make note of the street address as a fail-safe reference, where the client has multiple addresses.
    On occasion a hasty selection of an address has resulted in the address above or below the intended address being selected. This is the purpose of the Final Visual Review.

    When an Order is made (earlier) an address may be created (for subcontractors with new clients) or an existing one chosen, so yes, i do want to see all the addresses associated to the Customer for the purpose of locating the correct one in the event of the aforementioned error.

    I could delete my subsub form, add a new 2nd subform to the main form and parent/child link it on the deliveryaddressid and get the address record, that has been assigned to the order, as you've suggested, but if it's not the correct address, then where do I go to find alternate addresses for this Customer?

    As it stands it takes, seconds to scroll through a list of addresses in the subsubform ( for the 15-20% of orders with multiple addresses) to locate the matching Address ID, or maybe even less if utilizing 'find' after clicking in the AddressID field ..so all i need to do to make this completely effortless, without rebuilding anything, is enter some simple code (i believe) that has Access scroll to that record programatically ...so i can look at it.. that's all i need.

    thank you
    s

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Fastest and easiest is matter of perception. I view the combobox and associated textboxes as fastest and easiest.
    Find alternate addresses in the combobox RowSource list and combobox is where could easily change the selected address.

    I think what you want has been addressed in other threads but not finding. From what I can recall, requires setting focus on the subform container control. And then running code that will 'go to' desired record.

    How are addresses associated with customer? Is there a master Addresses table with a field for CustomerID? Set Master/Child Links for subform to show only associated address records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    123seven is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Location
    Aurora, ON Canada
    Posts
    22
    Quote Originally Posted by June7 View Post
    Fastest and easiest is matter of perception. I view the combobox and associated textboxes as fastest and easiest.
    requires new building, existing form already functions perfectly, minus the focus set to specific address on subsub.. just need probably 1 or 2 lines of code

    Find alternate addresses in the combobox RowSource list and combobox is where could easily change the selected address.

    technically what already exists in the form of a continuous form, the subsub form on subform Customer/PrimaryAddressINfo
    I think what you want has been addressed in other threads but not finding. From what I can recall, requires setting focus on the subform container control. And then running code that will 'go to' desired record
    yep, i agree.. that's how my first post opened and the code i tried to write references the subform container

    How are addresses associated with customer? Is there a master Addresses table with a field for CustomerID? Set Master/Child Links for subform to show only associated address records.
    Yes to all. That's how the subsubform shows the multiple address records (if they exist) for any customer.

    Thank you for your time JUne7,


  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Set focus with:

    Me.subformcontainername.SetFocus

    However, I just remembered I have a form that finds record in subform and does not require setting focus to subform container:

    Code:
    Private Sub tbxLabNum_AfterUpdate()
    With Me.ctrSampleList.Form.RecordsetClone
        .FindFirst "Submit.LabNum='" & Me.tbxLABNUM & "'"
        Me.ctrSampleList.Form.Bookmark = .Bookmark
    End With
    End Sub
    I know there is some action that requires setting focus to the subform but cannot remember what that is. It might be GoTo NewRecord or setting focus to a specific control https://www.accessforums.net/forms/f...ure-45926.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-02-2012, 03:14 PM
  2. Replies: 8
    Last Post: 08-26-2012, 11:11 PM
  3. Replies: 7
    Last Post: 03-30-2012, 01:43 AM
  4. Replies: 1
    Last Post: 02-29-2012, 09:38 AM
  5. Replies: 11
    Last Post: 01-26-2012, 01:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums