non-autonumber records 2

[swtrader]

I want to number the records on a form thru an OnClick event, first checking to see if the field is null. If so, 1 is added to the Max number until end of recordset.

Thanks for your help.



swtrader
-- The trouble with doing something right the first time is that no one appreciates how hard it was.

 

[CaptainD]

If it's a single user program that can work but what if multiple people use the program? There is the potential for dublicate "ID" entries.

 

[swtrader]

Sorry. Didn't make myself clear. The field I want to number will be part of a composite key/index so no dupes allowed. Other part will be unique customer id. So, will end up with 1-1, 1-2, 2-1, 2-2, etc.

swtrader
-- The trouble with doing something right the first time is that no one appreciates how hard it was.

 

[Zion7]

Private Sub cmdIndex_Click()
Dim rec As DAO.Recordset
set rec = Me.RecordsetClone

rec.MoveFirst

Do Until rec.EOF
If IsNull(rec!txtIndex) Then
rec!txtIndex = DMax("txtIndex",'thisTable", _
"pkID =" & Me!pkID)+ 1
rec.Update
End If
rec.MoveNext
Loop

rec.Close: set rec = Nada


End Sub

 

[ItIsHardToProgram]

Zion does that not leave the problem of multiple user creating potential duplicates?

 

[Zion7]

IIHTP, yes it does.
I should have stipulated, this was just an idea,
at least to address the initial problem.

But, there will definately be writing conflicts,
if 2 or more users attempt to run the same procedure,
simultaneously, Unique index or not.

 

[swtrader]

Thanks, Zion7. That's a great start. I'll give it a go. Since it's an 'assigned' task, there should not be a 'multiple' user issue.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[ItIsHardToProgram]

swtrader.... I strongly advise, has Zion7 did, not to use the code. not only could it create duplicates it is a very odd way to list table rows......... If I was you I would go with autonumber, there is a reason why they were implented in access...

 

[Zion7]

iSHTP, at the time, I said, proceed with caution.
But Since swtrader expressed the circumstances, under which he will be using the code,
I believe now that, it is a very viable option.
And, I don't see the oddity of listing table rows as such???

SWT, slight modification...

Private Sub cmdIndex_Click()
Dim rec As DAO.Recordset
Dim intIndex As Integer
set rec = Me.RecordsetClone

rec.MoveFirst

Do Until rec.EOF
If IsNull(rec!txtIndex) Then
intIndex = DMax("txtIndex",'thisTable", _
"pkID =" & Me!pkID)+ 1
rec!txtUnique = rec!CustomerID & intIndex
rec.Update
End If
rec.MoveNext
Loop

rec.Close: set rec = Nada


End Sub

 

[Golom]

If you have real paranoia about duplicates then here's some code that addresses that issue

Public Function GetSaleNo() As Long
Dim SN                          As DAO.Recordset
Dim TM                          As Double
Dim StartAt                     As Date

StartAt = Now

On Error GoTo CantLock
Restart:
Set SN = dbControl.OpenRecordset("SALENUMB", dbOpenTable, dbDenyWrite Or dbDenyRead)

With SN
    If .EOF Then
        GetSaleNo = 1
        .AddNew
        ![Sale_No] = 2
        .Update
    Else
        GetSaleNo = ![Sale_No]
        .Edit
        ![Sale_No] = GetSaleNo + 1
        .Update
    End If

    .Close
End With

Set SN = Nothing
Exit Function

