Holding Queue 
Author Message
 Holding Queue

Hi all,
I have a database with about 100 reports in it. I want to create a
centralized function which will only let a certain number of users to run a
report at one time. If the number of users is exceeded, then display a
message box stating that the report cannot be ran at this time, try later.
Someone in this group has advised me to store the names of the reports in a
separate table with the value (ex. 3, number of users to run the report at
the same time). Each time a report is opened, do the lookup in the table and
increment the value by one and every time the report stops running increase
the value by one.
I have a very limitted experience with coding VBA functions in access. Can
someone please direct me on how to write a function to achieve this.

Any help would be greatly appreciated,
Thanks to all.



Mon, 03 Jan 2005 22:37:20 GMT  
 Holding Queue

Quote:

> Hi all,
> I have a database with about 100 reports in it. I want to create a
> centralized function which will only let a certain number of users to run a
> report at one time. If the number of users is exceeded, then display a
> message box stating that the report cannot be ran at this time, try later.
> Someone in this group has advised me to store the names of the reports in a
> separate table with the value (ex. 3, number of users to run the report at
> the same time). Each time a report is opened, do the lookup in the table and
> increment the value by one and every time the report stops running increase
> the value by one.
> I have a very limitted experience with coding VBA functions in access. Can
> someone please direct me on how to write a function to achieve this.

> Any help would be greatly appreciated,
> Thanks to all.

This is air code. I hope I've got this right.  You may have to play with the
quote, single or double.
This logic does not address how to handle the report counts when a user ends the
Access application or when
the last user ends the application.  At some point you will need to reset the
counts or delete all of the records.
I'm not sure I understand why you want to do this.

------------------------------------------
Put this in the Module.
------------------------------------------
Dim dbs As Database
Dim SQLString as string
Dim nCount as integer

Function fReportCount( xReportName as string) by Boolean
Set dbs = OpenDatabase("MyDataBase.mdb")
fReportCount=True
If IsNull(DlookUp("[ReportName],"SomeTable", "ReportName=" & xReportName & ")"
then
    SQLString = "INSERT INTO SomeTable (ReportName,RptCount) VALUES ('" &
xReportName & "', 1 );"
    dbs.Execute SQLString
    dbs.Close
    fReportCount=False
Else
    nCount= DlookUp("[RptCount,"SomeTable", "ReportName='"_   & xReportName &
"')"
    IF nCount = 3 then
       fReportCount=True
    else
       nCount=nCount + 1
       SQLString = "UPDATE SomeTable SET SomeTable.RptCount =" & nCount
       SQLString = SQLString & " WHERE SomeTable.rptCurrentReport.Name=" & "'" &
xReportName &  "';"
       dbs.Execute SQLString
       dbs.Close
       fReportCount=False
    Endif
endif
End Function

Function FResetReportCount( xReportName as string) by Boolean)
FResetReportCount=True
Set dbs = OpenDatabase("MyDataBase.mdb")
if IsNull( DlookUp("[RptCount,"SomeTable", "ReportName='"  & xReportName & "')")
then
   nCount=0
Else
   nCount= DlookUp("[RptCount,"SomeTable", "ReportName='"  & xReportName & "')"
   nCount=nCount - 1
   if nCount < 0 then
      nCount=0
   endif
    SQLString = "UPDATE SomeTable SET SomeTable.RptCount =" & nCount
    SQLString = SQLString & " WHERE SomeTable.rptCurrentReport.Name=" & "'" &
xReportName &  "';"
    dbs.Execute SQLString
Endif
dbs.Close
End Function

------------------------------------------
In the General Declaration section of Each report.
------------------------------------------
Dim rptCurrentReport As Report
Dim lResult as boolean

------------------------------------------
In the Open event of the report.
------------------------------------------
Set rptCurrentReport = Screen.ActiveReport
lResult=fReportCount(rptCurrentReport.Name)
If lResult = true then
   display a message and exit the report
endif

------------------------------------------
In the Close event of the report.
------------------------------------------
Set rptCurrentReport = Screen.ActiveReport
if lResult=False then
   lResult=FResetReportCount(rptCurrentReport.Name)
Endif

Hope this helps.
Ron
--
Ronald W. Roberts
Roberts Communication



Tue, 04 Jan 2005 01:30:04 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Limit to maximum number of messages held by a queue?

2. Populate A Form While Mouse Is Held Down?

3. force input box and hold the variable

4. Holding a Date for multiple records.

5. Send 100 messages from [ Outbox\Hold ] Outlook 2000

6. Send 100 messages from [ Outbox\Hold ] Outlook 2000

7. Object Variable to hold my folder

8. ThisDocument Doesn't Hold Code?!?!

9. Get hold of shapes the header

10. Refering to Functions and Subs held in one global template from another global template

11. CommandBarComboBox to hold text and additional information

12. Can an arraylist hold arraylists?

 

 
Powered by phpBB® Forum Software