Update data in table

[Guest]

In tbl1 I have two fields (fldID and fldQty). With the OnClick event
cmdButton on frm1 I would like to loop through all the records in tbl1 and
update fldQty based on the result of the following function.

Select Case ID
Case 1
fldQty = 1 * fldID
Case 2
fldQty = 3 * fldID
End Select

I would greatly appreciate what help might be offered.

 

[Alex White MCDBA MCSE]

Hi hank,

An update query would do the job here

e.g.

dim SQL as string
Select Case ID
Case 1
SQL ="Update MyTable set myField= (1 * fldID)"
Case 2
SQL ="Update MyTable set myField= (3 * fldID)"
End Select

if you are using access 2000 or newer then

currentproject.connection.execute SQL

will execute the query

now if what you were trying to achieve was if the id value in the field was
x then update the above queries would look like

Update MyTable set MyField = (1 * fldID) where id=1
Update MyTable set MyField = (3 * fldID) where id=2

and execute both of them one after another.

Hope that answers you question.