Change record source on a Form via Combobox

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...

Answer 1

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)

Answer 2

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

Answer 3

What are the actual names of the tables?

---

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

Answer 4

.............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

Answer 5

tblBas2, tblBas1

Answer 6

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.

Answer 7

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)

Answer 8

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)

Answer 9

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

Answer 10

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

Answer 11

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.