Updating fields from one table with values from another

[smartglass]

Hi:
I use VFP to create a stand alone database of sales orders from a SQL database. I append new sales orders from SQL each day. My problem is that as sales order records in SQL are closed, then I need to close them in the VFP database, too (ie set the Field value 'Status' from 'O' to 'C' where they are 'C' in SQL. In other words update VFP table where SQL table = 'C'. THe vfp table is a mirror of the SQL table so joins are at the record number.
I have tried various UPDATE queries, and read several manuals, but nothing seems to point me to the right code.Can anybody point me forwards, please?
Thanks!

 

[mkrausnick]

What version of VFP are you using? If using VFP9, take a look at UPDATE ... FROM

in the online help. You would pull a record set from the SQL table into a cursor using SQLEXEC and operate with that.

If using an earlier version, you could always use a SCAN loop to update the records one at a time. Again, pull a record set from the SQL table and SCAN it, either by setting a relation to your VFP table or INDEXSEEK'ing the key in the VFP table, then update the record with the value from the cursor.

Mike Krausnick
Dublin, California

 

[smartglass]

Thank you very much, Mike. However I am on VFP8 and all I can find refers to updating with a fixed value.
I will look at SCAN - but the table is quite big.
I have run a 'changeto' query in Paradox but this does not convert to SQL. I have read a few SQL and VFP manuals but without any joy.
Maybe time to upgrade!

Thanks again

 

[JimWWinter]

Smartglass,

If you have some control over the SQL database, you might consider adding a datetime field to the table to indicate when a Sales record is closed. Then you could construct a query that would only return a small subset of the Sales records primary keys and the updates could be handled very quickly.

Regards,
Jim

 

[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 a 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

 

[Mike Lewis]

alphil2001,

It's much better to start a new thread for a new question. If you tack it onto someone else's thread, other people won't necessarily find it and you won't get such a good response.

Also, you don't actually say what your back end is, but in any case it's not really a VFP issue. You'd do better posting in a forum for your specific database.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[alphil2001]

Thanks Mike will do