Solved

Increase numeric value by one for each day

Posted on 2001-08-21
9
182 Views
Last Modified: 2006-11-17
I have a table:

ID            AutoNumber
Alpha         Text
BatchNumber   Number

Here is what I need to be able to do:

The BatchNumber needs to always start with 900 for each new day and as records are added the BatchNumber is increased by one.  

At the start of a new day, the BatchNumber needs to start again with 900.

Anyone got any suggestions?
0
Comment
Question by:gauton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
ID: 6409990
Don't use an autonumber.  Generate your own key for the record.  You do this with a table and a function like the one below.  At the start of each day, you'd reset the number to start at 900.  You could do that by saving the date/time you had a key out, then compare that to current day.  If different, reset the number.  Then get the key.

I can send a small sample MDB along.

Jim.

'
' Get a unique record key.
'
Function GetRecordKey(strTableName As String, strKeyParam As String) As Variant

  Const Routine = "GetRecordKey"
  Const Version = "1.0"
 
  Dim wrk As Workspace
  Dim dbCurrent As Database
  Dim dbRemote As Database
  Dim tdfAttached As TableDef
  Dim strPath As String
  Dim rst1 As Recordset
  Dim rst2 As Recordset
  Dim intlKeyValue As Long
  Dim lngWait As Long
  Dim lngX As Long
  Dim intLockCount As Integer
 
  GetRecordKey = Null

  On Error GoTo GetRecordKeyError

  Set dbCurrent = CurrentDb()

'
' First, get the key type.
'
  Set rst1 = dbCurrent.OpenRecordset("qrysysTableKeys", dbOpenDynaset)
  rst1.FindFirst "[TableName]= '" & strTableName & "'"
 
  If rst1.NoMatch Then
    ' Not good.  Need an entry for the table to generate a key.
    gstrMBTitle = "Generate key error"
    gstrMBMsg = "Can't generate key.  No entry in key table"
    gintMBDef = MB_OK + MB_ICONSTOP
    gintMBBeep = True
    gintMBLog = True
    Call DisplayMsgBox
    GoTo GetRecordKeyExit
  End If

'
' Now Generate a key based on the key type
'
GetAKey:
    Select Case rst1![KeyType]
   
    Case 1
    ' Key is simple numeric counter (ie xxxxxxx).
      rst1.Edit
      intlKeyValue = CLng(rst1![LastKeyValue]) + 1
      If intlKeyValue > rst1![MaximumValue] Then intlKeyValue = 1
      rst1![LastKeyValue] = Format$(intlKeyValue)
      rst1.Update
      GetRecordKey = intlKeyValue

    'Case 2
    ' Key is a base + a numeric counter (ie.  ABAxxx)
     

    Case Else
      ' Undefined key type
      gstrMBTitle = "Undefined call"
      gstrMBMsg = "Can't generate key.  Invalid key type."
      gintMBDef = MB_OK + MB_ICONSTOP
      gintMBBeep = True
      gintMBLog = True
      Call DisplayMsgBox
      GoTo GetRecordKeyExit
    End Select

    '
    ' Got a key.  Does it need to be tested for unique?
    '
    If (rst1![UniqueKey]) Then
        If dbRemote Is Nothing Then
            Set wrk = DBEngine.Workspaces(0)
            Set dbCurrent = wrk.Databases(0)
            Set tdfAttached = dbCurrent.TableDefs(strTableName)
            strPath = tdfAttached.Connect
            strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
            Set dbRemote = wrk.OpenDatabase(strPath, False, True)
        End If
       
        Set rst2 = dbRemote.OpenRecordset(strTableName, DB_OPEN_TABLE)
        rst2.index = "PrimaryKey"
        rst2.Seek "=", intlKeyValue
        If Not (rst2.NoMatch) Then
            rst2.Close
            GoTo GetAKey
        Else
            rst2.Close
        End If
    End If
     
GetRecordKeyExit:
    Set dbCurrent = Nothing
   
    If Not rst1 Is Nothing Then
      rst1.Close
      Set rst1 = Nothing
    End If
   
    If Not rst1 Is Nothing Then
      rst2.Close
      Set rst2 = Nothing
    End If
   
    If Not dbRemote Is Nothing Then
      dbRemote.Close
      Set dbRemote = Nothing
    End If
   
    Exit Function

GetRecordKeyError:
  'Table locked by another user
    If Err = CNT_ERR_RESERVED Or Err = CNT_ERR_COULDNT_UPDATE Or Err = CNT_ERR_OTHER Then
        intLockCount = intLockCount + 1
        If intLockCount > 5 Then
            GetRecordKey = Null
            Resume GetRecordKeyExit
        Else
            DoEvents
            DBEngine.Idle DB_FREELOCKS
            lngWait = intLockCount ^ 2 * Int(Rnd * 20 + 5)
            For lngX = 1 To lngWait
                DoEvents
            Next lngX
            Resume
        End If
    Else
      UnexpectedError ModuleName, Routine, Version, Err, Error$
      GetRecordKey = Null
      Resume GetRecordKeyExit
    End If

End Function
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6410004
hi gauton,

here is an example:
(first, create a new field in your table --> "DateCreated" = Date field, default value = Date()  )
in this example the name of the table = tblYourTable (change that according to your real name)

Private Sub cmdNewRecord_Click()
DoCmd.GoToRecord , , acNewRec
CreateBatch
End Sub

Private Sub CreateBatch()
Dim varMax As Variant
Dim varMaxDate As Variant

    If DCount("ID", "tblYourTable") = 0 Then
        'This is the first Record in the table
        Me.Batchnumber = 900
    Else
       
        If Date > DMax("DateCreated", "tblyourTable") Then
            'New date
            Me.Batchnumber = 900
        Else
            'Find highest date
            varMaxDate = DMax("datecreated", "tblYourtable")
            'find highest number for this date
            Me.Batchnumber = DMax("batchnumber", "tblyourtable", "datecreated = #" & Format(varMaxDate, "dd m yyyy") & "#") + 1
        End If
       
    End If
End Sub


cheers
Ricky
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6410019
btw, i used  

format(varmaxdate, "dd m yyyy")  b/c i am in europe

Me.Batchnumber = DMax ("batchnumber", "tblyourtable", "datecreated = #" & Format(varMaxDate, "dd m yyyy") & "#") + 1
       

if u are in the US, just use:

Me.Batchnumber = DMax "batchnumber", "tblyourtable", "datecreated = #" & varMaxDate & "#") + 1
       


djeez Jim, u type very very fast....  :-)

cheers
Ricky
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 58
ID: 6410045
Ricky,

  Not as fast as you think<g>.  And I really hate to poke a hole in your solution, but when using DMax, always remind people that it will not work in a multi-user situation.  It's possible for two (or more!) users to get the same key value.

Jim.
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6410129
True Jim,

something i learned using Cobol,

retrieve data,
check data,
user adjusts data,
retrieve data, compare, if necessary adjust automatic or warning,
lock data,
write data


(or something in that fashion...)
;-)
cheers
Ricky
0
 

Author Comment

by:gauton
ID: 6410218
Jim,

The small sample MDB would be great.

Garland
0
 

Author Comment

by:gauton
ID: 6410286
Jim,

The small sample MDB would be great.

Garland
0
 
LVL 58
ID: 6410878
need an e-mail address.

Jim.
0
 
LVL 58
ID: 6410883
If you don't want to post it, send an e-mail to jimdettman@earthlink.net

Jim.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question