numbering in access tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using two tables, one called projects and the other projects details.
They are linked to each other by a unique number (projectID). I want to be
able to number the lines in the projects details table by combining the
unique number fromthe projects table and a line number from the projects
details table. I would like to have the results look like this: 1688.0001
through 1688.00?? for each project. I don't want to use the autonumber
feature since this will eventually cause the number to the left of the
decimal to change after 9999 project details records (this would not take
long as we have many details to each project). I would also like each
project numbering to start with "projectID".0001. Any ideas as to how I can
do this?

Thank you,
 
You need to use 2 separate fields for this. (It's a basic rule of data
normalization to use atomic fields, i.e. do not store 2 things in one
field.)

It's easy enough to print them out as if they were one field, e.g. set the
Control Source of a text box to:
=[ProjectID] & "." & Format([ProjectDetailID], "0000")

To assign the next available project detail number when you add a new record
in your subform, use the BeforeUpdate event procedure of the subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
Me.ProjectDetailID = Nz(DMax("ProjectDetailID", _
"tblProjectDetail", "ProjectID = " & !ProjectID), 0) + 1
End If
End With
End Sub


Note that it is possible for that code to give the same ProjectDetailID
number to 2 different users if they are both entering subform records for
the same project at the same time, but you reduce the chances of that by
leaving the look up until the last possible moment. Form_BeforeUpdate is the
last event before the records is saved.
 
Thank you,

Where would I enter the BeforeUpdate procedure?


Allen Browne said:
You need to use 2 separate fields for this. (It's a basic rule of data
normalization to use atomic fields, i.e. do not store 2 things in one
field.)

It's easy enough to print them out as if they were one field, e.g. set the
Control Source of a text box to:
=[ProjectID] & "." & Format([ProjectDetailID], "0000")

To assign the next available project detail number when you add a new record
in your subform, use the BeforeUpdate event procedure of the subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
Me.ProjectDetailID = Nz(DMax("ProjectDetailID", _
"tblProjectDetail", "ProjectID = " & !ProjectID), 0) + 1
End If
End With
End Sub


Note that it is possible for that code to give the same ProjectDetailID
number to 2 different users if they are both entering subform records for
the same project at the same time, but you reduce the chances of that by
leaving the look up until the last possible moment. Form_BeforeUpdate is the
last event before the records is saved.

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

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

JCL said:
I am using two tables, one called projects and the other projects details.
They are linked to each other by a unique number (projectID). I want to
be
able to number the lines in the projects details table by combining the
unique number fromthe projects table and a line number from the projects
details table. I would like to have the results look like this:
1688.0001
through 1688.00?? for each project. I don't want to use the autonumber
feature since this will eventually cause the number to the left of the
decimal to change after 9999 project details records (this would not take
long as we have many details to each project). I would also like each
project numbering to start with "projectID".0001. Any ideas as to how I
can
do this?

Thank you,
Click to expand...
Click to expand...
 
1. Open your form in design view.

2. Open the Properties box (View menu).

3. Make sure the title bar of the Properties box says "Form", so you are
looking at the properties of the form, not those of a control.

4. On the Data tab, set the Before Update property to:
[Event Procedure]

5. Click the Build button (...) beside this.
Access opens the code window.

6. Make the code look like the code previously posted.

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

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

JCL said:
Thank you,

Where would I enter the BeforeUpdate procedure?


Allen Browne said:
You need to use 2 separate fields for this. (It's a basic rule of data
normalization to use atomic fields, i.e. do not store 2 things in one
field.)

It's easy enough to print them out as if they were one field, e.g. set
the
Control Source of a text box to:
=[ProjectID] & "." & Format([ProjectDetailID], "0000")

To assign the next available project detail number when you add a new
record
in your subform, use the BeforeUpdate event procedure of the subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
Me.ProjectDetailID = Nz(DMax("ProjectDetailID", _
"tblProjectDetail", "ProjectID = " & !ProjectID), 0) + 1
End If
End With
End Sub


Note that it is possible for that code to give the same ProjectDetailID
number to 2 different users if they are both entering subform records for
the same project at the same time, but you reduce the chances of that by
leaving the look up until the last possible moment. Form_BeforeUpdate is
the
last event before the records is saved.


JCL said:
I am using two tables, one called projects and the other projects
details.
They are linked to each other by a unique number (projectID). I want
to
be
able to number the lines in the projects details table by combining the
unique number fromthe projects table and a line number from the
projects
details table. I would like to have the results look like this:
1688.0001
through 1688.00?? for each project. I don't want to use the autonumber
feature since this will eventually cause the number to the left of the
decimal to change after 9999 project details records (this would not
take
long as we have many details to each project). I would also like each
project numbering to start with "projectID".0001. Any ideas as to how
I
can
do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top