Change record source on a Form via Combobox (1 Viewer)

Bosve

Registered User.
Local time
Today, 07:41
Joined
Jan 27, 2010
Messages
35
How do I change record source on a Form via Combobox? VBA:confused:

What I have now:

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, Form2, FormX are identical, the only thing that changes is record
source eg. TableA


What I am trying to accomplish is:

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 don't have to have a form A, B, C...
 

isladogs

MVP / VIP
Local time
Today, 06:41
Joined
Jan 14, 2017
Messages
18,186
So effectively you will have one form which is filtered to the selection in the combo box - i.e. to a particular person or thing.

So your form record source needs to include a WHERE clause based on the bound column of the combo. Does that make sense?
 

Bosve

Registered User.
Local time
Today, 07:41
Joined
Jan 27, 2010
Messages
35
So effectively you will have one form which is filtered to the selection in the combo box - i.e. to a particular person or thing.

So your form record source needs to include a WHERE clause based on the bound column of the combo. Does that make sense?

Yes it makes sense, how do I do that with VBA? This is how the code looks like on click event of the combobox:

Private Sub cmbMain_Click()
' combobox main form

Select Case cmbMain

Case "A"

DoCmd.OpenForm "A"
' open form


Case "B"

DoCmd.OpenForm "B"

Case "C"

DoCmd.OpenForm "C"
 

isladogs

MVP / VIP
Local time
Today, 06:41
Joined
Jan 14, 2017
Messages
18,186
It would help if you gave some detail about the contents of your form and combo box. As you haven't this is by necessity vague

Let's assume your combo cmbMain has a bound field called ID and its a number field. Use the after update event not the Click event. The ID field needs to be used in the record source for the form being opened

Code:
DoCmd.OpenForm "YourFormName",,,"ID =" & Me.cmbMain

If the bound column is a text field then text delimiters are needed

Code:
DoCmd.OpenForm "YourFormName",,,"ID = '" & Me.cmbMain & "'"

HTH
 

Bosve

Registered User.
Local time
Today, 07:41
Joined
Jan 27, 2010
Messages
35
It would help if you gave some detail about the contents of your form and combo box. As you haven't this is by necessity vague

Let's assume your combo cmbMain has a bound field called ID and its a number field. Use the after update event not the Click event. The ID field needs to be used in the record source for the form being opened

Code:
DoCmd.OpenForm "YourFormName",,,"ID =" & Me.cmbMain

If the bound column is a text field then text delimiters are needed

Code:
DoCmd.OpenForm "YourFormName",,,"ID = '" & Me.cmbMain & "'"

HTH
Main Form > One combobox that retrieves the list of values from table
FormA > opens when selected in prevoius combox, record source is set via forms properties to TableA. A simple form with a couple of text fields.
 

isladogs

MVP / VIP
Local time
Today, 06:41
Joined
Jan 14, 2017
Messages
18,186
Main Form > One combobox that retrieves the list of values from table
FormA > opens when selected in prevoius combox, record source is set via forms properties to TableA. A simple form with a couple of text fields.

That doesn't tell me anything else useful
However, you should have enough info from my previous answer to implement this now
 

Bosve

Registered User.
Local time
Today, 07:41
Joined
Jan 27, 2010
Messages
35
That doesn't tell me anything else useful
However, you should have enough info from my previous answer to implement this now
I don't understand how implementing your code will change the record source of the form...
 

isladogs

MVP / VIP
Local time
Today, 06:41
Joined
Jan 14, 2017
Messages
18,186
The form record source is set on the form and should have no filter
The code shown acts as the filter criteria

It’s a standard approach in Access
 

Bosve

Registered User.
Local time
Today, 07:41
Joined
Jan 27, 2010
Messages
35
Maybe I am in over my head here but it just should be straight forward to create one form which is used as "universal" form and then change the record source depending what the user chooses in the combobox. In theory...

It seems like copy/paste of the forms and then changing names and properties of the forms is more straight forward :)
 

isladogs

MVP / VIP
Local time
Today, 06:41
Joined
Jan 14, 2017
Messages
18,186
What I have suggested is the simplest way of doing this if you are just wanting to filter the data in the SAME table. I thought you confirmed that when I asked earlier...but reading it again, perhaps I was wrong.

If you want to use a different table each time, the form design will only work if all tables have the SAME structure. If you have multiple tables with identical structures, your database design is poor and needs changing
 
Last edited:

Bosve

Registered User.
Local time
Today, 07:41
Joined
Jan 27, 2010
Messages
35
Thank you for all replies and patience with a noob :)

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
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 06:41
Joined
Jan 14, 2017
Messages
18,186
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"

Hmm...basically NO to both points

Access is designed to handle very large tables.
For example I have several tables with over a million records and one with around 2.6 million records. Performance isn't an issue if the table is designed well and appropriate fields are indexed

If your data structure is such that one table would work then it is a mistake to use several. If you are worried about possible data loss, the main issue is to backup your database regularly e.g. at the end of each day
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:41
Joined
Feb 28, 2001
Messages
27,001
From a theoretical point of view, this sounds like a normalization problem. Having three tables for which this is even possible (same exact form, which will perform the same possible actions, but three different tables) implies that the tables are intimately related in some way. Which means they SHOULD be one table with one more field that differentiates whatever it was that made them go into one table or another.

Normalization has all sorts of complex ways to say it, but there is the descriptive issue that I think applies. When you have a properly normalized table, then every record's contents depend on the primary key of the table. You have intimately related data in the database for which one record's content not only would depend on its key but ALSO depend on its location - yet the structures are identical. You have violated the general rule about "like goes with like."

If you can merge those tables by adding one more field (to each obviously) such that you know from which table they came, you could then merge the tables and have one form, one table. And your question then becomes the one that Ridders has so efficiently answered, namely filtering a table via your combo box.
 

Users who are viewing this thread

Top Bottom