Converting from DBase3 to Access Module 3

[Kurt6905]

I was wondering if anyone could help. For years we have used DBase3 for a specific database process and are updating all dbase files to Access this year. Below is a sub-program we used for calulation and field replacement. This is DBase language - how would you write it in VBA for Access?

use Pesticide 2005 (database)
m_amt = 0
goto top
do while .t.
do case
case UNITS = "GAL"
m_amt = amount * 8.35 * formulation * 0.01
replace ACTIVE with m_amt
case UNITS = "OZ"
m_amt = amount/16 * formulation * 0.01
replace ACTIVE with m_amt
case UNITS = "LBS"
m_amt = amount * formulation * 0.01
replace ACTIVE with m_amt
endcase
skip
enddo
return

Any help is appreciated. Thanks.

 

[mhartman1]

Hello:

For your Do Case statement use Select Case in Access or VBA:
Dim m_amt as Double
Select Case Units
Case "Gal"
m_amt = Amount * 8.35 * Formulation * 0.01
??? = m_amt
Case "Oz"
m_amt = Amount * 8.35 * Formulation * 0.01
??? = m_amt
Case "Lbs"
m_amt = Amount * 8.35 * Formulation * 0.01
??? = m_amt
End select
'
I not sure what variable or control you have m_amt set equal to.
Regards
Mark

 

[Remou]

As far as I recall:

use Pesticide 2005 (database)
[blue]Set rs = CurrentDB.OpenRecordset("tblTable")[/blue]
m_amt = 0
goto top 
[blue]rs.MoveFirst[/blue]
do while .t. 
[blue]do while true 'However Do while Not rs.Eof might be better[/blue]
   do case
[blue]Select Case Units[/blue]
    case UNITS = "GAL"
[blue]Case  "GAL"[/blue]
          m_amt = amount * 8.35 * formulation * 0.01
          replace ACTIVE with m_amt  
[blue]rs.Edit
rs!Active=m_amt
rs.Update[/blue]      
    case UNITS = "OZ"
[blue]Case  "OZ"[/blue]
          m_amt = amount/16 * formulation * 0.01
          replace ACTIVE with m_amt
[blue]rs.Edit
rs!Active=m_amt
rs.Update[/blue]      
    case UNITS = "LBS"
[blue]Case  "LBS"[/blue]
          m_amt = amount * formulation * 0.01
          replace ACTIVE with m_amt
[blue]rs.Edit
rs!Active=m_amt
rs.Update[/blue]      
   endcase
[blue]End Select[/blue]
skip 
[blue]rs.MoveNext[/blue]
enddo
[blue]rs.MoveNext
Loop[/blue]      

return
[blue]May not be needed[/blue]

However, you may find a quite different way of doing this is better.

 

[PWise]

docmd.set warnings false

docmd.runsql "update [i]tablename[/i] set [i]fieldname[/i]= amount * 8.35 * formulation * 0.01 where units= 'gal'"

docmd.runsql "update [i]tablename[/i] set [i]fieldname[/i]= = amount/16 * formulation * 0.01 where units= 'oz'"

docmd.runsql "update [i]tablename[/i] set [i]fieldname[/i]= = amount * formulation * 0.01 where units= 'lbs'"

docmd.set warnings true