none
Change record source on a Form via Combobox RRS feed

  • Question

  • How do I change record source on a Form via Combobox? VBA or?

    What I have now:

    1. The user chooses Form1 from Combobox, Form1 opens with record source to Table1
        The user chooses Form2 from Combobox, Form2 opens with record source to Table2
        Form1 and Form2 are identical, the only thing that changes is record source
        

    What I am trying to accomplish is:

    2. The user chooses A from Combobox then FormX opens and based on what user has chosen
        it changes the record source eg. to the Table A. This way I dont<have to have a form A, B, C...



    • Edited by maverick228 Friday, July 13, 2018 6:51 AM misspelled
    Friday, July 13, 2018 6:49 AM

Answers

  • Thank you for all replies and patience with a noob :) 

    I understand Hans suggestion but one table would eventually grow and then run into performance issues. Correct? And I am somewhat reluctant to just have one table as it feels like "placing all your eggs in one basket"

    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


    • Marked as answer by maverick228 Saturday, July 14, 2018 9:01 PM
    Saturday, July 14, 2018 8:51 PM

All replies

  • You can pass the name of the record source in the OpenArgs argument of DoCmd.OpenForm, then use this in the On Open event of the form:

    In the code that opens the form:

        DoCmd.OpenForm FormName:="FormX", OpenArgs:=Me.MyCombo

    In the On Open event of FormX:

    Private Sub Form_Open(Cancel As Integer)
        If Not IsNull(Me.OpenArgs) Then
            Me.RecordSource = Me.OpenArgs
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 13, 2018 7:54 AM
  • You can pass the name of the record source in the OpenArgs argument of DoCmd.OpenForm, then use this in the On Open event of the form:

    In the code that opens the form:

        DoCmd.OpenForm FormName:="FormX", OpenArgs:=Me.MyCombo

    In the On Open event of FormX:

    Private Sub Form_Open(Cancel As Integer)
        If Not IsNull(Me.OpenArgs) Then
            Me.RecordSource = Me.OpenArgs
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thank you for your reply, I implemented the code and  what happens is that the form opens but all values are filled with #Name? Note that the combobox retrievesit's values from a table. This is the on click code for combobox:


    Private Sub cmbMain_Click()
    ' combobox  main form
     
     Select Case cmbMain
     
        Case "A"
       
       DoCmd.OpenForm "FormX", OpenArgs:=Me.cmbMain
     
        Case "B"
       
        DoCmd.OpenForm "FormX", OpenArgs:=Me.cmbMain




    • Edited by maverick228 Friday, July 13, 2018 8:44 AM grammarly
    Friday, July 13, 2018 8:37 AM
  • What are the actual names of the tables?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 13, 2018 10:13 AM
  • .............but all values are filled with #Name? Note that the combobox retrievesit's values from a table.

    If you are changing the form's RecordSource property to a different table name each time, then each of the tables would have to have columns with exactly the same column names.  That would indicate a badly designed database.  Assuming that is not the case, in addition to changing the RecordSource property you would also, in the form's Open event procedure, have to change the ControlSource properties of the bound controls in the form so that they refer to columns in the relevant table, or you would have to have different sets of controls for each of the tables, and set the Visible property of the relevant ones to True, and of the rest to False.

    Either way the process is very cumbersome, and it would make more sense to have separate forms, one for each table.  You can probably do this without too much effort by copying and pasting the existing form under new names, and then, in each copy, changing the relevant properties and controls as appropriate for each.

    Also, you should use the combo box's AfterUpdate event procedure to open the forms, not its Click event procedure.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, July 13, 2018 10:49 AM Typo corrected.
    Friday, July 13, 2018 10:49 AM
  • tblBas2, tblBas1
    Friday, July 13, 2018 11:10 AM
  • I meant all the textfields in the form that I am opening from the combobox and you are right I have to pass the value of the table too...

    Why is it a badly designed database if all my tables have the exact same column names?
    What is/should be a better approach?

    Maybe I am in over my head here but it just seems like it should be straight forward to create one form which is
    used as "universal" form and just change the record source depending what the user chooses in the combobox.

    Friday, July 13, 2018 11:20 AM
  • A better approach would be to use one table with the same structure as the current tables, but with one extra field to distinguish the groups of records. Use the same value in this field as the values of the combo box. You can then filter the form when opening it:

    Private Sub cmbMain_Click()
    ' combobox  main form
       DoCmd.OpenForm FormName:="FormX", WhereCondition:="ExtraField='" & Me.cmbMain & "'"
     End Sub

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 13, 2018 12:41 PM
  • With the current setup, you could use

    Private Sub cmbMain_AfterUpdate()
    ' combobox  main form
      Dim TableName As String
      Select Case cmbMain
        Case "A"
          TableName = "tblBas2"
        Case "B"
          TableName = "tblBas1"
        '...
      End Select
      DoCmd.OpenForm "FormX", OpenArgs:=Me.cmbMain
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 13, 2018 12:44 PM
  • Why is it a badly designed database if all my tables have the exact same column names?

    Because it encodes data in the table names.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    Say we have two tables like this:

    WidgetsType1
    ....WidgetID
    ....WidgetName
    ....Size

    WidgetsType2
    ....WidgetID
    ....WidgetName
    ....Size


    The correct model, as Hans has described, is a single table:

    Widgets
    ….WidgetID
    ….WidgetName
    ….Size
    ….WidgetType

    This makes things very much easier as, if we just want to see the type 1 widgets we can open a form bound to the table, filtered to "WidgetType = 1" by means of the WhereCondition argument of the OpenForm method, as Hans again described, or by basing the form on a query which restricts the result table by referencing an unbound control in the calling form as a parameter, e.g.

    PARAMETERS Forms!frmWiidgetDlg!cboWidgetType SHORT;
    SELECT *
    FROM Widgets
    WHERE WidgetType = Forms!frmWiidgetDlg!cboWidgetType
    OR Forms!frmWiidgetDlg!cboWidgetType IS NULL
    ORDER BY WidgetName;

    With a query like this the form would return only those widgets of the type number selected in the cboWidgetType combo box, or all widgets of all types if the combo box is left NULL.


    Ken Sheridan, Stafford, England

    Friday, July 13, 2018 3:31 PM
  • Thank you for all replies and patience with a noob :) 

    I understand Hans suggestion but one table would eventually grow and then run into performance issues. Correct? And I am somewhat reluctant to just have one table as it feels like "placing all your eggs in one basket"

    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


    • Marked as answer by maverick228 Saturday, July 14, 2018 9:01 PM
    Saturday, July 14, 2018 8:51 PM
  • I understand Hans suggestion but one table would eventually grow and then run into performance issues. Correct? And I am somewhat reluctant to just have one table as it feels like "placing all your eggs in one basket"

    Hi maverick228,

    With proper indexing millions of records should not be a problem with respect to performance. I expect more problems in the final run with the management of many the-same tables.

    Imb.

    Sunday, July 15, 2018 7:46 PM