On Mar 19, 12:11 pm, jlad...@gmail.c om wrote:
Hi everybody! I´m new at this and I realize there´s a lot of great
and smart people, so let´s see if anybody knows how to do this.
I have a table where i need to combine 2 fields in another. That
resulting field will be the primary key.
I´ve seen some debates about this topics and i realize isn´t very
difficult but here comes the harder part (for me).
I need the second field to be an autoincrement field depending on the
first.
EXAMPLE:
Field 1: Brand (It could be A, B and C)
Field 2: Number (Will increment depending of the last value of the
brand)
Field 3: Brand-Number
If the last A brand introduced was BRAND A-3, Then when I select the A
brand to assing it to another register the "Number" field should
automatically display 4, and the "Brand-Number" field should display
"A-4".
DO U GET ME? Do u need me to be more specific at anything? LET ME
KNOW...
I hope one of u could please HELP ME!
Specific Questions:
- WHERE and WHAT do I have to write for the second field "Number" to
acomplish that behavior??
- WHERE and WHAT do I have to write for the Third Field "Brand-Number"
to join the information from fields 1 and 2??
Thanks in advance!
Although my style is sometimes a bit unorthodox, I would do as
follows:
tblBrandProduct s
BPID AutoNumber (Primary Key)
Brand Text
KeyNum Long
Part of the reason for doing this is to avoid having a text field
become the primary key. Suppose you have a bound form to this table
called frmBrandProduct s with cbxBrand linked to Brand and txtKeyNum
linked to KeyNum. In the AfterUpdate event of the Brand combobox
(cbxBrand) you can set the KeyNum value as follows:
Private Sub cbxBrand_AfterU pdate()
If IsNull(txtKeyNu m.Value) Then
If Not IsNull(cbxBrand .Value) Then
txtKeyNum.Value = Nz(DMax("[KeyNum]", "[tblBrandProduct s]",
"[Brand] = " & Chr(34) & cbxBrand.Value & Chr(34)), 0) + 1
Else
txtKeyNum.Value = Null
End If
End If
End Sub
This will only affect new records since existing records already have
a KeyNum value.
I have a Rowsource for cbxBrand:
SELECT DISTINCT Brand FROM tblBrandProduct s;
When you move to a new record and select a brand, the appropriate
KeyNum will be calculated.
Whenever you need to display the Brand and KeyNum combination, such as
on a report, use:
[Brand] & "-" & [KeyNum]
For a search use:
WHERE [Brand] = "A" AND [KeyNum] = 3;
Breaking out the Brand and KeyNum values allows you to join on just
Brand or both Brand and KeyNum.
The purpose of the BrandKeyNum combination seems to be to help people
see a product's brand directly from the value. Access does not need
such assistance. One thing to watch out for is that deleted KeyNum
values are not reused unless the maximum KeyNum for a brand gets
deleted. If multiple users are adding products at the same time it
may be better to use the BrandKeyNum combination as a primary key so
that there is no chance of getting a duplicate BrandKeyNum combination
because two users try to create a new record for the same brand at the
same time. When doing it that way the form's BeforeUpdate event can
be used to check to see if the BrandkeyNum combination already exists
in the table so that a primary key error can only occur in rare
instances.
James A. Fortune
CD********@Fort uneJames.com 
