Is there a way to change a forms record source from a command button? I have a form with a record source from a table for adding records, but want to use the same form with a query as it's record source when I open it from a command button on a switch board or another form. This way I don't need several different forms for several different queries...I hope that makes sense. I am new to this.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Change a forms record source from a command button 1
- Thread starter WallT
- Start date
-
1
- #2
1.Don't specify Record source in Design time.
2. In form open event change the record source accordingly.
Select case record
case [Form Search form]
Me.Recordsource = Query1
case [From Menu Form]
Me.Recordsource=Table1
end Select Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!
2. In form open event change the record source accordingly.
Select case record
case [Form Search form]
Me.Recordsource = Query1
case [From Menu Form]
Me.Recordsource=Table1
end Select Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!
- Thread starter
- #3
Okay, I like this. However, I am getting a "Variable Not Defined" error, and then debug is highliting "RECORD" after Select Case. I am assuming the Brackets are enclosing the form name where the command button is? On my DB it would be[Form ProcessMenu]. Am I supposed to reference the Command button anywhere? Like, what if I have several command buttons on one form that open that same form using a few different record sources?
Sorry for the confusion.
Code should be something like this.
1.This code is good if you are calling from different forms.
Select case sgSearch
case "ProcessMenu"
Me.Recordsource= Query1
case "SearchMenu"
Me.Recordsource=Table1
case "CompanyName"
Me.Recordsource=Query2
End Select
2.If you are calling from One form with Several command buttons
Set sgSearch to Keyword like "CompanyName" etc.. Different keywords in each command button click event.
E.g. sgSearch="CompanyName"
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!
Code should be something like this.
1.This code is good if you are calling from different forms.
Select case sgSearch
case "ProcessMenu"
Me.Recordsource= Query1
case "SearchMenu"
Me.Recordsource=Table1
case "CompanyName"
Me.Recordsource=Query2
End Select
2.If you are calling from One form with Several command buttons
Set sgSearch to Keyword like "CompanyName" etc.. Different keywords in each command button click event.
E.g. sgSearch="CompanyName"
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!
- Thread starter
- #5
I am still getting the same error. I am real new to this so here is what I am working with:
The form that the command buttons are on is called "ProcessMenu".
There is 2 command buttons.
1. CMDAddRec 'When using this command button I want the record source to be from my table "TBL Orders"
2. CMDBillRec 'When using this command button I want this record source to be from my Query "QRY Billing"
Hopefully that will help...I appreciate you help on this.
The form that the command buttons are on is called "ProcessMenu".
There is 2 command buttons.
1. CMDAddRec 'When using this command button I want the record source to be from my table "TBL Orders"
2. CMDBillRec 'When using this command button I want this record source to be from my Query "QRY Billing"
Hopefully that will help...I appreciate you help on this.
1.Declare a Global variable in a module like
Public sgFilter as string
2.In ProcessMenu
a)In CMDAddRec Click event
sgFilter = "Orders"
b)In CMDBillRec Click Event
sgFilter = "Bill"
3. In your Second Form Open Event
Select Case sgFilter
case "Orders"
Me.RecordSource="TBL Orders"
case "Bill"
Me.RecordSource"QRY Billing"
end Select Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!
Public sgFilter as string
2.In ProcessMenu
a)In CMDAddRec Click event
sgFilter = "Orders"
b)In CMDBillRec Click Event
sgFilter = "Bill"
3. In your Second Form Open Event
Select Case sgFilter
case "Orders"
Me.RecordSource="TBL Orders"
case "Bill"
Me.RecordSource"QRY Billing"
end Select Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!
- Thread starter
- #7
When you open the database, you can see Tables,Queries,Forms,Reports,Macros and Modules.Click On Modules.
Click on New, then Type -- Public sgFilter As String ---.
And save it as basGlobal(name of the module). Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!
Click on New, then Type -- Public sgFilter As String ---.
And save it as basGlobal(name of the module). Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!
- Thread starter
- #9
This is actually a lot easier than all this.
Private Sub btnOpenOtherForm_Click()
Call DoCmd.OpenForm("frmWhatever", , , , , , "qryYadda"
End Sub
This passes the name of the query in the OpenArgs parameter. Then, in the Load event of your form, do something like this...
If me.openargs = "qryYadda" then
blah blah blah
end if
If you're going to be using several different recordsources, you can use a select/case structure.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Remember to reward helpful tips with the stars they deserve.
Private Sub btnOpenOtherForm_Click()
Call DoCmd.OpenForm("frmWhatever", , , , , , "qryYadda"
End Sub
This passes the name of the query in the OpenArgs parameter. Then, in the Load event of your form, do something like this...
If me.openargs = "qryYadda" then
blah blah blah
end if
If you're going to be using several different recordsources, you can use a select/case structure.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Remember to reward helpful tips with the stars they deserve.
- Status
Similar threads
