Add item to table field Based on another Field

[mcbigj]

Hello,
What I need to do is the following:

I've added an extra number field to an existing Access table. I need to loop through the table and based on an existing numerical fields value, perform a calculation on the value and place the new value into the new number field.

So literally Update the table row with the new value.

I'm thinking I need to do VBA and loop through a recordset

eg.

if Field1 = number then
do calculation1
write to newfield
Recordset.MoveNext
End if

I just cannot figure the syntax.

Any ideas would be very helpful.

Thank you.

John

 

[flyover789]

should be no problem, but why not simply use a query with a calculated field?

iif(Field1 = number; calculation1) as newfield

HTH,
fly



[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[PHV]

An SQL way:
UPDATE yourTable
SET newfield = Some calculated expression
WHERE Field1 = number

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mcbigj]

I have to do one of many calculations based on that number, so it would be an:

If item = val1 then
docalc1
elseif item = val2 then
docalc2

elseif item = val3 then
docalc3
endif

and the docalc is going to be a few lines of code in a function, so I thought I'd need to do this in VBA mod. What do you think?

Thanks

 

[mcbigj]

Thanks, PHV, but what I need to know is how to Access the ACCESS Table I want to work with in VBA and then to loop through it, do i need to put it in a recordset, or what?

What I really need is the correct Syntax for this.

Thanks again.

 

[PHV]

UPDATE yourTable
SET newfield = SomePublicFunction(needed fields list)
WHERE Field1 = number

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mcbigj]

PHV, I don't need the SQL syntax, what I'm looking for is after I hit a button on the form, the Code in the Mod for accessing the MS ACCESS tbl and then checking the value and then running the function if necessary.

sorry if I'm confusing here. I need to see something like

Dim rs as new recordset
rs = Me.RecordSource

do while not rs.eof
Movenext
loop

I can't seem to get my Access table into a recordset so that I can play with it row by row.

Thanks again.

 

[PHV]

Replace this:
Dim rs as new recordset
rs = Me.RecordSource
By this:
Dim rs As New DAO.Recordset
Set rs = Me.RecordSetClone

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mcbigj]

Thank you. I will give this a try