DoCmd in txt bx AfterUpdate event

  • Thread starter Thread starter Krisse
  • Start date Start date
K

Krisse

I need to run a query that may or may not return any
rows. If it does return rows, I want to undo the row that
is being entered edited. As a start, I did the following,
but it dies on the DoCmd. The query name is definitely
valid. I tried to put that query in a macro and
DoCmd.RunMacro (MacroAction) but that failed, too.

Private Sub BeginTime_AfterUpdate()
DoCmd.OpenQuery (qryFindConflicts)

'Here I need code to check the record count of the query
and if > 0 then I will execute the undo command below.
Help with how to write that if statement would be
appreciated!

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,
acMenuVer70
End Sub

Thanks!
 
I assume that qryFindConflicts is a select query. DoCmd.OpenQuery is used
only for running action queries (append, delete, update, etc.).

What you appear to need is the DCount function. This function allows you to
determine the number of records in a query or table. Check it out in Help
files, and post back if you need more assistance on setting up the
expression.
 
I discovered the Visual Basic Editor and VBA help! I
changed my DoCmd statement to:

DoCmd.OpenQuery "qryFindConflicts", , acReadOnly

It successfully opened the query but then it died on the
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,
acMenuVer70 saying undo was not available at that time.

Any ideas?

Thanks!
 
Hi,

I think instead of opening the query, you could either
use ADO (preferable, but probably too complicated for
this task) or use a domain aggregate function such as
DLookUp or DCount.

For example, you could write...

If DLookUp("[any field]","[name of query]") Then
'Undo code...
If Me.Dirty Then
RunCommand acCmdUndo
End If
End If

You need to check to make sure that the record has been
changed (using the "Dirty" property) because if it
hasn't, then the undo command won't be available.
RunCommand replaces the old commands to access the
toolbar used in older Access versions.
 
Back
Top