Count number of reportnumbers in a project and auto-create next reportnumber (1 Viewer)

[markzaal]

I have one table (SafetyEvents) where users will register the amount of safety events per day. For each event, a reportnumber must be created that looks something like XXX-XXXXX-001.
In my table SafetyIACT (a particular event), to register the reportnumbers I have the following fields:

ReportID - autonumber
DPRDate - Date/time
Projectnumber - text
reportnumber (PK) - text
description - text

I want each new reportnumber to have some info (about the project XXX-XXXXX) and an automated number in the form of 3 digits, so 001 to 999 (I will never have more than 100 reports in a certain project, but just to be sure). This number depends on the projectnumber. So each time a new projectnumber is added, it should start counting from 001 and increase by 1 for each added reportnumber in that project.
I would like to put the necessary code in the "got focus" event of the "reportnumber" field so it fills in automatically, but can be changed when needed.
I would also like to count the number of reportnumbers on a certain DPRDate and have just this number to fill in the SafetyEvents form (which tells me the number of reports per day.

Any thoughts?

Thank you very much in advance!

Mark

 

[mdlueck]

You may use code along the lines of:

  Dim obj As AccessObject
  Dim strThisForm As String

  'Put the mouse hourglass pointer
  Call uiutils_SetMouseHourglass

  For Each obj In CurrentProject.AllForms
    strThisForm = obj.Name
    If strThisForm <> Me.Name Then
      'Not self, so edit away!
      DoCmd.OpenForm FormName:=strThisForm, _
                     View:=acDesign, _
                     WindowMode:=acIcon
      With Forms(strThisForm)
        .Filter = vbNullString
        .FilterOn = False
        .OrderBy = vbNullString
        .OrderByOn = False
        'This one allows data entry into unbound controls
        .AllowEdits = True
      End With
      DoCmd.Close ObjectType:=acForm, _
                  ObjectName:=strThisForm, _
                  Save:=acSaveYes
    End If
  Next obj

only enumerate through the CurrentProject.AllReports collection to find out the name of each report... to find out the highest used number, then +1 to obtain the next available number.

 

[markzaal]

Hi Michael,

The reportnumbers I talk about have nothing to do with reports in or created by Access. In case of a safety event our data admin needs to write a report about this, which has to have a number corresponding with the date, projectnumber and how many reports have been already made for this safety event.
Same goes for the projectnumber I talk about. So I don't really know what you mean by currentproject...
I guess your coding is a bit to advanced for my knowledge of VBA...

Mark

 

[mdlueck]

So I don't really know what you mean by currentproject...

I thought you were looking for a way to look for a way to increment "1 higher" than the highest number Report currently in the database. The code I shared enumerates through Forms making changes to each Form (other than self) in the database.

Likewise it would be possible to loop through all Report objects, making note of the highest number encountered, then take that result + 1 to arrive at the next available number.