IF Statement Based on Returned Records

[MagnusFox]

I have a form that has a button that on click runs a select query. The query results pop up in a new window.

Now I want to modify the button so that when the query runs and the query has results, it updates a field in my form. I also do not want the query results to display on the screen. All I want is to press the button and know if any data is returned in the query, then update a field.

Here is some code that logically does what I want, but does not really work:

If (DoCmd.OpenQuery(stDocName, acNormal, acEdit) = True) Then
Forms!Form1.[Field1] = 99
End If

I looked at the ReturnsRecords = TRUE code, but I do not understand how to use it.

Many Thanks

 

[rbowes]

I would do it using VB after the query is run from the click event call this routine:

Private sub MyRoutine()
Dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset(MyQuery, dbopensnapshot)

if rs.BOF or rs.eof then

Forms!Form1![Field 1] = 99
end if

set rs = nothing
set db = nothing
exit sub

End sub