Results 1 to 14 of 14
  1. #1
    iDeals is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    22

    Subform listbox populate on load event AND form populate on Click event

    Okay so I'm having trouble getting my list box on my subform to populate properly when the parent form is opened. I have a "Form Load" event as follows:

    Code:
    Private Sub Form_Load()
         Dim strSQL As String
        
        strSQL = "SELECT tblContacts.[ContactID], ([LastName] & "", "" & [FirstName]) AS FullName, tblContacts.[Title], tblContacts.[fkOriginatorID] FROM tblContacts WHERE ((tblContacts.[fkOriginatorID]=Me.Parent.[OriginatorID])) ORDER BY ([lastname] & "", "" & [Firstname]);"
        Me.[tblContacts Query subform].Form!lbContacts.RowSource = strSQL
     
    
    End Sub
    This works, however when the form is loading it prompts me for a value for Me.Parent.OriginatorID. The goal here would be it did this w/o prompting the user.


    My second issue is I'm trying to get the fields above the listbox to update based on the selection of the listbox "On Click" event. Currently instead of reopening the subform with the updated information it is instead opening a second instance of the form in the background.



    Code:
    Private Sub lbContacts_Click()
    DoCmd.OpenForm "fsubContacts", , , "[ContactID] =" & Me.lbContacts
    End Sub
    The desired outcome here would be the fields above populate on click.

    I've attached a test database for reference. Database7 (2).zip

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of using sql, why not just assign the listbox to a query and be done. lbContacts.rowsource = "qsContacts"
    no need to run thru code filling it.

    the query reads the index of the master form, and doesnt need an ON CLICK event.
    you can refresh when you change master records.

    sub form_oncurrent()
    lbContacts.requery

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your form/subform arrangement is backwards. tblOriginator and tblContacts have a 1-to-many relationship. tblOriginator is the 1 side and tblContacts is the many side. Main form should be bound to tblOriginator and subform bound to tblContacts. Then the form/subform Master/Child Links properties will synchronize the related records. The OriginatorID will automatically be saved into tblContacts.

    Alternatively, don't use form/subform. Just have a single form bound to tblContacts with a combobox to select originator.
    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.

  4. #4
    iDeals is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    22
    Quote Originally Posted by ranman256 View Post
    instead of using sql, why not just assign the listbox to a query and be done. lbContacts.rowsource = "qsContacts"
    no need to run thru code filling it.

    the query reads the index of the master form, and doesnt need an ON CLICK event.
    you can refresh when you change master records.

    sub form_oncurrent()
    lbContacts.requery
    That looks like a much simpler approach, which I can definitely appreciate. I will give it a go and see if I can get it working. Thanks1

  5. #5
    iDeals is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    22
    Quote Originally Posted by June7 View Post
    Your form/subform arrangement is backwards. tblOriginator and tblContacts have a 1-to-many relationship. tblOriginator is the 1 side and tblContacts is the many side. Main form should be bound to tblOriginator and subform bound to tblContacts. Then the form/subform Master/Child Links properties will synchronize the related records. The OriginatorID will automatically be saved into tblContacts.

    Alternatively, don't use form/subform. Just have a single form bound to tblContacts with a combobox to select originator.
    June7, I thought my forms were bound. The only thing that wasn't bound was my listbox. Is this not true? I did noticed the relationship appeared to be going the wrong direction, but couldn't figure out how to correct it.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    They are bound. - Wait a minute, think I was confused because both tables are included in both form RecordSources. Don't do that. At least not in the main form. The dependent table (many side) should not be in the main form RecordSource. This will distort the dataset and make the customer (tblOriginator) show duplicated because of the many related records from tblContacts.

    There is no reason to show the OriginatorName in the subform. If you must see it when on the subform tab, then move the textbox from General tab to the main form off the tab control.

    Users don't even need to see the PK/FK fields.
    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
    iDeals is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    22
    Quote Originally Posted by June7 View Post
    They are bound. - Wait a minute, think I was confused because both tables are included in both form RecordSources. Don't do that. At least not in the main form. The dependent table (many side) should not be in the main form RecordSource. This will distort the dataset and make the customer (tblOriginator) show duplicated because of the many related records from tblContacts.

    There is no reason to show the OriginatorName in the subform. If you must see it when on the subform tab, then move the textbox from General tab to the main form off the tab control.

    Users don't even need to see the PK/FK fields.
    Well I tried to fix and made it worse than what I had blundered up to on my own. I replaced my code with a Rowsource Query but now it pulls in all contacts not just those associated with the currently selected Originator.

    I also tried removing OriginatorName from subform to eliminate the duplicate Originator records resulting from the One-to-Many relationship but that didn't do the trick either. Still have 3 records for two Originator/Customers.

    Today's helpless user award goes to me.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Removing the OriginatorName from subform will not eliminate main form duplicates - must remove tblContacts from the main form RecordSource.

    1. main form bound to tblOriginators

    2. subform bound to tblContacts

    3. set subform container Master/Child Links properties to the PK/FK fields

    4. navigate main form and only related records in subform should display
    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.

  9. #9
    iDeals is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    22
    Quote Originally Posted by June7 View Post
    Removing the OriginatorName from subform will not eliminate main form duplicates - must remove tblContacts from the main form RecordSource.

    1. main form bound to tblOriginators

    2. subform bound to tblContacts

    3. set subform container Master/Child Links properties to the PK/FK fields

    4. navigate main form and only related records in subform should display
    Ahh I see, thank you!

    Got rid of the multiple records due to the relationship faux pas...

    Still cant get the query to populate the listbox properly. It was previously showing all contacts, now it is prompting me to enter a value.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't use Me qualifier in query. Only in VBA.

    Tell me again why you need this listbox? Because of subform container Master/Child Links, subform will only show records associated with the main form record. There is no need to apply filter to the subform.
    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.

  11. #11
    iDeals is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    22
    Quote Originally Posted by June7 View Post
    Can't use Me qualifier in query. Only in VBA.

    Tell me again why you need this listbox? Because of subform container Master/Child Links, subform will only show records associated with the main form record. There is no need to apply filter to the subform.
    I guess need is a relative term. The idea behind the listbox is more from a user interface perspective. While in my dummy database there are only a few fields associated with a contact in the live version there are numerous. My hope is to display all contacts (Name, Title) in the list box and have the detail of any one selected contact displayed neatly above. This will prevent the need for clunky scrolling and such in a datasheet view or having to page through records.

  12. #12
    iDeals is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    22
    As far as the Me qualifier in my most recent iteration there isn't one.
    Attached Files Attached Files

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I would use this as the listbox RowSource:

    SELECT tblContacts.ContactID, [LastName] & ", " & [FirstName] AS FullName, [Title]
    FROM tblContacts
    WHERE (((tblContacts.fkOriginatorID)=[tbxOrig]))
    ORDER BY [LastName] & ", " & [FirstName];

    Just referencing fkOriginatorID field in the criteria didn't work so tbxOrig is a textbox on the subform bound to fkOriginatorID - it can be hidden.

    Then this code behind sfrmContacts:

    Private Sub Form_Current()
    Me.FilterOn = False
    Me.lbContacts.Requery
    End Sub

    Private Sub lbContacts_Click()
    Me.FilterOn = False
    Me.Filter = "[ContactID]=" & Me.lbContacts
    Me.FilterOn = True
    End Sub

    However, Access crashes when navigating to another record on main form and subform is filtered so added this to main form:
    Private Sub tblContacts_subform_Exit(Cancel As Integer)
    Me.[tblContacts subform].Form.FilterOn = False
    End Sub
    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.

  14. #14
    iDeals is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    22
    Quote Originally Posted by June7 View Post
    I would use this as the listbox RowSource:

    SELECT tblContacts.ContactID, [LastName] & ", " & [FirstName] AS FullName, [Title]
    FROM tblContacts
    WHERE (((tblContacts.fkOriginatorID)=[tbxOrig]))
    ORDER BY [LastName] & ", " & [FirstName];

    Just referencing fkOriginatorID field in the criteria didn't work so tbxOrig is a textbox on the subform bound to fkOriginatorID - it can be hidden.

    Then this code behind sfrmContacts:

    Private Sub Form_Current()
    Me.FilterOn = False
    Me.lbContacts.Requery
    End Sub

    Private Sub lbContacts_Click()
    Me.FilterOn = False
    Me.Filter = "[ContactID]=" & Me.lbContacts
    Me.FilterOn = True
    End Sub

    However, Access crashes when navigating to another record on main form and subform is filtered so added this to main form:
    Private Sub tblContacts_subform_Exit(Cancel As Integer)
    Me.[tblContacts subform].Form.FilterOn = False
    End Sub
    June7 you are a genius! Had to tinker a bit, but works like a charm. Onto phase 2. Thanks again!

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

Similar Threads

  1. SubForm on Click event
    By raffi in forum Forms
    Replies: 8
    Last Post: 09-27-2014, 11:22 AM
  2. Replies: 5
    Last Post: 06-23-2014, 03:32 PM
  3. Click event of form?
    By BLFOSTER in forum Programming
    Replies: 3
    Last Post: 04-25-2014, 11:29 AM
  4. Dbl Click Event on Form Header
    By drunkenneo in forum Forms
    Replies: 1
    Last Post: 11-18-2013, 04:04 AM
  5. On Click Event For Button On Form
    By Desstro in forum Forms
    Replies: 3
    Last Post: 08-09-2010, 02:36 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