I'm not quite following the suggested code, which I believe starts by
filtering for a category, then finding the maximum value for the Sequence
field and adding 1. From what I understand there are several fields
involved. I think that could work, but as I said I don't quite understand
how it all comes together.
I'm going to suggest a way of using a single field (CatNum). cboCat is a
combo box from which the category is selected. I don't know if you would
select the category or an abbreviation. If you are using the abbreviation,
do you wish the user to select the abbreviation, or the actual category?
tblCat is the table in which the number is stored. CatNum is the field in
which the combined value is stored (Cat1-001, Cat1-002, Cat2-001, etc.).
If Me.NewRecord Then
Dim strWhere As String, strCat As String, strCatNum as String
Dim varResult As Variant
strCat = Me.cboCat
strWhere = "[CatNum] Like """ & strCat & "-" & "*"""
varResult = DMax("[CatNum]", "tblCat", strWhere)
If IsNull(varResult) Then
strCatNum = strCat & "-001"
Else
strCatNum = Left(varResult, Len(strCat)+1) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
Me.CatNum = strCatNum
End If
Note that the line:
strCatNum = Left(varResult, Len(strCat)+1)...
assumes that the category is not always the same length. Len(strCat) is the
length (character count) of the category you selected from cboCat; +1 is for
the hyphen). If it is always the same length you could just a number. In
your example, that number would be 5 (four alphanumeric characters such as
Cat1, Cat2; and the hyphen):
strCatNum = Left(varResult, 5)
This code could go into the After Update event for cboCat, or into a command
button (but that would involve an extra step that may be unnecesary). Note
that the code runs only when a new record is being created.
Jackie L said:
What I was trying to do was to come up with a value based on category
chosen
so that the query could be restricted to only show those records from that
category. If the user picks the category from a drop down box, you could
put
an unbound field on the form (hidden, called CategoryChoice). On the
After
Update of the combo box put:
Me.CategoryChoice = Right(Me.Category,1)
So, "Category 1" would save 1 in the hidden field, "Category 2" would save
2, etc.
Then incorporate that hidden field into the criteria line of your query as
discussed below.
Jackie
CompleteBeginner said:
Thanks for the help, I'm not sure I follow everything. I can setup the
initial query that pulls the selected Category. I'm not sure what you
mean
by the hidden field on the form, is this suppose to be a relationship
between
the Category and the numeric values?
I guess i get stuck at this point. I'll continue to look at it to see if
I
can figure out what you're pointing me to, but any clarification would be
helpful.
Thanks,
CB
:
First do a query based on your category choice (qryCatSequence). The
query
should have the category field parsed out to two separate fields:
Category:Mid([CategoryField],3,1)
with the criteria line of that field referring to the form choice for
Category. So, on the form, if they pick Category 1, then in a hidden
field
on the form (CategoryChoice), have the value 1 stored.
The second field of the query should be
Sequence:Right([CategoryField],3)*1
Then, show the Totals line of the query and make the first field a
Where and
the sequence field a Max.
The result of your query should just be the last number for the
category
chosen.
Then, on your form, have a new record button or after choosing the
category
field, have
Me.CategoryField = "cat" & Me.CategoryChoice & "-" &
Format(DMax("Sequence","qryCatSequence")+1,"000")
This explanation may have dragged on a bit. Let me know if you are
confused
by it.
Jackie
:
Hello,
I'm trying to create an ID number that is based on another field in a
table.
Basically I have a field market Category with 6 different values. I
want to
create an ID field that starts at 0 and automatically counts up for
each of
the different categorys.
For example:
If Category 1 is selected the id would be cat1-001, cat1-002,
cat1-003.
If Category 2 is selected the id would be cat2-001, cat2-002,
cat2-003.
Is there a way that I can do this and have the values stored in a
field?
Thanks,
CB
