Adding Records to table not related to Opened Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeZz - 17 Mar 2005 19:57 GMT

Hi,
I have a form with an underlieing table.
On the form I have a button with some code that does things to the table in
the open form.

In that code, I also want to manipulate data in a table that doesn't have
anything to do with the open form or underlieing recordset.

I know this is probably basic, but how do I open another table so that I can
add new records to that table when I'm done adding records to the Form's
Table?

I guess my problem is how do I open a table and tell VBA that that table is
the recordset that I want to be updating?

Here is the basic code I have for updating the Form's Table:

>>>>Dim db As DAO.Database
>>>>Dim rst As DAO.Recordset
>>>>Set db = CurrentDb
>>>>Set rst = Me.RecordsetClone
>>>>For each vid in Me.lstVehicle.ItemsSelected
>>>> rst.AddNew
>>>> rst.Fields(0) = vid
>>>> rst.Update
>>>>Next
>>>>rst.Close
>>>>Set rst = Nothing
>>>>Set db = Nothing

What comes next to open a new table and set that table as the recordset to
update?

Thanks
Mike Zz

Reply to this Message

Chaim - 17 Mar 2005 20:59 GMT

Since you have closed the cloned rst recordset, you should be able to open it
on the new table as:

set rst = db.OpenRecordset (<new source>)

Your new source is either a table name, query name, or an SQL statement. The
other parameters are optional; add to suit your taste.

Now you should be able to work with that table through this recordset. If
you're concerned you can always declare another Recordset object as:
Dim rstOther as DAO.Recordset

and then open that one. Allthough, once you've closed the first and set it
to nothing, it is free to be reused, presumably within your procedure. Also,
if you don't set db to nothing, it can continue to be used, assuming this new
table is in the same database.

> Hi,
> I have a form with an underlieing table.
[quoted text clipped - 30 lines]
> Thanks
> Mike Zz

Reply to this Message