some code

[Pwyd]

Function RecordCountZero()
If DoCmd.OpenQuery("CountOfUserExistingRecords") = 0 Then
DoCmd.Close ([Costpoint Reversals Processor])
DoCmd.OpenForm ([Costpoint Processor New Record])


End Function


what's invalid about this function? it keeps highlighting "OpenQuery" and
saying it needs a function or variable. CountOfUserExistingRecords is a
valid query name, with a single resulting row with a numerical value.
What's the problem here?

 

[J_Goddard via AccessMonster.com]

Hi -

Docmd.openquery is not a function which returns a value - it runs the command
OpenQuery.

To do this, you will need to open a recordset based on your query, and then
use the value of one of the query fields, something like this:

dim rst as recordset
set rst=currentdb.openrecordset("CountOfUserExistingRecords")
rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[Pwyd]

heh. it doesn't even return a true or false to tell the calling function if
it failed or not? damnit.

 

[Pwyd]

all right, now i have


Function RecordCountZero()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
rst.MoveFirst
If rst![whatever field contains the count] = 0 Then
DoCmd.Close(acForm,[Costpoint Reversals Processor],AcCloseSave)
DoCmd.OpenForm ([Costpoint Processor New Record]) '
End Function

and its telling me it needs an "=" at the end of the first command after the
truth clause.

 

[Pwyd]

Let me amend that. it says:


Function RecordCountZero()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
rst.MoveFirst
If rst![RecordNum] = 0 Then
DoCmd.Close (acForm,[Forms]![Costpoint Reversals Processor],acSavePrompt)
DoCmd.OpenForm ([Costpoint Processor New Record])
End If

End Function

it replies "syntax error" on the Close command, though it won't tell me what
precisely its missing or is out of order.

all right, now i have


Function RecordCountZero()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
rst.MoveFirst
If rst![whatever field contains the count] = 0 Then
DoCmd.Close(acForm,[Costpoint Reversals Processor],AcCloseSave)
DoCmd.OpenForm ([Costpoint Processor New Record]) '
End Function

and its telling me it needs an "=" at the end of the first command after the
truth clause.

Hi -

Docmd.openquery is not a function which returns a value - it runs the command
OpenQuery.

To do this, you will need to open a recordset based on your query, and then
use the value of one of the query fields, something like this:

dim rst as recordset
set rst=currentdb.openrecordset("CountOfUserExistingRecords")
rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[J_Goddard via AccessMonster.com]

Sorry - you've lost me there. What are you referring to when you say "return
a value of True or False"?

If you are referring to your RecordCountZero function, then you need a line
RecordCountZero = [some value]
in the function.

If you want the function to return True or False, you should make it type
Boolean:

Function RecordCountZero() as Boolean

then use
RecordCountZero = True
or
RecordCountZero = False

However, you might encounter timing problems since your code to open/close
forms is in RecordCountZero function.

You might want to modify the code that calls the function to:

If RecordCountZero = True then
DoCmd.Close ([Costpoint Reversals Processor])
DoCmd.OpenForm ([Costpoint Processor New Record])
else

 

[J_Goddard via AccessMonster.com]

The close command should be:

DoCmd.Close acForm,[Forms]![Costpoint Reversals Processor],acSavePrompt

without the brackets.

John

Let me amend that. it says:

Function RecordCountZero()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
rst.MoveFirst
If rst![RecordNum] = 0 Then
DoCmd.Close (acForm,[Forms]![Costpoint Reversals Processor],acSavePrompt)
DoCmd.OpenForm ([Costpoint Processor New Record])
End If

End Function

it replies "syntax error" on the Close command, though it won't tell me what
precisely its missing or is out of order.

all right, now i have

[quoted text clipped - 22 lines]

rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[J_Goddard via AccessMonster.com]

Sorry missed this - the close command should be:

DoCmd.Close acForm,"Costpoint Reversals Processor",acSavePrompt

and the open command should be:

DoCmd.OpenForm "Costpoint Processor New Record"

The close command should be:

DoCmd.Close acForm,[Forms]![Costpoint Reversals Processor],acSavePrompt

without the brackets.

John

Let me amend that. it says:

[quoted text clipped - 17 lines]

rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[Pwyd]

I never quite understand why some function calls require brackets and
arguments spaced within them and others do not. Does access pick and choose
which parts of visual basic it wants to ignore on a random basis?

Sorry missed this - the close command should be:

DoCmd.Close acForm,"Costpoint Reversals Processor",acSavePrompt

and the open command should be:

DoCmd.OpenForm "Costpoint Processor New Record"

The close command should be:

