I have read several variations of this topic posted on this news group.
However I was not able to find the answer for my problem.
I am trying to build code (see below) to update a large single record
using data from a temporary (editing) table with identical field
structure. I iterate through the fields (some 50 of them) with a For
Each - Next loop to update each field with the SQL statement. The
updating works fine with numeric data type but not with text type data.
Each time it tries to update a text data field, I get the following
input box appearing, "Enter parameter value". If I go to the query
design window I can build and run a query based on the same parameters
and update all the fields without any problems (no input box popping
up) regardless of data type. I am wondering why my SQL statement gives
me the pop up and how I can get around it. Is this the best way to
update a record in a table or is there a simpler record global
overwright method that could be used.
Sub subUpDateRec()
Dim db As Database, tbl As Recordset, tbl2 As Recordset, fld As
Field, fld2 As Field,
strFld As String, strValue As String, i As Integer, strCriteria As
String
Set db = CurrentDb
Set tbl = db.OpenRecordset("MyTable")
Set tbl2 = db.OpenRecordset("MyTempTable")
strCriteria = [Forms].[MyForm].[MyControl]
For Each fld In tbl.Fields
strFld = fld.Name
Set fld2 = tbl2.Fields(i)
DoCmd.RunSQL "UPDATE MyTable SET MyTable." & [strFld] & " = " & fld2
& " WHERE (((MyTable.Field)=" & strCriteria & "));"
i = i + 1
Next fld
Set db = Nothing
Set tbl = Nothing
End Sub
(Note: My input form is not bound to the table to be updated.)
I also considered using delete and append methods but the records will
be edited
regularly and I'm afraid the table will become too large and need to be
compacted too often.
(can one never compact often enough!?)
