Help with a comparison code

[Mekinnik via AccessMonster.com]

I have this code I got to lookupfrom the MSDS field the highest number
already used for a left 2 letter prefix, then its suppose to add 1 to the
prefix only it doesn't work. The MSDS field prefix is based on the users
selection from another field. Can someone explain why??


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varResult As Variant
Dim strWhere As String
Dim iNum As Integer
Dim strFirstChar As String
Dim strVal As String
Me.Dept.SetFocus
strVal = Me.Dept.SelText
strFirstChar = Left(strVal, 2)
strVal = strFirstChar

If Me.NewRecord Then
'Lookup the highest number already used for the prefix.
strWhere = "MSDS Like " "" & Me.[Dept] & " * """"
varResult = DMax("MSDS", "Hazinventory", strWhere)
If Not IsNull(varResult) Then
iNum = Val(Right(varResult, 2)) + 1
End If

'Assign the Value
Me.MSDS = strVal & Format(iNum, "00")
End If
End Sub

 

[Randy Harris]

I have this code I got to lookupfrom the MSDS field the highest number
already used for a left 2 letter prefix, then its suppose to add 1 to the
prefix only it doesn't work. The MSDS field prefix is based on the users
selection from another field. Can someone explain why??


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varResult As Variant
Dim strWhere As String
Dim iNum As Integer
Dim strFirstChar As String
Dim strVal As String
Me.Dept.SetFocus
strVal = Me.Dept.SelText
strFirstChar = Left(strVal, 2)
strVal = strFirstChar

If Me.NewRecord Then
'Lookup the highest number already used for the prefix.
strWhere = "MSDS Like " "" & Me.[Dept] & " * """"
varResult = DMax("MSDS", "Hazinventory", strWhere)
If Not IsNull(varResult) Then
iNum = Val(Right(varResult, 2)) + 1
End If

'Assign the Value
Me.MSDS = strVal & Format(iNum, "00")
End If
End Sub

It looks to me as though MSDS is a text field. DMAX of a text field will
return the last value sorted in alphabetic order, not a numeric value. You
could probably use DMAX("Mid([MSDS], 3)", ... to get the highest numeric
value with the first two characters stripped off.

This is one of the reasons for normalizing data. You shouldn't have the
prefix and the value together in the same field.

 

[John Spencer]

I suspect that strWhere is not returning what you think it is.

strWhere = "MSDS Like " "" & Me.[Dept] & " * """"

StrWhere is now set to
MSDS Like " LL * " (Note the leading and trailing spaces)
where LL is being used to indicate two letters. I think you want
MSDS Like "LL*"

For clarity, I prefer to use the following method.
strWhere = "MSDS LIKE " & chr(34) & me.[Dept] & Chr(34)

But you can use
strWhere = "MSDS Like """ & Me.[Dept] & "*""""

And further review of your code leads me to guess that you don't want Me.Dept in
that string, but strFirstChar. Also, I would simplify this by not setting focus
to Me.Dept and simply use strVal = me.Dept (or Me.Dept.Value).

StrVal=Left(Me.Dept,2)
strWhere = "MSDS Like """ & strVal & "*""""
varResult = DMax("MSDS", "Hazinventory", strWhere)

In addition, what is going to happen when your values exceed 99?