Help with field problem

  • Thread starter Mekinnik via AccessMonster.com
  • Start date
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
 
A

Allen Browne

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
 
M

Mekinnik via AccessMonster.com

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..

Allen said:
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
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
 
A

Allen Browne

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mekinnik via AccessMonster.com said:
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..

Allen said:
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
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top