Getting count results froma query

I have a query which is called CountIP

SELECT count(*)
FROM attendance_tracking INNER JOIN attendance_code ON attendance_tracking.A_Code = attendance_code.A_Code
WHERE (((attendance_tracking.A_Code)="IP") AND ((attendance_tracking.PersonID)=CurrentUser()));


which obviously returns a value as an integer

However in my VBA code i try the following



Private Sub Report_Open(Cancel As Integer)
Dim IP, IPH, JD, NA, NAh, PB, PO, ULA, V, VH As Integer
IP = [CountIP]![Expr1000]
MsgBox "hello"
End Sub

however the report does not load or even atempt to load because it doe not like the code i have used, can anyone tell me the problem


Thanks
LVL 1
jon6Asked:
Who is Participating?
 
will_scarlet7Connect With a Mentor Commented:
Sorry, that line is un-needed. Try this:


    Dim IP, IPH, JD, NA, NAh, PB, PO, ULA, V, VH As Integer
    Dim cnn As Object
    Dim rst As Object

    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    Set cnn = CurrentProject.Connection

    rst.Open "SELECT Count(*) AS CountOfIP FROM attendance_tracking INNER JOIN attendance_code ON attendance_tracking.A_Code = attendance_code.A_Code WHERE (((attendance_tracking.A_Code)='IP') AND ((attendance_tracking.PersonID)=CurrentUser()))", cnn

    IP = rst!CountOfIP
    MsgBox "hello"
0
 
ZenMasterrrCommented:
maybe its Expr1000....... try

...... SELECT count(*) as CountOfIP....

IP = [CountIP]![CountOfIP]


Zen :))
0
 
jon6Author Commented:
Sorry that does not work either
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
will_scarlet7Commented:
Try this:

Private Sub Report_Open(Cancel As Integer)
    Dim IP, IPH, JD, NA, NAh, PB, PO, ULA, V, VH As Integer
    Dim cnn As Object
    Dim rst As Object

    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    rst.Open "SELECT * FROM CountIP", cnn

    IP = rst!Expr1000
    MsgBox "hello"

End Sub
0
 
will_scarlet7Commented:
Sorry... forgot the cleanup

Private Sub Report_Open(Cancel As Integer)
    Dim IP, IPH, JD, NA, NAh, PB, PO, ULA, V, VH As Integer
    Dim cnn As Object
    Dim rst As Object

    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    rst.Open "SELECT * FROM CountIP", cnn

    IP = rst!Expr1000

    rst.close
    Set cnn = Nothing
    Set rst = Nothing
    MsgBox "hello"

End Sub
0
 
jon6Author Commented:
It says user defined type not recognised regarding

Set rst = New ADODB.Recordset


I tried changing it to this because it did not work, but niether does this

 Dim IP, IPH, JD, NA, NAh, PB, PO, ULA, V, VH As Integer
    Dim cnn As Object
    Dim rst As Object

    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    rst.Open "SELECT Count(*) AS CountOfIP FROM attendance_tracking INNER JOIN attendance_code ON attendance_tracking.A_Code = attendance_code.A_Code WHERE (((attendance_tracking.A_Code)='IP') AND ((attendance_tracking.PersonID)=CurrentUser()))", cnn

    IP = rst!CountOfIP
    MsgBox "hello"

Thanks for your help and please keep it coming
0
 
jon6Author Commented:
That works thanks
0
 
will_scarlet7Commented:
Glad to help! ThanX for the points!

God bless!

Sam

PS. Don't forget the cleanup part after the msgbox:

    rst.close
    Set cnn = Nothing
    Set rst = Nothing
0
 
jon6Author Commented:
Thanks you

God BLess you too
0
 
jon6Author Commented:
One last thing, i have a label on the form called Label12 how can i put the information into that label

Thanks
0
 
will_scarlet7Commented:
I think this should work:

Me!Label12 = IP

insert it right after the "IP = rst!CountOfIP" line
0
 
jon6Author Commented:
For some reason it does not work. I have tried putting the name of the report and then the name of the label and still it does not work
0
 
will_scarlet7Commented:
Sorry I think that should be:

Me!Label12.Caption = IP
0
 
jon6Author Commented:
THanks that work

Cheer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.