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.
Initialize with serial number
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Initialize with serial number
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Initialize with serial number
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
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Initialize with serial number
Totally agree with you Hans. How may I do it with the serial number only?
Would appreciate any help on this.
Thankyou.
Would appreciate any help on this.
Thankyou.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Initialize with serial number
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
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07