How should I write this VBA code? 1

[jane30]

Hi, All!

I have a VBA code that run various macros based on the field values[FIELD1] in TBL1:

Sub chkappend()
Dim db As Database
Dim rst As Recordset
Dim intF1 As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TBL1&quot
intF1 = rst.RecordCount

If intF1 = 0 Then
MsgBox ("No need to update.&quot
Else

Select Case rst![FIELD1]
Case Is = "AA"
DoCmd.RunMacro ("Mcr1.AA&quot

Case Is = "BB"
DoCmd.RunMacro ("Mcr1.BB&quot

End Select

End If

When all the criteria are met, only the first case statement was executed, i.e. only Mcr1.AA was run, and the Mcr1.BB was ignored even [Field1] has a value = "BB".

Basically, I need to read throught TBL1. If [FIELD1]="AA", run Mcr1.AA, if = "BB", run Mcr1.BB. And this VBA code is part of my AutoExec job. So, it won't tight with with any controls in the form or report.

What did I do wrong? Thank you for your time and help.

 

[Databaseguy]

This will need a little polishing, but here's the idea. This routine uses your recordset based on TBL1.


Sub chkappend()
Dim strMyMacro
Dim db As Database
Dim rst As Recordset
Dim intF1 As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TBL1&quot
rst.movelast
rst.MoveFirst
intF1 = rst.RecordCount

If intF1 = 0 Then
MsgBox ("No need to update.&quot
Else


Do While not rst.EOF
strMacro = rst.fields("Your Field&quot
DoCmd.RunMacro strMyMacro
rst.movenext
Loop

End If Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[KenG]

Try these changes;

Set db = CurrentDb
Set rst = db.OpenRecordset("TBL1&quot
With rst
If .RecordCount= 0 Then
MsgBox ("No need to update.&quot
Exit Sub
Else
.MoveLast
.MoveFirst
Do Until .EOF
Select Case ![FIELD1]
Case Is = "AA"
DoCmd.RunMacro ("Mcr1.AA&quot
Case Is = "BB"
DoCmd.RunMacro ("Mcr1.BB&quot
End Select
.MoveNext
Loop
End If
End With

You need to 'populate' the recordset then step through each record and check the value of ![FIELD1].
I like to use the With statement, It can make the code easier to read.

 

[jane30]

Thank you very much! You're right, I should loop through the whole table to continue the condition checking.

It works perfectly now.

 

[Guest_imported]

I'm trying to switch from FoxPro to Access and I'm having problems learning to code. What advise can you give a X-base programmer to become familiar with Access and VBA coding???

Thanks for your help

 

[Databaseguy]

1. Help in Access. In the VBE (Code window) hit F2 and you can see all of the objects. When you're writing code, double click a word (I.E. Database) and hit F1.

2. Microsoft Knowledge Base

3. MSDN

4. Sybex book. ISBN 0-7821-2370-8

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy