Updating The Forms Recordset

  • Thread starter Thread starter ZRexRider
  • Start date Start date
Z

ZRexRider

Hi,

I have a form whose recordset is initially populated by a SQL statement
in the Me.Recordset.RecordSource.

I have a "Search" button that will allow the user to select various
criteria. Their selections are used to build a SQL statement which is
used to populate a variable record set (RS).

To change the contents of the form to these new results I do:

Set Me.Recordset = rs

This was working as expected but then I noticed I was getting blank
rows in my SQL server database. I made a number of fields "not
nullable" to force a failure when this "phantom new record write"
occurred. Turns out the error occurred on the line with Set
Me.Recordset = rs

So my questions are....
* why does replacing the form's recordset force an add of a new record?
* are there any suggestions for working around this?

Thanks
 
look at the code in the form for a DoCmd.GoToRecord acNewRec command.
This is sometimes done because people don't like the first record of a
recordset populating the form every time it is opened. So, when you change
the form's recordset, it first closes the old recordset which will cause the
current record to be saved.

I will not promise this is the problem, but it is worth looking into.
 
Thanks but I didn't find any explicit commands "DoCmd.GoToRecord
acNewRec". However, if there is a "new blank record" instantiated in
the form's record set - how do I prevent it from trying to write itself
to the DB when I want to replace the contents of the Form's
recordset....

I simply want to disregard the current contents of the Form's
recordset.

Thanks
 
Back
Top