DoCmd.Close acForm,[Forms]![Costpoint Reversals Processor],acSavePrompt

without the brackets.

John

Let me amend that. it says:

[quoted text clipped - 17 lines]

rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[Pwyd]

Now we have:

Function RecordCountZero()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
rst.MoveFirst
If rst![RecordNum] = 0 Then
DoCmd.Close acForm, "Costpoint Reversals Processor", acSavePrompt
DoCmd.OpenForm "Costpoint Processor New Record"

End If

End Function


And its highlighting the set Rst = line (the whole line) saying "too few
parameters, expected 1"

Sorry missed this - the close command should be:

DoCmd.Close acForm,"Costpoint Reversals Processor",acSavePrompt

and the open command should be:

DoCmd.OpenForm "Costpoint Processor New Record"

The close command should be:

DoCmd.Close acForm,[Forms]![Costpoint Reversals Processor],acSavePrompt

without the brackets.

John

Let me amend that. it says:

[quoted text clipped - 17 lines]

rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[J_Goddard via AccessMonster.com]

That sounds like a problem with the CountOfUserExistingRecords query.

Does the query have a parameter in it, i.e. does it prompt the user for a
value?
Does the query run properly when you open it from the database window, or run
it in query design view?

John

Now we have:

Function RecordCountZero()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
rst.MoveFirst
If rst![RecordNum] = 0 Then
DoCmd.Close acForm, "Costpoint Reversals Processor", acSavePrompt
DoCmd.OpenForm "Costpoint Processor New Record"

End If

End Function

And its highlighting the set Rst = line (the whole line) saying "too few
parameters, expected 1"

Sorry missed this - the close command should be:

[quoted text clipped - 17 lines]

rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[Pwyd]

Its output is a single numerical field. I re-checked the query, its working
just fine.

Even with the query open and running properly, it still gives me the same
erroneous response

That sounds like a problem with the CountOfUserExistingRecords query.

Does the query have a parameter in it, i.e. does it prompt the user for a
value?
Does the query run properly when you open it from the database window, or run
it in query design view?

John

Now we have:

Function RecordCountZero()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
rst.MoveFirst
If rst![RecordNum] = 0 Then
DoCmd.Close acForm, "Costpoint Reversals Processor", acSavePrompt
DoCmd.OpenForm "Costpoint Processor New Record"

End If

End Function

And its highlighting the set Rst = line (the whole line) saying "too few
parameters, expected 1"

Sorry missed this - the close command should be:

[quoted text clipped - 17 lines]

rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[Pwyd]

all right i've got it. The SQL re-wrote my query when i closed it again as
"group by" instead of "Where"...

That sounds like a problem with the CountOfUserExistingRecords query.

Does the query have a parameter in it, i.e. does it prompt the user for a
value?
Does the query run properly when you open it from the database window, or run
it in query design view?

John

Now we have:

Function RecordCountZero()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("CountOfUserExistingRecords")
rst.MoveFirst
If rst![RecordNum] = 0 Then
DoCmd.Close acForm, "Costpoint Reversals Processor", acSavePrompt
DoCmd.OpenForm "Costpoint Processor New Record"

End If

End Function

And its highlighting the set Rst = line (the whole line) saying "too few
parameters, expected 1"

Sorry missed this - the close command should be:

[quoted text clipped - 17 lines]

rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[J_Goddard via AccessMonster.com]

Strange. Can you post the SQL of the query, please?

John

Its output is a single numerical field. I re-checked the query, its working
just fine.

Even with the query open and running properly, it still gives me the same
erroneous response

That sounds like a problem with the CountOfUserExistingRecords query.

[quoted text clipped - 27 lines]

rst.movefirst
if rst![whatever field contains the count] = 0 then

 

[Stuart McCall]

I never quite understand why some function calls require brackets and
arguments spaced within them and others do not. Does access pick and
choose
which parts of visual basic it wants to ignore on a random basis?

<snip>

To help you understand better, when a parameter to a function is declared as
an object (ie a form, report etc.), then is the time to use square brackets
if you have spaces in the object's name. When the parameter is declared as
as string, then you put the object's name in quotes. Examples:

When parameter is object, like: FunctionName(FormName As Form)
the syntax can be:
FunctionName(Forms!MyForm) '<-- Form name has no spaces
FunctionName(Forms![My Form]) '<-- Form name has spaces
FunctionName(Forms("MyForm")) '<-- Alternative syntax when spaces

When parameter is string, like: FunctionName(FormName As String)
the syntax is:
FunctionName("My Form")
this syntax for string values will handle object names containing spaces
too.

HTH