DCOUNT in module

[ironwood9 via AccessMonster.com]

I have a module where I run an action query - here is the code:

CurrentDb.Execute "q_FIN_DLM_Misc_Append", dbFailOnError

It is an append query - but it should always have at least ONE record to
append to the destination table, which is called t_Main

Does anyone have an example of code I could add, probably with a DCOUNT
function and an IF / THEN statement to where perhaps a message would be
generated if there are no records being appended, because that shouldn't be -
I have several queries running from this one module, so if one "failed," I
would never know.

 

[John Spencer]

Try

Dim dbAny as DAO.Database
Dim LrecCount as Long

Set dbAny = CurrentDb()

dbAny.Execute "q_FIN_DLM_Misc_Append", dbFailOnError
LrecCount = dbAny.RecordsAffected

If LrecCount = 0 Then
'Do something here since there were no records appended
End If


You CANNOT use CurrentDB() since it "goes out of scope" immediately after
the line is executed. So the next time you use CurrentDB() it has no idea
of what it pointed to before.


--


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ironwood9 via AccessMonster.com]

John, Thanks for that !

You said >>You CANNOT use CurrentDB() since it "goes out of scope"
immediately after
the line is executed. So the next time you use CurrentDB() it has no idea
of what it pointed to before.>>

Could I use it if I only used it once in the procedure ?

Try

Dim dbAny as DAO.Database
Dim LrecCount as Long

Set dbAny = CurrentDb()

dbAny.Execute "q_FIN_DLM_Misc_Append", dbFailOnError
LrecCount = dbAny.RecordsAffected

If LrecCount = 0 Then
'Do something here since there were no records appended
End If

You CANNOT use CurrentDB() since it "goes out of scope" immediately after
the line is executed. So the next time you use CurrentDB() it has no idea
of what it pointed to before.

I have a module where I run an action query - here is the code:

[quoted text clipped - 9 lines]

I have several queries running from this one module, so if one "failed," I
would never know.

 

[John Spencer]

If you only use it once, who cares. But if you are trying to do the two
lines
CurrentDb().Execute "q_FIN_DLM_Misc_Append", dbFailOnError
LrecCount = Currentdb().RecordsAffected

You are going to use it twice. And if I recall correctly, the second line
would always return zero
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John, Thanks for that !

You said >>You CANNOT use CurrentDB() since it "goes out of scope"
immediately after
the line is executed. So the next time you use CurrentDB() it has no idea
of what it pointed to before.>>

Could I use it if I only used it once in the procedure ?

Try

Dim dbAny as DAO.Database
Dim LrecCount as Long

Set dbAny = CurrentDb()

dbAny.Execute "q_FIN_DLM_Misc_Append", dbFailOnError
LrecCount = dbAny.RecordsAffected

If LrecCount = 0 Then
'Do something here since there were no records appended
End If

You CANNOT use CurrentDB() since it "goes out of scope" immediately after
the line is executed. So the next time you use CurrentDB() it has no idea
of what it pointed to before.

I have a module where I run an action query - here is the code:

[quoted text clipped - 9 lines]

I have several queries running from this one module, so if one "failed,"
I
would never know.