Help with field problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mekinnik - 04 Nov 2005 22:07 GMT

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

Reply to this Message

Allen Browne - 05 Nov 2005 04:46 GMT

Use DMax() to get the highest value already used for the prefix.
Parse out the number part, add 1, and assign.

If the database is used by more than one person at a time, you will want to
do this at the last possibe moment before the record is saved, to reduce the
chance of 2 people being given the same number while entering data. The last
possible moment is the BeforeUpdate event of the form--just before the
record is saved.

So, somehing like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varResult As Variant
Dim strWhere As String
Dim iNum as Integer

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

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a table with a number of fields on it, however I want to use one
>field
[quoted text clipped - 32 lines]
>
> End Function

Reply to this Message

Mekinnik - 05 Nov 2005 06:42 GMT

Mr. Browne
The first 2 letters come from the user selecting a department for a drop
down list on my form, so the numbers will not all be the same, however if the
user chooses a department that all ready has an MSDS number assigned the I
want the code to query the field 'MSDS' for any records with that deparments
first two letters assigned and add 1 to the end number starting with 001..

>Use DMax() to get the highest value already used for the prefix.
>Parse out the number part, add 1, and assign.
[quoted text clipped - 30 lines]
>>
>> End Function

Reply to this Message

Allen Browne - 05 Nov 2005 06:59 GMT

So, modify the line:
strWhere = "MSDS Like ""DC*"""
to something like:
strWhere = "MSDS Like """ & Me.[Department] & "*"""

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Mr. Browne
> The first 2 letters come from the user selecting a department for a drop
[quoted text clipped - 42 lines]
>>>
>>> End Function

Reply to this Message