Using VBA in an ADP to generate an ID value

[Guest]

The table in the database has a field called LabID. That field is an
integer and consists of the year plus a counter. For example, the
first record of 2006 would be "20060001," the second record of 2006
would be "20060002" and so on.

Originally, I tried to create an Insert trigger that could generate the
ID value when a new record is inserted. An MVP in another forum was
kind enough to help me with this trigger and it worked flawlessly.
However, the problem is the Access ADP. Basically, the trigger and and
the bound form in the ADP did not get along, and an error message
occurred every time an attempt was made to insert a new row.

Now I'm looking at a new approach. I would like to generate the value
described in the first paragraph, but in the bound form itself instead
of using a trigger. I'm sure this would require a VBA solution, but my
VBA isn't as strong as my SQL. Can anyone help me achieve this goal
listed in the first paragraph using VBA in the form?

 

[Sylvain Lafontaine]

Do you want to use or is it necessary to use this LabID as the primary key
for the table or not?

If it's not necessary, then all you have to do is to add an identity field
to be used as the primary key and make ADP happy and use your present
trigger to create the new value. It will also be better if you set the
ResyncCommand and the UniqueTable properties but not necessary.

If you want to set this value in VBA code, the best place in my opinion
would be probably to use the OnBeforeUpdate event of the form.

 

[aaron.kempf]

you mean Form_BeforeInsert?

Do you want to use or is it necessary to use this LabID as the primary key
for the table or not?

If it's not necessary, then all you have to do is to add an identity field
to be used as the primary key and make ADP happy and use your present
trigger to create the new value. It will also be better if you set the
ResyncCommand and the UniqueTable properties but not necessary.

If you want to set this value in VBA code, the best place in my opinion
would be probably to use the OnBeforeUpdate event of the form.

 

[Guest]

Thanks for your help. This LabID is the primary key of the table.

 

[aaron.kempf]

you really don't need a trigger or a VBA function.

can't you just change the seed value for an identity column?

 

[scarecrow]

sorry