Automating and Joining a Field

  • Thread starter Thread starter jladiaz
  • Start date Start date
J

jladiaz

Hi everybody! Let´s see if anybody knows how to do this.

I have an ms-access 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 or 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 (a new) 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!
 
In a single user environment its very easy to achieve, provided all data
input is via a form, which is where it should be done anyway, never in raw
datasheet view.

To assign a value to Field 2 put code in the AfterUpdate event procedure of
the control bound to Field 1, which looks up the last number for the current
value of Field 1 and adds 1:

Dim strCriteria as String

strCriteria = "[Field 1] = """ & Me.[Field 1] & """"

If Not IsNull(Me.[Field 1] Then
Me.[Field 2] = DMax("[Field 2]", "[YourTable]", strCriteria) + 1
Else
Me.[Field 2] = Null
End If

On the form set Field 2's Locked property to true and its Enabled property
to false so it can't be edited.

In a multi-user environment the above could give rise to conflicts if two or
more users are adding a record for the same brand simultaneously. You would
have to cover this by handling the error resulting from the index violation
in the form's Error event procedure, or by using some other means to generate
the number.

As for Field 3, do not include this field in the table; it introduces
redundancy which puts the integrity of the data at risk by leaving the door
open to update anomalies. Instead make Field 1 and Field 2 the composite
primary key of the table, or use a surrogate autonumber primary key.

If for any reason you need to show the values of Field 1 and Field 2 as a
single string you can do so by concatenating the values at runtime, [Field1]
& "-" & [Field 2], either in a computed column in a query, or in a computed
control in a form or report.

Ken Sheridan
Stafford, England
 
Reconsider your approach...

If you want to use more than one field as a (combined) primary key, go right
ahead -- Access allows this.

But you will find you've painted yourself into a corner later on if you
insist on stuffing two facts into a single field (and this is counter to
good database design).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi everybody! Let´s see if anybody knows how to do this.

I have an ms-access 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 or 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 (a new) 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!
 
Correction: to allow for the record being the first of the brand in question
amend the code slightly:

Dim strCriteria as String

strCriteria = "[Field 1] = """ & Me.[Field 1] & """"

If Not IsNull(Me.[Field 1] Then
Me.[Field 2] = Nz(DMax("[Field 2]", "[YourTable]", strCriteria),0) + 1
Else
Me.[Field 2] = Null
End If

Ken Sheridan
Stafford, England
 
Doh! Missing closing parenthesis. Should have been:

Dim strCriteria as String

strCriteria = "[Field 1] = """ & Me.[Field 1] & """"

If Not IsNull(Me.[Field 1]) Then
Me.[Field 2] = Nz(DMax("[Field 2]", "[YourTable]", strCriteria),0) + 1
Else
Me.[Field 2] = Null
End If

Ken Sheridan
Stafford, England
 
Back
Top