Initialize with serial number

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Initialize with serial number

Post by adam »

Hi anyone,

I have a column named "serial" in my access table "data" with the format 23/2021. 23 is serial number. 2021 is the year.

I want to create an excel userform initilize code where when the userform opens, it loads the form with last row value plus 1 to the TextBox 1.

For example, if the last row value is 23/2021 the code will populate the TexBox 1 as 24.

Any help on this would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Initialize with serial number

Post by HansV »

It would be easier if you had two separate columns in the Access table, one with the serial number and one with the year. You can always concatenate those in a query for display purposes. Retrieving the highest serial number for a year would be simple; with your current setup it is more work.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Initialize with serial number

Post by adam »

Totally agree with you Hans. How may I do it with the serial number only?

Would appreciate any help on this.

Thankyou.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Initialize with serial number

Post by HansV »

Use ADODB or DAO to connect to the database, then open a recordset to retrieve the maximum serial number for the current year. For example if you use ADODB and open an ADODB.Connection object named cnn to the database:

Code: Select all

    Dim rst As ADODB.Recordset
    Dim sql As String
    Dim serial As Long
    sql = "SELECT Max([SerialNumberField]) FROM [TableName] WHERE [YearField]=Year(Date())"
    Set rst = New ADODB.Recordset
    rst.Open Source:=sql, ActiveConnection:=cnn, Options:=adCmdText
    serial = rst(0) + 1
    rst.Close
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Initialize with serial number

Post by adam »

Thankyou very much for the help Hans.
Best Regards,
Adam