Sending unbound form information into existing table by SQL

[alphil2001]

Hi all

I have a problem, im quite new to the SQL part of access and stored procedures so please be gentle with me.

I have an access database which is has a number tables which holds information about different things. One is a Service table, one for Inspections and one for Applications all linked by a service ID number.

The problem I have is this:
I have a form that has been created with unbound fields that is populated by SQL taking the information from the three different tables into one form.

I am trying to create an Audit train to show what has been changed from and what has been changed into.

I have copied each table and called them "table name" with "audit" at the end of it. I have also created an Append Query for each of the tables because I want the sql stored procedure to pass the information from the unbound form, into the 3 audit tables and add an entry before anything is changed. Is this the best option to be able to do this or am I making it too complicated for myself

I have been going round and round in circles trying to get this sql to work and pass the text from the form into the tables and add entries each time a button is pressed.

I have stored procedures that populate the unbound fields with the information from the tables but when I try and sort this out the information is not being passed through from the sql into the Append query and adding the rows from the form into the audit tables, can anyone help me please
thanks very much for your help in advance,
Alphil2001

Here's my code for the passing through of information:

Private Sub UpdateServiceAudit(plngFromId As Long)
On Error GoTo errhand

Dim strSQL As String
Dim Srvid As String
Dim srvno As String
Dim srvname As String
Dim postcode As String
Dim deleted As String
Dim RMUcode As String
Dim RegTypecode As String
Dim StatusText As String
Dim Name As String

strSQL = "EXEC qryGetService @SrvId=" & lngSrvId
strSQL = strSQL & "@SrvId=" & lngSrvId
strSQL = strSQL & "@srvno=" & srvno
strSQL = strSQL & "@srvname=" & srvname
strSQL = strSQL & "@postcode=" & postcode
strSQL = strSQL & "@deleted=" & deleted
strSQL = strSQL & "@RMUcode=" & RMUcode
strSQL = strSQL & "@RegTypecode=" & RegTypecode
strSQL = strSQL & "@StatusText=" & StatusText
strSQL = strSQL & "@Name=" & Name

strSQL = "EXEC qryUpdateServiceAudit @SrvId=" & lngSrvId
strSQL = strSQL & "@lngSrvId=" & Srvid
strSQL = strSQL & "@srvno=" & srvno
strSQL = strSQL & "@srvname=" & srvname
strSQL = strSQL & "@postcode=" & postcode
strSQL = strSQL & "@deleted=" & deleted
strSQL = strSQL & "@RMUcode=" & RMUcode
strSQL = strSQL & "@RegTypecode=" & RegTypecode
strSQL = strSQL & "@StatusText=" & StatusText
strSQL = strSQL & "@Name=" & Name
DoCmd.RunSQL "INSERT INTO tblServicesAudit ( [idsSrvId]= [SrvID],[chrSrvNo]=[Srvno], [chrSrvname]=[srvname], [chrdelete]=[deleted], [chrRMUCode]=[RMUCode], [chrRegtypecode]=[regtypecode], [chrStatustext]=[statustext], [chrname]=[name]) "
DoCmd.OpenQuery "qrygetservice @SrvId=" & plngtoSrvId
DoCmd.OpenQuery "qryUpdateServiceAudit @SrvId=" & plngtoSrvId
Call LoadNextService
strSQL = "UPDATE tblServicesAudit " & _
"SELECT [srvid],[srvno],[srvname],[postcode],[deleted],[RMUcode],[RegTypecode],[Statustext],[Name]FROM tblservices WHERE @SrvId=" & plngFromId
CurrentDb.Execute strSQL

If (Not objDBconnection.ExeSQLActionCmd(strSQL)) Then GoTo errsql

Exit_rtn:
Exit Sub

errsql:
Call ReportSQLError("frmInspections", "UpdateInspection", _
"FAILED to Update Inspection", strSQL)
GoTo Exit_rtn

errhand:
AddError "frmTest", "UpdateServiceStatus", Err.Number, Err.Description, , 4
Err.Clear
Resume Exit_rtn

Exit_UpdateInspection:
Exit Sub

End Sub

 

[lameid]

It's hard to tell if I follow correctly but the very first thing I notice is that you seem to neglect whitespace and parameter separatros entirely in your code.

Instead of

strSQL = "EXEC qryGetService @SrvId=" & lngSrvId
strSQL = strSQL & "@SrvId=" & lngSrvId
etc.

I think you want

strSQL = "EXEC qryGetService @SrvId=" & lngSrvId
strSQL = strSQL & ", @SrvId=" & lngSrvId

etc.

Also
CurrentDb.Execute strSQL
is only going to excute against the current database using Jet SQL (Access SQL). You need to either need to connect to SQL server programatically, which I don't remember how to do using DAO or write Jet SQL (not recommended) against linked tables and use it to use your execute.

You might do well to look through the Access Project forum. Assuming you are using at least Access 2000.