 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / October 2006Using VBA in an ADP to generate an ID value
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? 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.  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > 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 [quoted text clipped - 13 lines] > 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? 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? [quoted text clipped - 29 lines] > > 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? Thanks for your help. This LabID is the primary key of the table. > you mean Form_BeforeInsert? > [quoted text clipped - 31 lines] > > > 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? you really don't need a trigger or a VBA function. can't you just change the seed value for an identity column? > 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 [quoted text clipped - 13 lines] > 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? sorry <imani_technology_spam@yahoo.com> ??????:1158960898.162750.217790@i42g2000cwa.googlegroups.com... > 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 [quoted text clipped - 13 lines] > 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? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.