SQL Update command

[luismagally]

I need to update a table based on information obtained from a query which is being displayed in a subform. What is the correct syntax for the update query and if possible does any one know how to read the values of the fields in the query to perform the update or do I need to create a temporary table and then get the values from there.

Sorry if it's confusing.

 

[Adjetive]

I learned on Access 97 and I use the database/recordset relationship. This is the code I use to get to the results of a query. If you are using Office 2000 then Database is not a valid Dim unless you include DOA and DAO compile options(I believe from Add-Ins). This code simulates reading your query results as a sequential file.

Dim MyDB as Database
Dim MyRcrdset as Recordset
Dim SQLSTMT as String

Set MyDB = CurrentDB()
SQLSTMT = "Select * From

where [Column1] = ? AND [COLUMN2] = ??"
set MyRcrdset = MyDB.OpenRecordset(SQLSTMT)
With MyRcrdset
If MyRcrdset.EOF then
'no records returned
Else
MyRcrdset.MoveFirst
Do While Not MyRcrdset.EOF
Column1 = MyRcrdset![Column1]
Column2 = MyRcrdset![Column2]
....
SQLSTMT = "Update [Table2] Set [Column1] = " & Column1 & " Where.....;"
DoCMD.RunSQL(SQLSTMT)
MyRcrdset.MoveNext
Loop
End IF
MyRcrdset.Close
End With

What this does is uses the String Concatenation (&) to build your SQL Update query and then executes the query. I hope this helps.

 

[redapples]

I need to update a table based on information obtained from a query which is being displayed in a subform. What is the correct syntax for the update query and if possible does any one know how to read the values of the fields in the query to perform the update or do I need to create a temporary table and then get the values from there.


Sorry if it's confusing.

Well it is a little?

When you say Update a table do you mean change the existing values? or do you mean adding a new record?

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[luismagally]

I mean change existing values. I have a query which I display inside a subform, If I make changes to the values displayed, can I update a table with those values?

 

[redapples]

Yes you can but maybe a bound form is a better solution. The syntax is pretty simple as shown above oir in you other thread. I'd use ADO over DAO for this type of thing - I tend to reserve using DAO for things I cant do in ADO.

Function UpdateTab(cnn As ADODB.Connection, tbName As String, arFields As Variant, arVals As Variant, Optional strWhere) As Boolean
    Dim strSQL As String
    Dim StrChange
    Dim i As Integer
    
    If UBound(arFields) <> UBound(arVals) Then
        Err.Raise 513, "Class clsTableViewMech UpdateTab", "The number of fields and values do not match"
        Exit Function
    End If
    
    strSQL = "Update " & tbName & " SET "
    For i = 0 To UBound(arFields)
        StrChange = arFields(i) & " = " & arVals(i) & " ,"
        strSQL = strSQL & StrChange
    Next i
    
    If Not IsMissing(strWhere) Then
        strSQL = Left(strSQL, Len(strSQL) - 1) & strWhere & ";"
    Else
        strSQL = Left(strSQL, Len(strSQL) - 1) & ";"
    End If
    
    cnn.Execute strSQL
    UpdateTab = true
End Function

This can then be called in these ways

If   UpdateTab( CurrentProject.Connection, "tblMine", Array("FirstField", "SecondField"), Array(MyForm.Ctrl1, MyForm.Ctrl2)) then'no where clause

 If   UpdateTab (CurrentProject.Connection, "tblMine", Array("FirstField", "SecondField"), Array(MyForm.Ctrl1, MyForm.Ctrl2, "WHERE IDField = 1) then"

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk