Control a Query Datasheet with Code

[webunit]

I have a select query which filters records from a table and presents the
results in a datasheet. Certain fields in the datasheet may be modified and
the table is updated when the datasheet is closed. I would like to add some
code that will check the data upon closing the datasheet. I need some
guidance on how to do this. I know a little about recordsets but only
recordsets that I create not ones generated by a query (I presume the
datasheet is a recordset). Any guidance would be appreciated. Thanks.

 

[Tom van Stiphout]

On Wed, 2 Sep 2009 15:46:02 -0700, webunit

You wrote: "Certain fields in the datasheet may be modified and the
table is updated when the datasheet is closed."
That's not how datasheets work. Rather the record (row) is updated to
the underlying table as soon as the user explicitly saves it or moves
to another row.
If you want to check things before the save, you need to write code in
the BeforeUpdate event, and set "Cancel = True" if you don't like what
you see.
No need to use recordsets. Something like this suffices:
private sub Form_BeforeUpdate(Cancel as Boolean)
if Me.FirstName = "Tom" then
Msgbox "FirstName cannot be Tom!"
Cancel = True
end if

-Tom.
Microsoft Access MVP

 

[webunit]

Yes. Thanks for the correction. The problem I had was I wasn't using a
form, I was opening the query in datasheet view. Since then I've learned how
to use a form with the query as the record source and now I can write code
for everything on the form (and the form itselft, etc.), so I think I can
figure it out from here. But I'm still curious if you can write code to
say, check for something after (or better yet just before) the user moves to
another row (before the computer actually makes the move) for queries that
are opened in the datasheet view (no form, looks like a spreadsheet). Feel
free to correct me if I'm not describing things right.