Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    smbrush is offline Experienced Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    30

    Get a value from the current record in a subform and select that value in a combo box

    I have a form with a combo box control and a subform in datasheet view. They are both populated from the same database table. The combo box has the values of one of the columns in the subform.

    How can I retrieve a value from a subform row selected by the user and then use that value in code for the main form? I want to place the value in the text box portion of the combo box and then fire its AfterUpdate event.

    There are only two events available for the subform, neither of which are effective for capturing user clicks on subform rows. The main form's OnCurrent event doesn't fire when the user clicks on subform rows, as well. What event do I use for code to determine which subform row the user has clicked on?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    it usu works the other way, pick a value in the master form, to see the items in the subform.
    why are you doing it backwards?

    does that mean all 3, the main form, the subform and the combo box ALL have the same records?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Form's (whether an independent or sub form) OnClick event works for me. Must click in the RecordSelector at left edge of form.

    What exactly do you want to do with value from subform record?

    Combobox AfterUpdate event will not automatically fire when combobox populated by code. However, can Call the AfterUpdate 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.

  4. #4
    bosve73 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2010
    Posts
    22
    Quote Originally Posted by smbrush View Post
    I have a form with a combo box control and a subform in datasheet view. They are both populated from the same database table. The combo box has the values of one of the columns in the subform.

    How can I retrieve a value from a subform row selected by the user and then use that value in code for the main form? I want to place the value in the text box portion of the combo box and then fire its AfterUpdate event.

    There are only two events available for the subform, neither of which are effective for capturing user clicks on subform rows. The main form's OnCurrent event doesn't fire when the user clicks on subform rows, as well. What event do I use for code to determine which subform row the user has clicked on?
    Thank you for all replies and patience with a noob


    My solution was this:


    Private Sub Combo0_Click()

    Select Case Combo0

    Case "item1"
    DoCmd.OpenForm "Form1"
    Forms!Form1.RecordSource = "Table1"

    Case "item2"
    DoCmd.OpenForm "Form1"
    Forms!Form1.RecordSource = "Table2"

    Case "item3"
    DoCmd.OpenForm "Form1"
    Forms!Form1.RecordSource = "Table3"



    End Select

    End Sub



  5. #5
    smbrush is offline Experienced Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    30

    How dare you!

    Quote Originally Posted by bosve73 View Post
    Thank you for all replies and patience with a noob


    My solution was this:


    Private Sub Combo0_Click()

    Select Case Combo0

    Case "item1"
    DoCmd.OpenForm "Form1"
    Forms!Form1.RecordSource = "Table1"

    Case "item2"
    DoCmd.OpenForm "Form1"
    Forms!Form1.RecordSource = "Table2"

    Case "item3"
    DoCmd.OpenForm "Form1"
    Forms!Form1.RecordSource = "Table3"



    End Select

    End Sub


    How dare you call me a noob! I've been developing apps for years. Your snotty attitude is as useless as the so called solution you included in your reply. I'm looking for a way to respond to a user click in a subform, not the main form. Maybe if you didn't have your head so far up your ass, you would have read my post completely and understood what I was looking for. Don't post to these boards with attitude!

  6. #6
    smbrush is offline Experienced Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    30
    Yes, the form, subform databasheet, and combo box are all populated with values from the same table. I've been instructed to capture a user click on the subform and place one of the values in the selected row as the combo box's value. I know how to place a value in a combo box. What I'm asking about is what event do I use. As stated in my post, the subform has only two events associated with it, On Enter and On Exit, neither of which do the job. Do you know of an event on the main form that can detect a subform click? On Current doesn't work.

  7. #7
    smbrush is offline Experienced Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    30
    As stated in my post, the subform has only two events associated with it, On Enter and On Exit. There is no On Click event for the subform. It's not an object in my navigation pane so I can't open it as its own object. Do you know of an event on the main form that can detect user clicks on the subform?

  8. #8
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You are looking at the events for the subform container, not the actual subform.
    You need the OnCurrent event of the subform which can then set something on the parent form itself, not the container.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    smbrush is offline Experienced Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    30
    How do I access the subform beyond the container? There is no listing of the subform in the Property Sheet dropdown list other than the name of the subform, which is the container. Double clicking on the subform doesn't change anything. How can I access the subform's events?

  10. #10
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Normally you simply click on the forms contents within the subform container in design mode?
    Alternatively you can simply open the sub form directly in design view and code it from there, but be aware that the intellisense won't be able to help you with me.parent. references.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    smbrush is offline Experienced Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    30
    I've been trying to tell you that I can't open the subform by itself in design view. I see the subform object on the main form, which is the container, but I can't access the subform itself. This object doesn't appear in the navigation pane. How do I access the subform underlying its container?

  12. #12
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is this on one of the inbuilt navigation form by any chance?
    Or are you setting the sub forms control source programmatically ?
    Click image for larger version. 

Name:	subformObject.jpg 
Views:	8 
Size:	152.3 KB 
ID:	34738

    As you can see this is how it should appear. The yellow shows a form within the form.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  13. #13
    smbrush is offline Experienced Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    30
    I've assigned a query as the source object for the subform. I set a filter for the subform rows based on user selections on the main form using the LinkMasterFields and LinkChildFields properties.

    I added this control to the main form from the Controls list in the Design tab of the ribbon. It's just a form within a form, a subform.

    Strangely enough, I have another form in my project with a subform and that subform appears on in the Navigation Pane. I don't know why the subform on this other form doesn't also appear. Is there a setting I may have disabled which would cause this subform not appear in the navigation pane?

  14. #14
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Ah ha - I believe we have it Watson...
    I've assigned a query as the source object for the subform.
    This is why. A query is NOT a form, Access very kindly lets you use a query as a subform but you don't get all the functionality or pretties...

    Create a simple datasheet form based on on the query and you will get your form properties back, and hopefully solve your issue.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  15. #15
    smbrush is offline Experienced Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    30
    Please read the last sentence of my previous post. I created a subform in another form and that subform appears as an object in the navigation pane. For some reason, the subform in the form I'm developing now does not appear as an object in the navigation pane. I did exactly the same method to create both subforms but only one of them appears as an object in its own right. Is this a system error in Access 2010?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-23-2016, 08:39 PM
  2. many forms, how to select current record
    By bholgate in forum Access
    Replies: 6
    Last Post: 10-07-2015, 12:39 PM
  3. Replies: 7
    Last Post: 02-18-2015, 12:07 PM
  4. Replies: 4
    Last Post: 02-17-2015, 06:49 AM
  5. combo box based on current record
    By snewman in forum Access
    Replies: 2
    Last Post: 02-09-2014, 03:44 PM

Tags for this Thread

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