CantLock:
Select Case Err.Number

    Case 3008, 3009, 3187, 3189, 3211, 3212, 3262
        ' Errors mean that exclusive use of the recordset did not work.
        ' Wait for half a second and then try again.
        ' 3008 - The table <name> is already opened exclusively by another user
        ' 3009 - You tried to lock table <table> while opening it, but the table can't be locked because it is currently in use.
        ' 3187 - Couldn't read; currently locked by user <name> on machine <name>.
        ' 3189 - Table <name> is exclusively locked by user <name> on machine <name>.
        ' 3211 - couldn't lock table <name> because it's already in use by another person or process.
        ' 3212 - Couldn't lock table <name>; currently in use by user <name> on machine <name>.
        ' 3262 - Couldn't lock table <name>; currently in use by user <name> on machine <name>.
        TM = Timer + 0.5
        Do Until Timer >= TM
        Loop
        If DateDiff("s", StartAt, Now) > 10 Then
            Dim Ans             As Integer
            Ans = TimedMessageBox(Err.Description & vbCrLf & vbCrLf & _
                                  "The database containing the Sale Number " & _
                                  "has been locked for over 10 seconds." & vbCrLf & vbCrLf & _
                                  "Continue Trying?", vbCritical + vbYesNo, "Locked Database")

            If Ans = vbNo Then
                Dim db          As DAO.Database
                Dim Frm         As Form

                For Each Frm In Forms
                    If Frm.Name <> "SaleMain" Then
                        Unload Frm
                    End If
                Next
                If Forms.Count > 0 Then
                    ForceShutDown = True
                    Unload SaleMain
                Else
                    For Each db In DAO.DBEngine(0).Databases
                        db.Close
                    Next
                End If

                End
            Else
                StartAt = Now
            End If
        End If
        Resume

    Case 3046, 3186
        ' 3046 - Couldn't save because another user has the page locked.
        ' 3186 - Couldn't save; currently locked by user <name> on machine <name>.
        TM = Timer + 0.5
        Do Until Timer >= TM
        Loop
        Resume

    Case 3197
        ' 3197 - you and another user are attempting to change the same data at the same time.
        TM = Timer + 0.5
        Do Until Timer >= TM
        Loop
        Resume Restart

    Case Else
        Set SN = Nothing
End Select

End Function

As should be evident ... guaranteeing that you are not creating duplicates is a bit complex.

 

[ItIsHardToProgram]

Very nice code, that diserves something.

 

[swtrader]

Thanks, Golom. Useful for many (as you know). Commented: 'Developed by Golom - Tek-tips forum - 2006

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[Zion7]

Great initiative Golom!!!

 

[Zion7]

...Forgot to mention Golom,
you may consider posting your code as a FAQ?
(excuse me, if it's already there?)

 

[ItIsHardToProgram]

It most certainly was in his library

 

[Zion7]

IIHTP,
do you know what a Recordsetclone Is, or a FAQ????
...or at least tell me where Golom has this posted elsewhere?

 

[swtrader]

Try:

http://learning.londonmet.ac.uk/CCTM/im212/week4/recordsetclone.htm

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[ItIsHardToProgram]

What I meant is that he probably had it in his own library, with a bunch of other codes (he did it earlier in his life...)

 

[MichaelRed]

Already some faq / code re autonumber. Includes multiuser considerations and permits (demonstratyes) the assignment of value based on criteria and adding an incremental part.

search the faqs keyword ~~ autonumber

MichaelRed

 

[swtrader]

At the risk of wearing out my welcome, the following
works great, but I need the new doc_no's to start with
1 + the Max Doc_no -- from the appended-to-and-final [tbl301_docs] (so user can add docs after the first set of numbered docs have been
appended to tbl301 from tbl302 -- and user realizes they need to add a few more). Any other foreseen problems?

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM tbl302_DocTmpHold"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

rs.MoveFirst

Do Until rs.EOF

If (rs!doc_no) = 0 Then

'The MAX doc_no needs to be the max doc number in tbl301
'not tbl302 -- tbl302 doc_no will be updated from 0 incrementally
'using parameter "Cust_No = NNNN"

rs!doc_no = DMax("doc_no", "tbl302_DocTmpHold") + 1
rs.Update
End If
rs.MoveNext
Loop

'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
rs.Close

Set rs = Nothing
Set cn = Nothing
End Sub



swtrader
-- If you don't know where you're going, you'll always know when you're not there.