Generating a unique number & linking form/subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kathy - 17 May 2007 17:05 GMT

I am creating a form with a subform...the tables behind the forms are
PatientData and MedicationRecord in a one-to-many relationship with
strChartID as the primary key and linking field. I would like to generate a
unique chart number and so far I have built the following:

Private Sub strChartID_Enter()
If Me.NewRecord Then
Me.strChartID = Left([strLast], 3) & Left([strFirst], 2) & "00" &
[autoNumber]
End If
End Sub

This code seems to be working, but I am nervous to use it (since I am an
insecure newcomer) and I am not sure about using the autoNumber? Is there a
better way? Or a more secure code?
Any advice is much appreciated.

Signature

Kbelo

Reply to this Message

Jerry Whittle - 17 May 2007 17:27 GMT

Unless there is some business rule that says that the chart number must be in
some specific format, rigid numerical sequence, and/or have "meaning", you
best bet is to use an autonumber that is also the primary key field.

If you do need a specific chart number format, I'd still have an autonumber
as the primary key field and link it to the child table. I'd have another
ChartNumber field what would have a unique constraint/unique index on it.

Remember that an autonumber, if in a primary key field, is only guaranteed
to be unique within the records in a table. (There's even bug that might
cause that not to happen. Bug fixed if you have automatic updates.) It will
not necessarily be sequential.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I am creating a form with a subform...the tables behind the forms are
> PatientData and MedicationRecord in a one-to-many relationship with
[quoted text clipped - 12 lines]
> better way? Or a more secure code?
> Any advice is much appreciated.

Reply to this Message

Kathy - 17 May 2007 21:58 GMT

Thank you Jerry for your reply...please bear with me as my ignorance may be
glaring...At first, I did try to link with the autoNumber(primary Key), but
Access wouldn't let me do that, so that is why I tried that other code. I
would prefer to stay with an autoNumber, but I am guessing that I am missing
the "child" ChartNumber with unique contraint/unique index that you
mentioned. Is that what I am missing and is it easy to do? There is no
special format nor do the chart numbers need to be sequential. Thank you
again.

Signature

Kbelo

> Unless there is some business rule that says that the chart number must be in
> some specific format, rigid numerical sequence, and/or have "meaning", you
[quoted text clipped - 25 lines]
> > better way? Or a more secure code?
> > Any advice is much appreciated.

Reply to this Message

Jerry Whittle - 18 May 2007 14:58 GMT

As the code seems to be working and it would take a big redesign effort for
your tables, forms, reports, and queries, I think I'd leave it alone.

Just remember that the code only works when the form is open. If someone
goes directly to the tables, they could put in bad records. One way to negate
this problem is to set up a relationship with Referential Integrity. At the
database window go to Tools, Relationships. See if there is a relationship
defined between the two tables. Is so both tables should show up plus there
should be a line between them just like in a query. Better yet if it is a
bold line with a 1 and infinity symbol on it.

If not, add both tables. Drag and drop the bold strChartID field in the
PatientData table onto the matching field in the MedicationRecord table. When
the dialog box appears, select Referential Integrity and then Cascade Update.
(I don't recommend Cascade Delete usually). The moment of truth: Hit OK and
see if it takes. If there's a problem, such as orphan records in the
MedicationRecord table, Access will give you an error.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Thank you Jerry for your reply...please bear with me as my ignorance may be
> glaring...At first, I did try to link with the autoNumber(primary Key), but
[quoted text clipped - 34 lines]
> > > better way? Or a more secure code?
> > > Any advice is much appreciated.

Reply to this Message