changing recordsource

[slimjen]

All, using 2010. I have a 3 forms with the same fields. I have an unbound field to filter through the records so I can make notes. I would like to change the record source based of the command button I choose to change the recordsource because the forms are based on a query. This would prevent the user from having to go into and out of forms. The memo field is a subform so it should not change because each query has the same key fields to link the records. So far I have the following code:

Forms!frmCustomers.RecordSource = "Customers"

Something is missing. Please help. Thanks

[R_Badger]

I have used a similar system, on my forms as there are too many 'selection' options to make a static query viable.

I construct the SQL in VB and then assign it as the recordsource
For the command button

dim rsql as string

if commandbutton.caption="Caption for default" then
    rsql="SELECT * from tbl_example where Primary_ID is not null ORDER BY Primary_ID ASC"
    me.recordsource=rsql
    commandbutton.caption="Caption for alternative"

elseif commandbutton.caption="Caption for alternative"  then
rsql="SELECT * from tbl_example2 where Secondary_ID is not null ORDER BY Secondary_ID ASC"
me.recordsource=rsql
commandbutton.caption="Caption for default"
endif

In your case you may wish to switch the if criteria from a commandbutton caption to a list or combobox valuee, but hopefully it should point you in the right direction.