what am i doing wrong

[Dave]

Hello

I am trying to return a count from a table into a variable. As you can see
from the code below I have the select statement counting the rows where, in
table1, invno is equal to 1. However when i run this code the messagebox is
blank ( i have tried dropping it into a text box but with the same result!).
If I run the Select line as a query though it gives me the correct count as
2. Help!!!

Thanks


Dim rst2 As ADODB.Recordset

Set rst2 = New ADODB.Recordset
With rst2
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open ("SELECT count(Table1.invno) as countofno FROM Table1 WHERE
(((Table1.invno) = 1))")
MsgBox (countofno)
End With

rst2.Close

 

[Dirk Goldgar]

Hello

I am trying to return a count from a table into a variable. As you
can see from the code below I have the select statement counting the
rows where, in table1, invno is equal to 1. However when i run this
code the messagebox is blank ( i have tried dropping it into a text
box but with the same result!). If I run the Select line as a query
though it gives me the correct count as
2. Help!!!

Thanks


Dim rst2 As ADODB.Recordset

Set rst2 = New ADODB.Recordset
With rst2
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open ("SELECT count(Table1.invno) as countofno FROM Table1
WHERE (((Table1.invno) = 1))")
MsgBox (countofno)
End With

rst2.Close

In the MsgBox statement, you're not qualifying "countofno" with the
recordset object, so you're just displaying an undeclared, uninitialized
variable with the same name as the field. You must not have Option
Explicit set for the module.

Change your line to

MsgBox .Fields("countofno")

or

MsgBox !countofno

 

[Dave]

if i want to use a number input from a field on my form rather than a
predefined one, how can i amend my sql line? I have tried SELECT
Count(Table1.invno) AS countofno
FROM Table1
WHERE (((Table1.invno)=[forms]![form1]![text0]))

and entered a figure into text0 and ran the query from a button. However i
get an error message saying that one of the parameters is not set. Any
ideas?

 

[Rick B]

One post please. You don't need three or four different people working on
this in three or four different groups.

 

[Dirk Goldgar]

if i want to use a number input from a field on my form rather than a
predefined one, how can i amend my sql line? I have tried SELECT
Count(Table1.invno) AS countofno
FROM Table1
WHERE (((Table1.invno)=[forms]![form1]![text0]))

and entered a figure into text0 and ran the query from a button.
However i get an error message saying that one of the parameters is
not set. Any ideas?

The control reference is a parameter in the query. If you run the query
from the Access user interface, Access will fill in that parameter for
you, but ADO doesn't know anything about the Access forms and controls,
so it views it as an unresolved parameter.

Since you're building the SQL string yourself, just build in the value
of the control:

.Open "SELECT count(Table1.invno) as countofno FROM Table1 " & _
"WHERE Table1.invno=" & Forms!Form1!Text0