Help with For Next statement

[dzirkelb]

I have a series of 20 if then statements I wish to run per record set...My current code works fine for one record setl; however, I wish to have it run for all of my records...here is a sample of one of the if then statements (they are all the same, just diff. fields)

If P4Fresh.Value = 1 Or P4AcceptableAppearance.Value = 1 Or P4ProperTemperature.Value = 1 Then
P4TotalPointsDocked.Value = 4
Else
P4TotalPointsDocked.Value = 0
End If

I basically want the following to happen:

for i =1 to acLast
if then statements (all 20 of them)
next

I will place the code on a command button which will run the code and update everything. This is possible with update query's, but, I don't want 20 of them lying around...I want it all to happen at one button click...any ideas?

thanks!

 

[Mile-O]

20 If Statements? All recordsets? What have you done to your poor database?

 

[dzirkelb]

haha

Oh, its horrendous..but I can't really think of any other way to do it. I am making a scantron form and putting it into an access database to put the data online...will save my company a ton in shipping and the forms alone; however, the way the form has been in the last 5 years is absolutely terrible...they fill in data if they are docked, which I think should be a yes / no or something like that. I really dislike the fact that they have 6 categories for one question...if one is filled in, then they loose the points for that question; however, they can add 1, 2, 3, etc...however many problems there were, but only loose the amount listed for that category...hence, why I have 20 if then statements. Do you have any idea what I'm talking about or have any idea on how to improve it?

 

[WayneRyan]

dz,

You're storing something that can be calculated, but ...

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDB
Set rst = dbs.OpenRecordset("YourTable")

While Not rst.EOF And Not rst.BOF
   '
   ' This code will see each record
   '
   rst.Edit  <-- Tell it we're gonna change values
   '
   ' This is where your 20 If statements go.
   '
   ' To refer to a field:
   '
   '    If rst!SomeField = "X" Then
   '       rst!SomeOtherField = "Y"
   '    End If
   '
   rst.Update  <-- Tell it to Update the record
   rst.MoveNext
   wend

Wayne

 

[dzirkelb]

unsure

I am unsure on where to put this code...I placed it on a command button and tried to compile, but received the following error:

Compile Error: user-defined type not defined

The following is the code I currently have:

Option Compare Database

Private Sub Command2_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblMasterEvaluations")

While Not rst.EOF And Not rst.BOF
rst.edit

If rst!S3CustomerService = 1 Then
rst!S3TotalPointsDocked = 20
End If
rst.Update
rst.MoveNext
Wend

End Sub

the portion with the error is dbs As DAO.Database

I placed this on a brand new blank form adn tried it...what am I doing wrong? and thanks for the code!

 

[WayneRyan]

dz,

You can put the code in any button on any form. It will access the
table directly.

To correct the error try this:

Get your code in Design View,
Select: Tools --> References
Check the "Microsoft DAO" reference and boost its priority as high as possible.

Then you can run it ... Or try this:

Put the word "Stop" at the first line of code.

When you run, it will STOP!

Ctrl-F9 will let you move the "program counter" to that line.
F8 will single-step
F5 will run to completion.

You can hover over a variable and its value will be displayed.

You can also View --> Immediate Window

Then type:

?rst!SomeField

and its value will be printed out.

Enjoy,
Wayne

 

[dzirkelb]

sweet!

so far so good!!! It works for one of my if then statements...now, I am going to put the other 20 in, and place it in my form, and i'll get back to make sure everything works correctly...thanks a ton!!!

 

[dzirkelb]

it all works great..thanks a ton!