@bherring
bherring
I do not see why you need a trigger to be frank! the ODBC driver is not tuning into the trigger and my inclination would be to dump the trigger entirely in favour of methods that existed prior to the invention of those things - for which I am not a great fan myself.
Wherever possible I personally favour keeping processing on the server and stored procedures are all about keeping the processing precompiled and optimised on the server.
The idea is that you place your sequence code CRUD (create, read,update,delete) in nice tidy little packages in stored procedures and make calls to them to do the work server side, as opposed to on the client. You are doing a mixture of both and many would argue that the simplest way is to just to fire off an update SQL statement in the 'afterupdate' event of textbox control servicing your AMNT form control and let it deal with the foreign table value accordingly and thats it!... job done!... not a trigger in sight and no complicated stored procedures either!
But given the client is not 'SQLing' properly and given you 'have' that trigger all you want to do is get it activated! that is the thrust of the thread, so that is why I mention a stored procedure. You are obviously working server side so this is my little contribution to your understanding as to what else there is out there 'other' than triggers.
If a stored procedure was to do the work then it obviously does it server side. This
would activate the trigger because
both routines are sitting on the same machine. In order to make the stored procedure do something meaningful in these circumstance you would have to pass/provide 'something' to it, to enable it to its work... and that is a parameter value.
Below is a stored procedure that takes a single parameter of the datatype INT namely your PATID value it then uses that value to UPDATE the AMNT amount in the PAT_VISIT table on the server, but only where the Patient ID field matches the PATID field in the CUST_PAYMENT table.
- CREATE PROCEDURE dbo.usp_Update_Pat_Visit_Amount
-
(@PATID int)
-
-
AS
-
UPDATE PAT_VISIT
-
SET PAT_VISIT.[Total Payments]=CUST_PAYMENT.amnt
-
FROM PAT_VISIT, CUST_PAYMENT
-
WHERE CUST_PAYMENT.[PatID]=@PATID
-
GO
So how do you get this procedure working? heres how, firstly you create it on the server. If you created the trigger you can create this as well, just paste it into a new procedure window.
Now that it is created and you have set the relevant permissions on the server to use it, you want to call it from your frontend application. The easiet way to call it is to create a simple '
passthrough' query and save it.
The passthrough query is a conventional query having an SQL property. It is that SQL property text string that we can manipulate whereby we 'pass' a parameter to it replacing the @PATID section of the string with a value in code.
So.. next step....create that passthrough query and in the SQL window just paste this into it and save it as qryPassthrough and then paste the following into its SQL window save and close the query
dbo.usp_Update_Pat_Visit_Amount @PatID
Now you can place the following code wherever you wish either on a command button to test it out and then maybe later on the afterupdate event of your AMNT textbox
- Dim db As DAO.Database
-
Dim qdf As DAO.QueryDef
-
Set db = CurrentDb
-
With db
-
Set qdf = .QueryDefs("qryPassthrough")
-
qdf.SQL = "dbo.usp_Update_Pat_Visit_Amount '" & Me!txtPATID & "'"
-
.Close
-
End With
-
'turn warnings off and run the passthrough
-
DoCmd.SetWarnings False
-
DoCmd.OpenQuery "qryPassthrough", acNormal, acEdit
-
DoCmd.SetWarnings True
Now the point of all of this is to show you that the stored procedure is actually updating that value for you and NOT the trigger so there really is not point keeping that trigger at all from how I understand this.
Bit long winded this post to say the least, but I hope I am helping you a little as to what is available to you
:-) 
6
