create unique sequential index on a form 2

[blom0344]

I am looking for a way to create a unique counter on a form that is the composite of the following elements:

1. String like 'GAR_'
2. Login Account on PC like BVH
3. Primary key value of the current record (= the autonumber on the form)

This would lead to an unique identifiable code like:

'GAR_BVH_1233'

This code is to be stored in the same table as rest of the data on the form.

Can this be done?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Eupher]

Certainly it is, it's a pretty simple concatenated string. However, if you use the value of an autonumber as part of the string, there will be gaps in the sequence when records are deleted, because autonumbers are NOT re-used when records are deleted. Something like this should work:

Me!MyUniqueID = "GAR_" & CurrentUser & "_" & Me!RecID

...where "MyUniqueID" is the name of your unique ID field and "RecID" is the name of your autonumber field. You can put this code behind a command button or possibly the After Update event of your form.

If the ID field must be truly sequential, i.e. no gaps, then it's a little more complicated, but can also be done. Post back if you prefer that method.

Ken S.

 

[blom0344]

Hello Ken,

There is no problem with gaps in the sequence, all it needs to be is unique.
However, I am trying to get at the login account that was used to log in to the PC, instead of the account for the database. So far I haven't been able to find if I can get to this information from access ..........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Eupher]

Okay, a quick and dirty way:

Me!MyUniqueID = "GAR_" & Environ("UserName&quot & "_" & Me!RecID

See below for more info:

http://www.mvps.org/access/api/api0008.htm

Thread96-613910

Thread705-653130

Ken S.

 

[blom0344]

Ken,

The piece of code you reffered to in the links does the job brilliantly. Here's a star for your excellent reply.......

T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

ouch,

I thought I was there, but it appears the unique key needs to be propagated to the child records with an additional counter.
Something like:

GAR_TBL_1344 for the parent and

GAR_TBL_1344-1
GAR_TBL_1344-2
GAR_TBL_1344-3
..............
..............

for the child records. These records are created using a subform in the main form, creating entries in the child table.
Can this be done?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Eupher]

Sure. Something along the lines of:

Me!subformUniqueID = Me.Parent!MyUniqueID & "-" & Me!subformRecID

This would be placed in the subform in a similar manner to the code you placed in your main form. And of course, change field names as appropriate. Here's another link from that MVPS site:

http://www.mvps.org/access/forms/frm0031.htm

HTH...

Ken S.

 

[blom0344]

Yep,

I was just about to dabble with what you are proposing.
However this throws me a compile error: Expected end of statement.

Me.ID = Me!Parent!Garantie_Ref_Nummer

is compiled without error,

but Me.ID = Me!Parent!Garantie_Ref_Nummer&"-"

gives compilation error

Is there a way to get the subformID as a counter from 1 to ...?

Thanks for all your support , by the way..

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Eupher]

Okay, be careful how you use the bang vs. the dot. If referring to the parent form from the subform, use the dot or Access will think you're referring to a field named "Parent". Also, there should be a space before and after the ampersand character. So the correct syntax for your statement should be:

Me!ID = Me.Parent!Garantie_Ref_Nummer & "-"

There are many times when either the bang or the dot can be used, but not in all instances. See

http://doc.advisor.com/doc/05352

for a pretty good explanation.

I suppose the easiest way to get an incremental counter is to create an autonumber field in the table to which your subform is bound (assuming it is bound, and assuming gaps in the sequence are okay). There are other ways, of course, but that's the simplest and most direct.

Ken S.

 

[danvlas]

Sorry to pop in...but why on earth would you want to store a calculated value in the table?

As Ken said in his first reply...it's just a concatenation. You can always restore the string you want through an expression...

Use the AutoNumber as a primary key in the main table.
Set a sequential numbering system in the subform using any available method to increment each subform record by 1.

One simple way is to use the BeforeUpdate event of the subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Newrecord Then
Me("SequentialNumber&quot = Nz(DMax("SequentialNumber","SubformRecordsource","ForeignKey = " & Me.Parent("AutoNumberField&quot), 0)+1
End If
End Sub

(Since this may fail in a multi-user environment, you should explore some more possibilities.)

Then add the two tables to a query and use the concatenation to obtain whatever you want.

Storing a calculated value in a table is reasonable as long as the calculation elements may change over time and you want to keep a history of the results.
Otherwise it's a source of trouble.

Just my opinion.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[blom0344]

First answer to Eupher:

I got the code to work, making sure to get exclamation mark and dot right. Funny thing is that the field on the subform is not filled correctly untill I actually move the cursor to the ID field.

My expression is:

Me!Omschrijving = Me.Parent!Gar_Ref_Nummer & "-" & Me!ID

I tried this in various after_update/exit events for a number of fields, but it does not work like it should

So I tried the expression on the subform properties in the after insert event. This does work only if you create a new entry,than the previous one gets the proper index-value.So every last subform record never gets its index filled in...

To danvlas:

The reason to store the calculated field is because it stores the Windows-ID of the employee who creates the entry,which is 100% historic by nature.

I will try to get your code to working, though the syntax will probably kill me



T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

The code did compile the very first time!

Two problems:

1. There is no increment, every sequential number is 1.
2. The sequential number is only created on creating the next record, which is naturally a problem for the last real entry in the subform.

Sorry for time I ask from you, but I feel that it is this close to working like it should!

T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

Okay,

I think I got it to work like this:

1. I created a query "Q_Max_ID" on the maintable that count number of children for each Key. = Count_ID

2. In the Before_Insert event I referred to the query like:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intMax As Integer
If NewRecord Then
intMax = Nz(DMax("Count_ID", "Q_Max_ID", "Key = " & Me.Parent("Key&quot), 0)
intMax = intMax + 1
Me("Aantal&quot = intMax
End If
End Sub

This does give the increments and solves the other problem.

Thanks again for showing the way ..............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[danvlas]

BeforeInsert is a bad choice in this case.
It is triggered when the record is created, but the value is NOT saved until you save the record. If another user starts creating a new record before you save yours, he will get the same value.

BeforeUpdate reduces this risk (but does not eliminate it completely!)

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[blom0344]

Before update does not work in this case, because the code only runs upon creating a new record. So , you can imagine that the last entry will never receive it's incremental key!
(I would not expect this behavior either, but it is really what is happening)

Users will never work on the same main-table entry, so I cannot see them getting into each other's way, cause the query fetches the number of children for each Key.

But maybe I am overlooking something there........


T. Blom
Information analyst
tbl@shimano-eu.com

 

[Manaburrn]

Excellent post - helped me out tremendously.

Raphael