Where is my code wrong?

N

Nova

My file is Access2003 contain of Table X (3 fields are ID,xx,yy)

This is my code in Form2

Private Sub Command15_Click()

Dim DBs As Database
Dim Rst As Recordset
Dim Strsql As String

Strsql = "Select xx from X where xx > forms!Form2.text0"
Set DBs = CurrentDb
Set Rst = DBs.OpenRecordset(Strsql, dbOpenDynaset, dbReadOnly)

Text13 = Rst.RecordCount

End Sub

when I click command15, I get message

Runtime error '3061'
Too few parameters Expected 1

and yellow hilight on Set Rst=.... Line

Where is it wrong
 
R

RoyVidar

Nova said:
My file is Access2003 contain of Table X (3 fields are ID,xx,yy)

This is my code in Form2

Private Sub Command15_Click()

Dim DBs As Database
Dim Rst As Recordset
Dim Strsql As String

Strsql = "Select xx from X where xx > forms!Form2.text0"
Set DBs = CurrentDb
Set Rst = DBs.OpenRecordset(Strsql, dbOpenDynaset, dbReadOnly)

Text13 = Rst.RecordCount

End Sub

when I click command15, I get message

Runtime error '3061'
Too few parameters Expected 1

and yellow hilight on Set Rst=.... Line

Where is it wrong

1 - I think I would recommend disambiguating the declarations, though
it has nothing to do with the 3061

Dim DBs As DAO.Database
Dim Rst As DAO.Recordset

2 - there's something wrong with the criterion, one thing is that
you're
feeding the name of the reference to the string, not the value.
For a numeric field

....where xx > " & forms!Form2!text0

should it be text

....where xx > '" & forms!Form2!text0 & "'"

should such text contain single quotes etc

....where xx > """ & forms!Form2!text0 & """"

it could also be that the name of your criterion field might be
mistyped.
 
N

Nova

I change code like you advise, no error message show but result not correct.
In table X I have 9 records, value is 1 to 9
In form2, text0 is 5
Result should be 4 (greater 5 is 6,7,8,9)
but result is 1
I try to change value of text0 to any value. Result is still 1
Where should I correct it?
 
R

RoyVidar

Nova said:
I change code like you advise, no error message show but result not
correct. In table X I have 9 records, value is 1 to 9
In form2, text0 is 5
Result should be 4 (greater 5 is 6,7,8,9)
but result is 1
I try to change value of text0 to any value. Result is still 1
Where should I correct it?

The DAO Recordcount will display the number of "accessed" records,
which means that to be sure that all records have been accessed, you'd
need for instance to issue a .movelast prior to fetching it. Some
air code

Set Rst = DBs.OpenRecordset(Strsql, dbOpenDynaset, dbReadOnly)
if ((not rs.bof) and (not rs.eof)) then
rst.movelast
Me!Text13 = Rst.RecordCount
else
Me!Text13 = 0
end if

If you're only after the count, you could in stead do

"SELECT Count(*) FROM X where xx >" & forms!Form2.text0
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top