Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-12-2018, 11:00 PM   #1
Bosve
Newly Registered User
 
Join Date: Jan 2010
Posts: 32
Thanks: 3
Thanked 0 Times in 0 Posts
Bosve is on a distinguished road
Change record source on a Form via Combobox

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

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

__________________
Windows 7x64, Access 2013 x86
Bosve is offline   Reply With Quote
Old 07-12-2018, 11:05 PM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,551
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Change record source on a Form via Combobox

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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-12-2018, 11:19 PM   #3
Bosve
Newly Registered User
 
Join Date: Jan 2010
Posts: 32
Thanks: 3
Thanked 0 Times in 0 Posts
Bosve is on a distinguished road
Re: Change record source on a Form via Combobox

Quote:
Originally Posted by ridders View Post
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"

__________________
Windows 7x64, Access 2013 x86
Bosve is offline   Reply With Quote
Old 07-12-2018, 11:45 PM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,551
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Change record source on a Form via Combobox

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Bosve (07-14-2018)
Old 07-13-2018, 12:51 AM   #5
Bosve
Newly Registered User
 
Join Date: Jan 2010
Posts: 32
Thanks: 3
Thanked 0 Times in 0 Posts
Bosve is on a distinguished road
Re: Change record source on a Form via Combobox

Quote:
Originally Posted by ridders View Post
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.
__________________
Windows 7x64, Access 2013 x86
Bosve is offline   Reply With Quote
Old 07-13-2018, 12:53 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,551
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Change record source on a Form via Combobox

Quote:
Originally Posted by Bosve View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-13-2018, 12:59 AM   #7
Bosve
Newly Registered User
 
Join Date: Jan 2010
Posts: 32
Thanks: 3
Thanked 0 Times in 0 Posts
Bosve is on a distinguished road
Re: Change record source on a Form via Combobox

Quote:
Originally Posted by ridders View Post
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...

__________________
Windows 7x64, Access 2013 x86
Bosve is offline   Reply With Quote
Old 07-13-2018, 01:45 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,551
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Change record source on a Form via Combobox

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-13-2018, 03:43 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,551
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Change record source on a Form via Combobox

Cross posted at http://www.accessforums.net/showthre...d=1#post403147

Standard link supplied at that site explaining etiquette of cross posting.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-13-2018, 03:45 AM   #10
Bosve
Newly Registered User
 
Join Date: Jan 2010
Posts: 32
Thanks: 3
Thanked 0 Times in 0 Posts
Bosve is on a distinguished road
Re: Change record source on a Form via Combobox

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
__________________
Windows 7x64, Access 2013 x86
Bosve is offline   Reply With Quote
Old 07-13-2018, 03:50 AM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,551
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Change record source on a Form via Combobox

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 07-13-2018 at 04:00 AM.
isladogs is offline   Reply With Quote
Old 07-14-2018, 12:59 PM   #12
Bosve
Newly Registered User
 
Join Date: Jan 2010
Posts: 32
Thanks: 3
Thanked 0 Times in 0 Posts
Bosve is on a distinguished road
Re: Change record source on a Form via Combobox

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
__________________
Windows 7x64, Access 2013 x86

Last edited by Bosve; 07-14-2018 at 01:00 PM. Reason: spelling
Bosve is offline   Reply With Quote
Old 07-14-2018, 02:24 PM   #13
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,551
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Change record source on a Form via Combobox

Quote:
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Bosve (07-15-2018)
Old 07-15-2018, 06:51 AM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,279
Thanks: 62
Thanked 1,145 Times in 1,046 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Change record source on a Form via Combobox

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Bosve (07-15-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change Record Source of a form to base it on certain table Ramimarachli Forms 2 11-29-2010 11:50 AM
How to change a form/report record source using a macro hkeiner Macros 3 07-01-2010 05:17 PM
Unable to change Record Source in Sub Form patkeaveney Forms 11 04-07-2009 02:29 PM
Change the sub formís record source myhnews General 7 11-16-2005 12:46 PM
How to change the record source of a report from a form MattCollins Forms 8 10-03-2005 08:29 AM




All times are GMT -8. The time now is 11:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World