Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 26th, 2004, 11:32 AM
Registered User
 
Join Date: Jun 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Urgent please help to find Max ID and add 1 to it

Hello all,
Urgent please help. I have an MS Access table, "tbl_Final_Log" and my PK, "FinalLotNum" is a custom Unique Identifier Number (UID) which format like: 63MYDDNN (for example: 63FD2601, 63FD2602…, 63FD2699)

It must start with 63 then
the Month (A=1 for January, F=6 for June ...)
then Y for Year (A=1 for 2001, D=4 in 2004 ...)
then DD for Today Date, (like 26th day of the month, and
NN is the sequence number of the entry. The NN can only go from 01 to 99.

I need to create a command bottom to find the MaxID in the field “FinalLotNum” and
add 1 to it when the user click on the "cmdNextLotNum". The output will go in the "txtFinalLotNum" text box.
If the "txtFinalLotNum" text box is not null then some error message and do not thing else FinalLotNum = MaxID + 1.

Urgent please help.
Thank you

Vandat
 
Old June 26th, 2004, 04:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Vandat,

If I understand you correctly, you're looking for a way to get your next MAX sequence number (1 – 90) appended to the rest of your UID string. Hope that’s in the ballpark.

If so, I’d suggest first creating a table with a single field to store the current MAX value.

Table: tblMaxValue
-------------------
    Field: MaxValue
    DataType: Number
    Default Value: 0

Then here’s a function you can call to increment the MaxValue field, and return the new MaxValue.

Function GetNextMaxValue() As Integer
    On Error GoTo ErrorHandler

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim intNextMaxValue As Integer

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    rst.Open "tblMaxValue", cnn, adOpenKeyset, adLockPessimistic

    ' increment current max value by 1
    intNextMaxValue = rst("MaxValue") + 1

    ' validate new max value range
    If intNextMaxValue <= 90 Then
        rst("MaxValue") = intNextMaxValue
    Else
        rst("MaxValue") = 0
    End If
    rst.Update

    ' return new max value
    GetNextMaxValue = rst("MaxValue")

     ' Clean up
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Function

ErrorHandler:
    If Not rst Is Nothing Then
        If rst.State = adStateOpen Then rst.Close
    End If
    Set rst = Nothing

    If Not cnn Is Nothing Then
        If cnn.State = adStateOpen Then cnn.Close
    End If
    Set cnn = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If

End Function

I then placed a command button and a textbox on a from, and here’s the event procedure:

Private Sub cmdGetNextMaxValue_Click()
    Me.txtNextMaxValue.SetFocus
    Me.txtNextMaxValue.Text = CStr(GetNextMaxValue)
End Sub

Hope that gets you a little further along. That’s a pretty wicked UID you’re working with.

HTH,

Bob


 
Old June 27th, 2004, 10:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Bob, any reason you chose to ignore the Domain Aggregate function DMax?
Couldn't it be done something like:
=DMax("Right(FinalLotNum,2)","tbl_Final_Log","")+1



I am a loud man with a very large hat. This means I am in charge
 
Old June 28th, 2004, 01:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Steven,

Its because the number sequence has to start over again at 1 once it reaches 99. Take the sequence:

63FD2697
63FD2698
63FD2699
63FD2601
63FD2602

The next sequence number you need is 03, but DMax will return 100 because 99 already exists in the database.

Hope that's right?

Also worth is mentioning that this UID format is only good for 99 UIDs per day before you start getting duplicates.

Bob

 
Old June 28th, 2004, 09:22 PM
Registered User
 
Join Date: Jun 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Bob and Steven,

I just get back from work. I have try Bob method, but I could not get the function to known what is my MaxID from the field name "FinalLotNum"; this field is also a PK and index key.
I will never run into the problem of having the function restart after it reach 63FD2699 and go back to 63FD2601, because we could never reaches to 99 in one day anyway. If that case would every come true, then we will push the work to the next day base on priority like 63FD2701 instead.

Thank you for reply
Vandat
 
Old June 28th, 2004, 10:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Vandat,

Quote:
quote:I could not get the function to known what is my MaxID from the field name "FinalLotNum"
Just as a point of clarification, the method doesn't attempt to get your MaxID from the FinalLotNum field. It simply generates the the next, sequential final two digits of your UID.

If you have a UID like 63FD2601, the method generates the 01 portion and stores is in a variable. You would then have to concatenate the 01 prtion to the 63FD26 portion. The 63FD26 portion would have to be generated by another method, probably involving the DatePart function to extract your day, month, and year values, then some logic to associate the date parts with their respective letters.

Once the 63FD26 portion is concatenated to the 01 portion, then the complete UID string can be written to your FinalLotNum field.

Least thats my best guess at what you're trying to do.

HTH,

Bob






Similar Threads
Thread Thread Starter Forum Replies Last Post
select Max 2 rows for each ID veeruu SQL Language 1 January 31st, 2006 05:08 AM
Problem with displaying ID when finding max or min marcin2k Access VBA 1 October 3rd, 2005 07:42 AM
Find a logon id timoma Access VBA 1 September 10th, 2004 05:31 PM
Urgent please help to find MaxID and add 1 to it nhatrang71 Access VBA 1 June 26th, 2004 04:15 PM
How to get max value within same id jackps SQL Server 2000 1 January 20th, 2004 07:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.