Hilton

Making a small application to send possibly large numbers of emails to a user group. It has two forms


On the first form (Hubform) users use checkboxes to define records to be selected from an Access database. Command1 opens Contacts.mdb (ie CreateObject) and runs a query to delete table Contacts, the runs some SQL code which remakes the table (from table Contacts Data) containing the selected records. The database is closed and the form is hidden and the second form is shown.

The second form (Mailform) is used to compose an email message to be sent to the selected records. This form uses a data control and DBgrid to connect to the database.

Before the message is composed or sent, users can reveiw the records made on the previous form. Command button4 conatins the code to connect data1 and DBgrid1 to Contacts.mdb. Users can quite easily de-select records they don't wish to mail to. But the big problem is...

If the selection is wrong in some regard (eg user selected all records with USA in Country field instead of UK), then they need to be able to go back to Hubform and re-submit the query.

Command6 contains the following code, which i think is the problem.


KeyPreview = True
file = App.Path + "\contacts.mdb"
cr = Chr$(13) + Chr$(10)
Dim db As Database
Set db = OpenDatabase(file)
Set rs = db.OpenRecordset("select * from contacts")
Set Data1.Recordset = rs

rs.Close
db.Close

Text1.Text = ""
msg.Text = ""
Mailform.Hide
hubform.Show

It is supposed to close data1 (which i assumed would release the table fo use), clear the contents of the email subject and message the return the user to Hubform.

But when the query is resubmitted, the new selection is ADDED to the table. It seems as if the table is not released by Data1.

CAn anyone help?

iowahawk43

I assume you are binding your Grid to Data1 data control. This is why you get a recordset and then move that recordset to Data1.Recordset.

It seems a little odd you would create a Recordset object, fill it with records and then move it to the Data Control (other than the convenience of binding to the data grid).

Anyway, so you kill the Recordset object. And the Database object. Cool. What makes you think the Data1 is closed yet? Once you passed the records (Set Data1.Recordset = rs) the link between "rs" and "Data1.recordset" is over.

If you requeried using "rs" object your Data control would not automatically be updated too.

Try getting your new rs filled and then update your Data1 with the new results. Data1.Refresh might be helpful.

By best suggestion...1) start using ADO and 2) avoid the data control (more code, but more control).

Hope this helps.