UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Change Record Source Using Vba, Access 2007    
 
   
JanRaven
post Nov 4 2019, 02:42 AM
Post#1



Posts: 11
Joined: 22-March 19



Hi All.

I've got a form with a subform. In the subform, in Property Sheet ---> Data ---> Record source I have: SELECT * FROM tbLabel;
In my form I would like to place a frame with 2 options so that when I choose one potion my subform's record source reads data from one table and when I select the second option my subform's record source should read data from the other table.

How can I achieve this using the VBA?

Any help appreciated.
Go to the top of the page
 
Larry Larsen
post Nov 4 2019, 03:18 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,385
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
May I ask how different are these two tables in both their structure (fields) and data..

My early thoughts would be based on two sub forms that are made visible or hidden on the selection of a control..

thumbup.gif iconfused.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
JanRaven
post Nov 4 2019, 03:28 AM
Post#3



Posts: 11
Joined: 22-March 19



They are identical.
Go to the top of the page
 
Larry Larsen
post Nov 4 2019, 04:36 AM
Post#4


UA Editor + Utterly Certified
Posts: 24,385
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

Ok many thanks..

My time is limited as I'm about to leave the building, here is a link that quickly.simply shows you how to control a forms "RecordSource".

Form.RecordSource property (Access)

Link info:
You can use the RecordSource property to specify the source of the data for a form. Read/write String.

Syntax
expression.RecordSource

expression A variable that represents a Form object.

Remarks
The RecordSource property setting can be a table name, a query name, or an SQL statement. For example, you can use the following settings.

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Scotty
post Nov 28 2019, 07:32 AM
Post#5



Posts: 1
Joined: 9-April 19



Hallo

If the table structure is the same, you can change the data table through vba.
In case the structure is not the same then you can create 2 subforms and set them through vba

Regards, Marc
Go to the top of the page
 
gemmathehusky
post Nov 28 2019, 11:23 AM
Post#6


UtterAccess VIP
Posts: 4,745
Joined: 5-June 07
From: UK


the recordsource for a form is me.recordsource

so you should be able to say

me.recordsource = whatever in the frame's after update.

the subform that is actually represented in a forms subform is

subform.sourceobject rather than the recordsource. (this syntax is possibly off)

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
PaulBrand
post Nov 28 2019, 01:32 PM
Post#7



Posts: 1,735
Joined: 4-September 02
From: Oxford UK


In the option group AfterUpdate event use something like this:

CODE
Select Case OptionGrpName

Case 1
Me.RecordSource = SELECT * FROM tbLabel

Case 2
Me.RecordSource = ...

End Select

--------------------
Paul
Go to the top of the page
 
tina t
post Nov 29 2019, 01:05 AM
Post#8



Posts: 6,175
Joined: 11-November 10
From: SoCal, USA


as Paul says; though if the option group control is in the mainform, then to change the RecordSource property of the subform, you'd need

CODE
Select Case OptionGrpName

Case 1
Me!MySubformControlName.Form.RecordSource = SELECT * FROM tbLabel

Case 2
Me!MySubformControlName.Form.RecordSource = ...

End Select

make sure you reference the name of the subform control that holds the subform object - not the name of the subform itself. the subform control may have the same name as the form object it holds, or it may have a different name. to get the name of the subform control, open the mainform in Design view, click once on the subform, and look at the Name property in the Properties box.

btw, i am wondering why you have two separate tables with identical fields?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 12:15 AM