Results 1 to 8 of 8
  1. #1
    cbh35711 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    40

    Referencing Controls on Forms

    Hey all,

    I have a main form, 2 sub forms, and 2 text-boxes on the main form. I have a search box now for the first subform. Works perfectly. Once the users filtered based on name, I want them to be able to select that filtered record which would filter another subform.

    Right? ok.



    My first attempt was to assign the ID that was selected to a textbox on the main form, then automatically requery the second subform based on that value. I used this way on my search box. I'm sure there is a way to go directly from clicking the value to requerying the second subform, but i don't know it. If you do, i would be grateful.

    That being said even the indirect first attempt isn't working. It all has to do with the ".", "!", and ordering of this stuff. I'm not sure what . and ! mean. Can anyone explain them, or point is an explanation? It has to do something with Parent child, but beyond that i'm at a loss.

    Code:
    Me.[Collateral] = Acct_Reference_id
    Me.[Navigation]![textbox] = Acct_Reference_id
    ...
    'Me!Subform1.Form!ControlName
    'Me!Subform2.Form!ControlName
    'Me.Parent!ControlName
    I did find this very useful site http://access.mvps.org/access/forms/frm0031.htm. It shows what code you can use depending on where you are and what you're doing. Even with that as a resource, i'm still at a loss.

    Any assistance will be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The dot (.) will provoke intellisense popup tips in VBA code. The bang (!) does not.

    They can often be interchangeable but I use . when I want to refer to objects and controls. I use ! when referring to recordset fields.

    You want record in one subform to control filter in another subform? I have found subform referencing another subform to be quite frustrating.

    Do you want to provide db for analysis?
    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
    cbh35711 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    40
    Thanks for the response June. I missed the notification.

    .! Awesome intel.

    I can't really provide the database. I attached a screenshot of the main form in Design view. The control filter is the unbound box below the search box. So, the user goes in and searches for a record. When the user selects the record in the first datasheet view, the two other subforms filter based on that record. After that point the user would open one of those records. That popup form has a macro that user can press to basically create another version of that pop form, but with only some of the fields brought over. When that macro runs, it thinks it is running from the main form but it needs to be running from the pop-up form.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	51.7 KB 
ID:	7030

    I hope that helps...

    Thanks again!

    Chris

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Doesn't really help me. Lose me at 'That popup form has a macro...'

    Having two forms open to the same record can be problemmatic. This is like two users hitting on the same record.

    Why can't provide db? Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post.
    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
    cbh35711 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    40
    Ha. Yeah...i guess it wouldn't.

    I was worried about confidentially. I have wiped out the data. Test FE.zip

    You'll have to relinked the FE to the BE. So select the first blue Open. Once open, select Create and Attach Loan. This macro transfers the obligor_id from the open borrower to the new loan. Great right? It works because the "Set frmPrevious = Screen.ActiveForm". The problem it isn't working for the second set of blue opens. The reason is that it isn't choosing the correct form as the frmPrevious. Of course, i'm not saying it is doing something wrong. Clearly, I am.

    Let me know what other details i can provide.

    Thanks again for your time!

    Chris

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I opened the Navigation v2 form and tested the 'Open' behavior. Couldn't get macro to work, gave up and converted to VBA:

    DoCmd.OpenForm "Borrower Details", acNormal, , "[ID]=" & [ID], , acDialog

    Don't know why you used the Nz function on the ID criteria so I dropped it.

    Where do I select Create and Attach Loan?

    Not really understanding the 4 subforms. They all have RecordSource that filters on the Search box under the Borrower Name field. Why not have main form bound to Borrower Search (no subform for this) and the other 3 subforms set Master/Child links on the Borrower Obligor_ID pk/fk fields? Could put the 3 subforms on pages of a tab control.

    What should I enter into the Search box? A name? Why not a combobox?
    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
    cbh35711 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    40
    June,

    Not sure why the macro wouldn't work. There's no functional change in what you did. But you're correct, NZ is unnecessary.

    Borrower details is where you create and attach a loan. It takes some of the fields and transfer it to the new loan details form.

    As far as the general structure, I don't really know what value the master/child method would add. This has all the functionality i am looking for since "Obligor_id = Forms![navigation v2]![loan search subform]!Obligor_id.Value" is actually working now.

    Correct a name goes in the search box. Just tried the combo box. Didn't realize it auto-populated the rest. I like it much better.

    I am continuing to work on this trying various things. Any additional advice you could offer would be appreciated.

    Thanks so much for your help so far.

    Chris

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I just noticed the code for the requery of subforms is wrong. Use:

    Me.Loan_Search_subform.Requery
    Me.Collateral_Search_subform.Requery
    Me.Action_Search_subform.Requery
    Me.Borrower_Search_subform.Requery
    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. Controls in forms and subforms
    By donnan33 in forum Access
    Replies: 2
    Last Post: 01-05-2012, 10:29 AM
  2. referencing a control
    By looloo in forum Programming
    Replies: 3
    Last Post: 09-23-2011, 07:57 PM
  3. Filling controls on two forms
    By recon2011 in forum Forms
    Replies: 3
    Last Post: 09-01-2011, 01:54 PM
  4. Replies: 1
    Last Post: 06-24-2011, 04:27 PM
  5. Referencing problem
    By thegamezn in forum Forms
    Replies: 1
    Last Post: 12-09-2005, 10:13 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