Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: Using Combobox to set subform ControlSource as table

    Ok, what I want to do is have a combobox that lets the user select a table from the database and add or delete records from it in datasheet view. This is supposed to be a sort of power-user/admin-level way of adjusting what values the comboboxes display throughout the rest of the database.

    Here is the code I am using right now:

    Code:
    Private Sub cboSelectOptionTable_AfterUpdate()
    
        Me!subfrmOptionTable.Form.SourceObject = Me.cboSelectOptionTable.Value
        ' ^ The subform source object = the table selected in the combobox (the table's name as string)
    End Sub
    The problem is that this gives me a runtime error 2467-"The expression you entered refers to an object that is closed or doesn't exist." I think it's refering to the left side of the expression, because I was getting a 3011 error on it earlier when I was referencing it in a different way (I forget how, but I looked up how to reference it properly and got this. )

    The subform is unbound right now, and binding it doesn't fix anything. Also, I can't just make a bunch of subforms because I have 50 tables to edit right now, and many more might need to be added...
    Last edited by RetraRoyale; 05-30-09 at 22:37.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try this code. I personally like to reference the form name itself.

    Private Sub cboSelectOptionTable_AfterUpdate()

    (Also, you may need to put the words "Query" before Forms!MyFormName.cboSelectOptionTable.Value if that value is a query.)

    Forms!MyFormName!subfrmOptionTable.Form.SourceObje ct = Forms!MyFormName.cboSelectOptionTable.Value
    ' ^ The subform source object = the table selected in the combobox (the table's name as string)
    End Sub
    or Try this...
    Forms!MyFormName!subfrmOptionTable.Form.SourceObje ct = "Query." & Forms!MyFormName.cboSelectOptionTable.Value

    Your problem though is that the form you're referencing the value to (ie. me) is not open. It must be open. Either that or your Me.cboSelectOptionTable.Value statement isn't returning a good value. Add msgbox Me.cboSelectOptionTable.Value in your code to test.

    Also, you may need to put the words "Query" before Forms!MyFormName.cboSelectOptionTable.Value if that value is a query.
    Last edited by pkstormy; 05-31-09 at 01:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2009
    Posts
    5

    Tried

    Here's the code now:

    Code:
    Private Sub cboSelectOptionTable_AfterUpdate()
        Dim strLoadTable As String
        
        'Table to load
        strLoadTable = "Table." & Me.cboSelectOptionTable.Value
        
        MsgBox strLoadTable
        
        'Load table in subform
        Forms![frm_Mnu_Manage Configuration Settings]!subfrmOptionTable.Form.SourceObject = strLoadTable
        
    End Sub
    It still doesn't work. I get the error after the message box (which always has the correct table's name in it.) I use copy and paste to set the reference names, so they are correct. If I use the table name as the default control source, it works... I've tried setting SourceObject = "" before loading it, like with a recordsource, but it doesn't help either.

    [Me] is open, because that's where the combobox is operating from. Maybe the subform doesn't open without a sourceobject? But it's open when I have a default sourceobject... Do I need to set the recordsource as well? I just want it to display the whole table in datasheet view.
    Last edited by RetraRoyale; 05-31-09 at 09:21.

  4. #4
    Join Date
    May 2009
    Posts
    5

    Solved

    Ok, I got it to work, finally. Here's the code:

    Code:
        'Table to load
        strLoadTable = "Table." & Me.cboSelectOptionTable.Value
        
        MsgBox strLoadTable
        
        'Clear and Load table in subform
        Forms![frm_Mnu_Manage Configuration Settings]!subfrmOptionTable.SourceObject = strLoadTable
    Just had an extra ".form." in there...

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. Sometimes you use the .form. while other times you don't need to. Sorry - I didn't catch that. Good catch.
    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
  •