Dim statement question 2

[Kurt6905]

Hello. For the following VBA program, Access does not like my Dim statement. How do I assign the table (or recordset) I want converted to "rs?"

Thanks for any help.

Function DoTheConversion()
Dim table "Structural 2005" As rs

DoCmd.RunSQL "update rs set [Active] = [Amount]* 8.35 * [Formulation] * 0.01 where [Units] = 'GAL'"

DoCmd.RunSQL "update rs set [Active] = [Amount]/16 * [Formulation] * 0.01 where [Units] = 'OZ'"

DoCmd.RunSQL "update rs set [Active] = [Amount] * [Formulation] * 0.01 where [Units] = 'LBS'"

End Function

 

[PHV]

Like this ?
Function DoTheConversion()
Dim myTable As string
myTable = "[Structural 2005]"

DoCmd.RunSQL "update " & rs " set [Active] = [Amount]* 8.35 * [Formulation] * 0.01 where [Units] = 'GAL'"

DoCmd.RunSQL "update " & rs " set [Active] = [Amount]/16 * [Formulation] * 0.01 where [Units] = 'OZ'"

DoCmd.RunSQL "update " & rs " set [Active] = [Amount] * [Formulation] * 0.01 where [Units] = 'LBS'"

End Function

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

 

[Golom]

I think that this is what you're trying to do

Function DoTheConversion()
Const table As string = "[Structural 2005]"

DoCmd.RunSQL "update " & table & " set [Active] = [Amount]* 8.35 * [Formulation] * 0.01 where [Units] = 'GAL'"

DoCmd.RunSQL "update " & table & " set [Active] = [Amount]/16 * [Formulation] * 0.01 where [Units] = 'OZ'"

DoCmd.RunSQL "update " & table & " set [Active] = [Amount] * [Formulation] * 0.01 where [Units] = 'LBS'"

End Function

There are no recordsets involved in an UPDATE ststement.