Front End : VB 2008
Back End : Access 2003
Example of Database Record:
cCode | Name | Company | Contact
20091 | ABC | XYZ | 123
20101 | AB1 | XYZ1 | 456
20102 | AB1 | XY123 | 987
I am using year for making a unique code for person.
Eg: 1 person joined in year 2009 so only one entry with code 20091, 2 join in 2010 so two codes 20101, 20102
'Combo Box Used for Selecting Year Starting from 2007
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
cboYear.Items.Clear()
Dim i As Single
For i = 2007 To Year(Now)
cboYear.Items.Add(i)
Next
cboYear.SelectedIndex = cboYear.MaxDropDownItems - 1
End Sub
'Calling Database on Combo Box Change
Private Sub cboYear_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboYear.SelectedIndexChanged
cCode()
End Sub
' Calling from Combo Box Change
Private Sub cCode()
Try
If con.State = ConnectionState.Open Then con.Close()
con.Open()
com = New OleDbCommand("Select max(val(mid(cCode,5,len(cCode)))) as maxV from Contact where val(left(cCode,4)) = " & Val(cboYear.SelectedItem), con)
reader = com.ExecuteReader(CommandBehavior.CloseConnection)
If reader.read Then
txt.Text = Val(cboYear.SelectedItem) & (reader("maxV") + 1)
Else
txt.Text = Val(cboYear.SelectedItem) & 1
End If
reader.Close()
Catch ex As Exception
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Sub
Also tried
If reader("maxV") Is DBNull.Value Then
Above code works if any existing value is there for any given year, if 20091 is there then it show new value as 20092. But when selecting 2011 from Combo Box then it do now show any changes.
Wants it to check for the highest value with any year and then increase the value by 1, if no record is found for the year then create a new record like YEAR1.
During runtime checked that maxV is showing System.DBNull while selecting a New Year which is not in database.
Please provide some information on the matter.