Counting records to diplay an error message.

[JonnyB]

Hi all,

There are three tables in my database user, job and booking. It’s a job booking system so users login and make a booking against a job. I’m trying to get a button to display an error message if they try to make a booking but already have made 15 or more.

Below is the code I’ve got so far can anyone help me to get it working or point me in the right direction.

Private Sub btnEngCreateBooking_Click()
 
Dim username As String
Dim strSQL As String
 
strSQL = "SELECT Count(*) AS numberOfBookings FROM tblBooking WHERE userId = username AND jobStatus = open "
DoCmd.RunSQL (strSQL)
 
 
If numberOfBookings <= 15 Then
   MsgBox "You have 15 or more open bookings, please close bookings before proceeding"
 
 
DoCmd.OpenForm "frmEngCreateBooking"
 
 
End Sub

Cheers Jonny

[pks00]

u cannot use RunSQL as this is for action queries, instead use a recordset or a domain function

eg domain function - DCOUNT

DCOUNT("*","tblBooking","userid = username AND jobStatus = open")

example above takes your filter, I assume u modify it accordingly. It returns the number of bookings

the recordset example is like this


Dim username As String
Dim strSQL As String
Dim rs as dao.recordset
Dim lCount as long


strSQL = "SELECT Count(*) AS numberOfBookings FROM tblBooking WHERE userId = username AND jobStatus = open "
set rs=currentdb.openrecordset (strSQL)
lCount = 0
if rs.eof = false then
rs.movelast
lCount = rs.RecordCount
endif
rs.close
set rs=nothing


If lCount <= 15 Then
MsgBox "You have 15 or more open bookings, please close bookings before proceeding"

[hariharanmca]

u cannot use RunSQL as this is for action queries, instead use a recordset or a domain function

eg domain function - DCOUNT

DCOUNT("*","tblBooking","userid = username AND jobStatus = open")

example above takes your filter, I assume u modify it accordingly. It returns the number of bookings

the recordset example is like this


Dim username As String
Dim strSQL As String
Dim rs as dao.recordset
Dim lCount as long


strSQL = "SELECT Count(*) AS numberOfBookings FROM tblBooking WHERE userId = username AND jobStatus = open "
set rs=currentdb.openrecordset (strSQL)
lCount = 0
if rs.eof = false then
rs.movelast
lCount = rs.RecordCount
endif
rs.close
set rs=nothing


If lCount <= 15 Then
MsgBox "You have 15 or more open bookings, please close bookings before proceeding"

i thinh in the above Qry there is no use of count(*)

[hariharanmca]

u cannot use RunSQL as this is for action queries, instead use a recordset or a domain function

eg domain function - DCOUNT

DCOUNT("*","tblBooking","userid = username AND jobStatus = open")

example above takes your filter, I assume u modify it accordingly. It returns the number of bookings

the recordset example is like this


Dim username As String
Dim strSQL As String
Dim rs as dao.recordset
Dim lCount as long


strSQL = "SELECT Count(*) AS numberOfBookings FROM tblBooking WHERE userId = username AND jobStatus = open "
set rs=currentdb.openrecordset (strSQL)
lCount = 0
if rs.eof = false then
rs.movelast
lCount = rs.RecordCount
endif
rs.close
set rs=nothing


If lCount <= 15 Then
MsgBox "You have 15 or more open bookings, please close bookings before proceeding"

it will select only one record. if you give

lCount = rs.RecordCount

then the value will be 1

beter you can use like this
lCount = rs.field("numberOfBookings")

[JonnyB]

Thanks pks00.

I tried the recordset option but got a compile error fo this line

set rs=currentdb.openrecordset (strSQL)

saying "Too few parameters. Expected three"

Any ideas

[hariharanmca]

Thanks pks00.

I tried the recordset option but got a compile error fo this line

set rs=currentdb.openrecordset (strSQL)

saying "Too few parameters. Expected three"

Any ideas

just reply the qry you used....

[JonnyB]

Hi hariharanmca,

