June 25, 2013 at 8:36 pm
Hi,
I have created a database with a table called "cable"
Fields are
ID - identity
cableID - nchar(8) not null
other fields are present but irrelevant for what I need to ask.
The cableID field consists of an alphnumeric code.
I have created a trigger on update of cable table which works the way I want it.
My problem is that I want to use an Access 2010 form as the front end to enter information to the cable table to cause the trigger to run but as I have made cableID not null it must be filled in and it cannot be duplicated.
In my form the identity field autoincrements which is great and what I want to appear in the cableID field is the next cableID based on the highest cableID present in the database.
The code I have written below as an SQL query does what I want and creates the value (@newcableID) but I do not know how to have this appear automatically in the cableID field in the Access form so that when the users finishes filling the other fields and inserts the record it gets put into the database and therefore runs the trigger. This query would have to run each time a new record needs to be created so that it increments. I cannot use the ID field as there is some data already in the table that needs to stay and it is not in line with the ID field. There may also be instances where data needs to be entered manually which is why I have not used the identity field. It is there for other future uses.
declare @newcableID nchar(8)
declare @cableIDnum int
declare @maxcableID nchar(8)
set @maxcableID = (select max(cableid) from tblCable)
set @cableIDnum = (convert(int, substring(@maxcableID,3,8)))
set @cableIDnum = @cableIDnum + 1
set @newcableID = (select 'CA' + right('000000' + cast((@cableIDnum) as varchar),6))
I hope somebody understands what I am trying to do and I don't know if this is the correct site to ask this question but if anyone can help it would be great.
June 26, 2013 at 9:27 am
If I understand this correctly the trigger needs to run on insert but a record can't be inserted until the trigger runs.
Perhaps you could put your trigger logic in a stored procedure with an output parameter and call the stored procedure on the Access 'before insert' event. Then populate the field from the output parameter and commit your record.
Does you trigger run on insert or on update or both?
June 26, 2013 at 5:04 pm
Hi,
Thanks for replying.
I don't know if I should have mentioned the trigger but as it is affected by the field I am trying to populate I thought I should.
The trigger is an After Insert trigger and it requires the cableID field to be filled as part of it's calculations.
I think a stored procedure might be something to run to get my piece of code working for the cableID but I don't know how to write a stored procedure especially since I think the parameter that I want to put into the code is the max(cableID) from the most recent record and the output of @newcableID is the value that I want to go into the new record as part of it's creation.
Does this make sense?
June 27, 2013 at 8:16 am
Can you move you After Insert trigger logic into the Access 'before insert' event?
July 1, 2013 at 12:25 am
Unfortunately I cannot. The records that the trigger creates are not to be created before the details entered into the tblCable record is created.
I think there must be a way in Access that when a new record form is opened or new record on an already opened form is opened it should be able to run the new cableID code and populate the cableID field ready for insert once the person enters the rest of the information for the cable and then ends with a tab on the last field.
July 1, 2013 at 10:23 am
My Access 2010 application uses DSN-Less linked tables with the SQL Server Native Client 11.0
Just finished a form in Access that creates a new record.
An after update trigger on the table takes some key information (e.g. primary key) and creates a new record on another table.
The key on my Access form, there are 5 fields that must be completed before the Update event fires. (Before_Update event is used)
There are many ways in Access to control that. So, the SQL table isn't aware of an update until Access has all the required fields (including a calculated field) ready to submit.
http://www.access-programmers.co.uk/forums
There is a SQL Server forum and a huge amount of Access Code Samples.
There is also code for the SQL Server Pass-Through query from MS Access.
This passes a T-SQL string to SQL Server to process on the server.
However, since Access 2007, the ODBC or SQL Server Native Client does a very efficient job of translating Access to TSQL.
An Access Select query with a Where statement is so little different than a SP or SQL Pass-Through in terms of execution plan or records returned in most cases.
Hope that gives you something to look into.
July 3, 2013 at 5:22 pm
I have had this answered on another forum. Apparently using max() + 1 is not a good idea as it can cause duplicates.
I know I said I didn't want to use the identity field as a way of generating this field but it seems it is the safest and best way so I now have the ID field as Identity as I always have but the CableID field is now a computed column added in the following way
ALTER TABLE dbo.tblCable
ADD CableID AS 'CA' + RIGHT('000000' + CAST(ID AS VARCHAR(6)), 6) PERSISTED
Thanks for your suggestions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply