Record update with VBA code

[Mike W]

I am trying to update records with the following code and continue to have
problems getting this to work. I'd appreciate ideas on my syntax / command
problems.

Set rst = CurrentDb.OpenRecordset("CRData")

rst.Index = "CRNumber"
rst.Seek "=", rptSN
rst.Edit
rst.Fields("PartNumber") = rptPartNum
rst.Update

rst.Close
Set rst = Nothing


Thanks,
Mike

 

[Douglas J Steele]

Why not simply:

strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "' " & _
"WHERE CRNumber = " & rptSN

CurrentDb.Execute strSQL, dbFailOnError

This assumes that PartNumber is a text field and that CRNumber is numeric.

 

[Mike W]

Would this work for a large number of other data? The PartNumber item is only
one of list of items I need to update based on the CRNumber selected.

In this application, I am creating a record of a customer repair in the
CRData table. Based upon the "Type" of unit returned, I want to lookup test
criteria in a separate table and append it to the appropriate customer repair
order in the CRData table.

This is the first time I've tried this sort of data manipulation and it's
been frustrating. I'm sure there must be a simple way of doing this but I
have yet to figure it out.

Thanks
Mike

 

[Douglas J. Steele]

strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "', " & _
"NumericField = " & lngNumericValue & ", " & _
"DateField = " & Format(dtmDateValue, "\#mm\/dd\/yyyy\#") & ", " & _
"Field2 = '" & txtOtherValue & "' " & _
"WHERE CRNumber = " & rptSN

 

[Mike W]

I tried a simplified version of your code below and am still not getting the
update in my table. I'd appreciate more ideas.

Set rsReport = New ADODB.Recordset

rptTable = "SELECT * FROM CRData"
rsReport.Open rptTable, cnCurrent, adOpenDynamic, adLockOptimistic

strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "' " & "WHERE
SerialNumber = " & rptSN

cnCurrent.Execute strSQL

Thanks,
Mike