No to sure what you were meaning by that i'm guessing you ment us my sql statement not the one done by pks00.

I tried that but i'm still having problems.

I've uploaded the access file if that would help http://www.celestepoker.com/Temp/jr_system.mdb

username = u1
password = 00

Then click create booking.

Thanks.

[GRDev]

I think you need to check query

strSQL = "SELECT Count(*) AS numberOfBookings FROM tblBooking WHERE (((tblBooking.userID)='u1') AND ((tblBooking.bookingStatus)='Active')) "

[JonnyB]

Cheers GRDev,

Thats stopped the errors but its not quite working.

The error message seems to appear even if I have less than 15 records in the booking table.

I'm sure its nearly there, I think its the line in bold i've tried the other methods for this line above but they don't seem to fix it either. Any ideas, my codes:-

Private Sub btnEngCreateBooking_Click()


Dim username As String
username = Nz(Forms!frmLogin!txtMembername, "")

Dim strSQL As String
Dim rs As DAO.Recordset
Dim lCount As Long


strSQL = "SELECT Count(*) AS numberOfBookings FROM tblBooking WHERE (((tblBooking.userID)='username') AND ((tblBooking.bookingStatus)='Active')) "
Set rs = CurrentDb.OpenRecordset(strSQL)
lCount = 0
If rs.EOF = False Then
rs.MoveLast
lCount = rs.RecordCount
End If
rs.Close
Set rs = Nothing


If lCount <= 15 Then
MsgBox "You have 15 or more open bookings, please close bookings before proceeding"

Else

DoCmd.OpenForm "frmEngCreateBooking"

End If


End Sub

[pks00]

Hi

Right, apologies, I mixed up the usage with the sql regarding recordset

Firstly, I didnt change the sql, I left it as u initially did it

secondly, the dcount option returns the number of records based on your filter, given now that I know your table and how the data should be, u can use DCOUNT

here is the code again which gets the count
Note the use of recordset, I used a column alias (Cnt), then referenced that

    Dim lCnt As Long
    Dim sUser As String
 
 
    sUser = "u1"
 
 
'METHOD 1
    lCnt = DCount("*", "tblBooking", "userID = '" & sUser & "' AND bookingStatus = 'Active'")
 
    Debug.Print "METHOD 1 - DCOUNT"
    Debug.Print lCnt
 
 
'METHOD 2
    Dim rs As DAO.Recordset
    Dim sSql As String
 
    lCnt = 0
    sSql = "SELECT Count(*) AS Cnt FROM tblBooking WHERE userID = '" & sUser & "' AND bookingStatus = 'Active'"
    Set rs = CurrentDb.OpenRecordset(sSql)
    If rs.EOF = False Then
        lCnt = rs!Cnt
    End If
    rs.Close
    Set rs = Nothing
 
    Debug.Print "METHOD 2 - RECORDSET"
    Debug.Print lCnt
 

The value of the count is in lCnt

[JonnyB]

Worked perfectly, thankyou for all your help with this.

[NeoPa]

Nice work PKS.
Long time no see.

[pks00]

Howdo NeoPa

How are you
Yea, been busy with other things, cant spend as much time as I would like. Im occasionally popping in now when I get emails from Mary asking for help.
I must try to find more time to spend here!!!

[pks00]

Worked perfectly, thankyou for all your help with this.

Hi JonnyB, Im glad its worked fine for you, main thing is, do u understand the two solutions provided? DCOUNT is quick and easy to use but u cant do it where u have multiple tables/queries to deal with, hence the 2nd example and use of recordsets.

[JonnyB]

Yeah thanks, I was only querying the one table so got away with using DCount.

[NeoPa]

Strictly speaking (I should have been a school-marm - picture stern old lady wagging admonishing finger at young child) it is possible to do it on complex queries with multiple tables as long as you use a QueryDef as the record source. SQL code won't work.

Domain Aggregate functions (of which DCount() is just one) are fine for simgle requests, but can add a very heavy overhead if called from within SQL or QueryDefs. In this case you needn't worry though :)