M
Mekinnik via AccessMonster.com
I have a table with a number of fields on it, however I want to use one field
named 'MSDS' as my primary key. No problem there however the 'MSDS' field
contents is an alphanumeric number I have generated with code, which looks
something like this 'DC001'.. What I want to do is make this number count
based on the previous records number meeting the criteria of a query of the
first two letters of the number.... SO 'DC001,DC002....ETC.' for another it
might be 'Pr001,Pr002.....ETC' Here is a copy of the code I'm tring to use
with this problem..
Private Sub Dept_Change()
Dim strFirstChar As String
Dim countVal As Integer
Dim strVal As String
strVal = Me.Dept.SelText
countVal = Count_Record()
strFirstChar = Left(strVal, 2)
strVal = strFirstChar & "00" & countVal
Me.MSDS.SetFocus
Me.MSDS.Text = strVal
Me.Repaint
End Sub
Private Function Count_Record() As Integer
Dim strReturnVal As Integer
Dim sql As Variant
sql = DLookup("strVal", "hazinventory", "MSDS")
Count_Record = intReturnVal
intReturnVal = intReturnVal + 1
End Function
named 'MSDS' as my primary key. No problem there however the 'MSDS' field
contents is an alphanumeric number I have generated with code, which looks
something like this 'DC001'.. What I want to do is make this number count
based on the previous records number meeting the criteria of a query of the
first two letters of the number.... SO 'DC001,DC002....ETC.' for another it
might be 'Pr001,Pr002.....ETC' Here is a copy of the code I'm tring to use
with this problem..
Private Sub Dept_Change()
Dim strFirstChar As String
Dim countVal As Integer
Dim strVal As String
strVal = Me.Dept.SelText
countVal = Count_Record()
strFirstChar = Left(strVal, 2)
strVal = strFirstChar & "00" & countVal
Me.MSDS.SetFocus
Me.MSDS.Text = strVal
Me.Repaint
End Sub
Private Function Count_Record() As Integer
Dim strReturnVal As Integer
Dim sql As Variant
sql = DLookup("strVal", "hazinventory", "MSDS")
Count_Record = intReturnVal
intReturnVal = intReturnVal + 1
End Function