Help with recordcount command

[genturo]

I am trying to count how many records has a table with a special value (SolicitaçãoID.Text) but I keep getting error:
Compile Erro:
User-Defined not defined

And it goes to this line: Dim db As DAO.Database

This is the command:

Private Sub CmdAmostra_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String

Set db = CurrentDb
SolicitaçãoID.SetFocus
strSQL = "SELECT COUNT (*) As RecordCount FROM Protocol where SolicitaçãoID=" & SolicitaçãoID.Text
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

intResult = rs("RecordCount")

MsgBox intResult

Amostras.SetFocus
Amostras.Text = intResult

rs.Close
db.Close

End Sub

Well i have no clue what that error means and why.
Thanks for the Help
Art

 

[whitesmoke]

here is the easiest method..
variable = DCount([ID FIELD ],"TABLE NAME ","SolicitaçãoID=" & SolicitaçãoID.Text)

you could even put this in a form

could even user form.recordsetclone.count (i think)

 

[pbaldy]

Make sure the Microsoft DAO 3.x reference is checked in Tools/References.

 

[genturo]

Make sure the Microsoft DAO 3.x reference is checked in Tools/References.

First thanks for the prompt answer.
Second, you will kill me but i have no Tools>references

 

[pbaldy]

Sorry, that's in the VBA editor, not Access.

 

[genturo]

Sorry, that's in the VBA editor, not Access.

Thaks now I am getting this error 3061 not enought parameters
on this line
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
.
THanks

 

[whitesmoke]

I think you lost the case... with the code you don't need your long code..


assign the code i gave you to your field Amostras.Text
..

 

[genturo]

I think you lost the case... with the code you don't need your long code..


assign the code i gave you to your field Amostras.Text
..

im going to try yours cuz is way simple (since i am not vb programmer, though i made it work , hehehe)
brb
THanks

 

[genturo]

I think you lost the case... with the code you don't need your long code..


assign the code i gave you to your field Amostras.Text
..

I go to Amostras.text
Control Source: variable = DCount([SolicitaçãoID_P ],"Protoloc ","SolicitaçãoID=" & SolicitaçãoID.Text)

and nothing happend, it doesnt even keep the code, it go back to the old value

 

[whitesmoke]

remove variable from the field just use

= DCount([SolicitaçãoID_P ],"Protoloc ","SolicitaçãoID=" & SolicitaçãoID.Text)


you may or may not have to put quote on the initial field

= DCount("[SolicitaçãoID_P ]","Protoloc ","SolicitaçãoID=" & SolicitaçãoID.Text)