some code

  • Thread starter Thread starter Pwyd
  • Start date Start date
P

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?
 
heh. it doesn't even return a true or false to tell the calling function if
it failed or not? damnit.
 
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.
 
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.


Pwyd said:
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.


J_Goddard via AccessMonster.com said:
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
Click to expand...
Click to expand...
 
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?


J_Goddard via AccessMonster.com said:
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"


J_Goddard said:
The close command should be:

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

without the brackets.

John
Let me amend that. it says:
Click to expand...
[quoted text clipped - 17 lines]
rst.movefirst
if rst![whatever field contains the count] = 0 then
Click to expand...
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
Click to expand...
 
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"


J_Goddard via AccessMonster.com said:
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"


J_Goddard said:
The close command should be:

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

without the brackets.

John
Let me amend that. it says:
Click to expand...
[quoted text clipped - 17 lines]
rst.movefirst
if rst![whatever field contains the count] = 0 then
Click to expand...
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
Click to expand...
 
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 :(



J_Goddard via AccessMonster.com said:
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:
Click to expand...
[quoted text clipped - 17 lines]
rst.movefirst
if rst![whatever field contains the count] = 0 then
Click to expand...
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
Click to expand...
 
all right i've got it. The SQL re-wrote my query when i closed it again as
"group by" instead of "Where"...


J_Goddard via AccessMonster.com said:
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:
Click to expand...
[quoted text clipped - 17 lines]
rst.movefirst
if rst![whatever field contains the count] = 0 then
Click to expand...
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
Click to expand...
 
Pwyd said:
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?
Click to expand...
<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
 
Back
Top