• Home

Sub btnBrowse_Click()

Sub btnBrowse_Click ()
'gconSTART_IND_BROWSNUM
'This routine goes through each record in table
'SINDIVID in order to delete the current entry
'in the Browse Number field and enter a new one
'that is sequential.  This eliminates the possibility
'of blank Browse Number fields or BN fields that are duplicates.

'Const BZ is the filler of preceding zeroes for making the browse number.
'Each browse number is actually a 4-character string, i.e., 0001, 0002, etc.
'This is the ANSI code for the zero (0) character.
    Const BZ = 48
'Const MC is the number of characters, or 4, to use for each browse number.
    Const MC = 4
    Dim WhelpDB As Database
    Dim WhelpSet As Recordset
    Dim UtilInt0 As Integer
    Dim UtilInt1 As Integer
    Dim RecordAmts As Integer
    Dim UtilStr0 As String
    Dim UtilStr1 As String
    Dim UtilVar0 As Variant

'Open a recordset from the Deperson table.
    Set WhelpDB = DBEngine.Workspaces(0).Databases(0)
    Set WhelpSet = WhelpDB.OpenRecordset("SINDIVID", DB_OPEN_DYNASET)

'Initialize RecordAmts to 1 to show progress,
'and Initialize UtilInt0 to first browse number
'to be used for individual records in this help file.
'NOTE: See global constants in the Module
    RecordAmts = 1
    UtilInt0 = gconSTART_IND_BROWSNUM

'Determine number of records so a status indicator can be provided.
    WhelpSet.MoveLast
    WhelpSet.MoveFirst
    UtilInt1 = WhelpSet.RecordCount
    UtilVar0 = SysCmd(SYSCMD_INITMETER, "Updating browse numbers...", UtilInt1)
    DoCmd Hourglass True

'Process each record's browse number field.
    Do Until WhelpSet.EOF
        UtilVar0 = SysCmd(SYSCMD_UPDATEMETER, RecordAmts)
        UtilStr0 = Format$(UtilInt0)
'Make string from current number.
        UtilInt1 = Len(UtilStr0)
'Make remainder of string consist of preceding zeroes.
        UtilStr1 = String$(MC - UtilInt1, BZ)
        UtilStr1 = UtilStr1 & UtilStr0
'Enter the value into the Browse Number field of the current record.
        WhelpSet.Edit
        WhelpSet.[Browse_Num] = UtilStr1
        WhelpSet.Update
'Update UtilInt0 and move to the next record.
        UtilInt0 = UtilInt0 + 1
        RecordAmts = RecordAmts + 1
        WhelpSet.MoveNext
    Loop

'Close open recordset and temporary database.
    WhelpSet.Close
    WhelpDB.Close
    RecordAmts = RecordAmts - 1

'Announce that task is finished.
    DoCmd Hourglass False
    UtilVar0 = SysCmd(SYSCMD_REMOVEMETER)
    UtilStr1 = "Done! There were " & RecordAmts & " records in SINDIVID updated "
    UtilStr1 = UtilStr1 & "with new Browse Numbers."
    MsgBox UtilStr1
    
Exit_btnBrowse_Click:
    Exit Sub

End Sub

• Home

See also:

• Differences Between Access Basic and VBA for Access 97