Continous SubForm requery problem

[TBernard]

I have a very strange problem with a continous subform.
The recordsource for the subform is just 1 table, which
makes this error that much stranger.

In SQL to get the autonumber (my ID field for the subform)
to populate you must leave the current record (opposed to
MS Access where as soon as you type on a new record the
autonumber field populates). Well on this continous
subform, I have a command button that opens up a pop-up
form that shows data related to the current record on the
subform. So I need this autonumber field to populate for
the pop-up form to work correctly. When I used the
following code:

Me.Requery

It sends the users back to the 1st record in the subform.
So if they are entering 20 records into the subform, after
the Me.Requery code, they would have to scroll back down
to get to the correct record.

I then tried this code:

If Me.Dirty Then
Me.Dirty = False
End If

Which worked great, but for certain records only I get
this following weird error:

Runtime error '30014':

The data was added to the database but the data won't be
displayed in the form because it doesn't satisfy the
criteria in the underlying record source.

I then click "Debug" and it goes to the code: Me.Dirty =
False.

If I continue to run the form, then the record appears
blank, but if I scroll to one record on the main form
back, and then come back to the current record on the main
form the subform record that caused this error is properly
populated. And everything saves correctly in the table.

I am at a loss for where to go, so if anyone has any ideas
it would be greatly appreciated.

Thank you,

TBernard

 

[Vadim Rapp]

T> If Me.Dirty Then
T> Me.Dirty = False
T> End If

I wonder what did you expect... dirty means that some data was modified in
the current record and not saved yet. If you want the data to stay, you have
to save the record; if you want to discard the changes, you have to undo.
It's the same as if you declared the laundry clean instead of washing it.

To have available the ID from the new record, I think the easiest way is:

create new record "manually', i.e. by separate ADO command, and return the
just credted ID:;
then requery and go to the record with the new ID. To make it smooth, use
Painting property of the form

painting=false
requery
move to the new record
painting=true


Vadim