changing a form's Record Source through code

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

Please could anyone give me any suggestions about the
following as I can't seem to get anything I've tried to
work.

I'd like to change the Record Source (as given when you
look at the properties of a form) of a form from one table
to another using either a macro or VBA such that the user
can choose to look at the dataset in table one or the
dataset in table two as they wish, but through the same
form (rather than setting up duplicate forms and subforms
to look at the second table as this seems a bit
cumbersome).

I've looked in the Access Help files and an Access 2000
Developer's Handbook but can't seem to find anything about
automating this sort of change, is it possible?

There is a command word 'RecordSource' in VBA but I'm not
sure if that refers to quite the same thing as the 'Record
Source' as from the form properties...?
 
Sarah, you could reassign the form's RecordSource to "Table2" by placing a
command button on the form, and putting this into the Click event procedure:

Private Sub cmdChangeRecordSource_Click()
If Me.Dirty Then 'Save the record first.
Me.Dirty = False
End If
Me.RecordSource = "Table2"
End Sub

Because your form has text boxes bound to the fields of the table, this will
only work if both tables have the same structure (field names and field
types). The more important question is why you would have two tables with
the same structure. That's odd. Would you consider combining the 2 into one
table, and adding a field that can indicate whatever is the difference
between the two?
 
Hi Allen,

Many thanks for your reply!

The reason for setting this form up to work with two
tables is that within the database there will be a set
of 'definitive' tables each containing different
information about a number of sites, and this data will be
unalterable, however to allow people to play with the
data, test different scenarios etc. I've set something up
which will copy all data for a specific site from these
definitive tables into a single separate table for
editing, then after editing the user can either discard
all the changes or save the altered site info to a further
reference table as a record of what has been done. This
form will be swapping between the editable table and the
reference table. I appreciate that editing could be done
in the reference table itself, but it's been decided we
should do it this was as part of controlling the process
the user goes through in editing data and controlling site
version info etc. There may well be cleverer ways of doing
this in Access but I am only new to the software so am on
a learning curve!
 
Back
Top