Update record of a table

[dealwi8me]

How can i create a module that updates a particular field of all records in a table?

Thank you in advance!

 

[Mile-O]

A bit more information. Update the field to what?

You could just use an UPDATE query to do this, maybe.

 

[modest]

How about?..

Function CallUpdate(strTable As String, strField As String, varValue As Variant)
    Dim db     As DAO.Database
    Dim rs     As DAO.Recordset
    Dim strSQL As String

    strSQL = "UPDATE " & _
                     strTable & _
             "SET " & _
                     strField " = '" & varValue & "';"                    

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Function

 

[dealwi8me]

A bit more information. Update the field to what?

You could just use an UPDATE query to do this, maybe.

what i want to do is to take some fields from diffrent tables, apply a mathematical formula and return a number. The returned number should be inserted in a field of a table.
and i want this procedure applied to all records at once.

Is it possible?

I thought of an Update Query but i don't know if i can apply a formula in an Update query...

 

[dealwi8me]

How about?..

Function CallUpdate(strTable As String, strField As String, varValue As Variant)
    Dim db     As DAO.Database
    Dim rs     As DAO.Recordset
    Dim strSQL As String

    strSQL = "UPDATE " & _
                     strTable & _
             "SET " & _
                     strField " = '" & varValue & "';"                    

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Function

I get error on debugger here...
strSQL = "UPDATE " & _
strTable & _
"SET " & _
strField " = '" & varValue & "';"

 

[Louie]

strSQL = "UPDATE " & _
strTable & _
"SET " & _
strField " = '" & varValue & "';"

An & is missing after strField. the line should read:

strSQL = "UPDATE " & _
strTable & _
"SET " & _
strField & " = '" & varValue & "';"

 

[RoyVidar]

The field is supposed to be numeric, so the single quotes will need to be removed too (and the ending semicolon isn't really necessary)

...strField & " = " & varValue

but, since we really don't know enough about the operation, this is only guesswork... oh - and btw, should you be using spaces or special characters in either the table or field names, be sure to use [brackets] too...

After you've tried the different suggestions, and should it still not work, I think a tad more information on what you're trying to achieve might help us help you...

 

[modest]

they're right, I was missing an "&" after the variable.

as Roy has said, if you're going to use my code, customize it to your needs. if it's a number or a date, you're going to want to take out the single quotes around varValue. it'd be best to test what type of variable you're passing in... and make different conditions for the different variables.

and yes, you can apply a formula in the update query, depending on which formula.

please repost what you want to specifically do.

 

[dealwi8me]

I want to do these through a form (the record source of the form is table1):

1. Open the table called table2
2. Find all records with table2.ID= table3.ID AND table3.ID=table1.ID
3. do the summation of table2.val for the records with table2.ID=table1.ID
4. insert this value (of summation) in the table1.val


I tried the update query in this way:
I create a Select query to get the sum of table2.val. (query1)
I create an update query that updates table1.ID to query1 table2.val sum

..but i get this error "Operation must use an updateable query"