Do While Loop

[rileyjm]

I'd like to create a Do While loop to help make my code a lot thinner. Below is the code I'm working with now. I currently have App1 - App90 and it continues to grow. I'd like to perform a Do While loop but I'm not sure how to accomplish this. App1 - 90 are all string values.


If ((Application = App1) Or _
(doc.FormFields("RBITApp1").Result = App1) Or _
(doc.FormFields("RBITApp2").Result = App1) Or _
(doc.FormFields("RBITApp3").Result = App1) Or _
(doc.FormFields("RBITApp4").Result = App1) Or _
(doc.FormFields("RBITApp5").Result = App1) Or _
(doc.FormFields("RBITApp6").Result = App1)) Then

DoCmd.RunSQL "UPDATE [tbl_RIF-App Info] SET [" & App1 & "] = 0 WHERE [DM #] =" & DM

Else: ![BDS-ST] = Null

End If


If ((Application = App2) Or _
(doc.FormFields("RBITApp1").Result = App2) Or _
(doc.FormFields("RBITApp2").Result = App2) Or _
(doc.FormFields("RBITApp3").Result = App2) Or _
(doc.FormFields("RBITApp4").Result = App2) Or _
(doc.FormFields("RBITApp5").Result = App2) Or _
(doc.FormFields("RBITApp6").Result = App2)) Then

DoCmd.RunSQL "UPDATE [tbl_RIF-App Info] SET [" & App2 & "] = 0 WHERE [DM #] =" & DM

Else: ![SST] = Null
End If

If ((Application = App3) Or _
(doc.FormFields("RBITApp1").Result = App3) Or _
(doc.FormFields("RBITApp2").Result = App3) Or _
(doc.FormFields("RBITApp3").Result = App3) Or _
(doc.FormFields("RBITApp4").Result = App3) Or _
(doc.FormFields("RBITApp5").Result = App3) Or _
(doc.FormFields("RBITApp6").Result = App3)) Then

DoCmd.RunSQL "UPDATE [tbl_RIF-App Info] SET [" & App3 & "] = 0 WHERE [DM #] =" & DM

 

[vbaInet]

You're performing different actions in different IF blocks... here:

Else: ![BDS-ST] = Null
Else: ![SST] = Null

So I don't think looping will help you in this circumstance.

Where are the values of those 90+ variables coming from?

 

[MarkK]

You can do this...

(doc.FormFields("RBITApp1").Result = App1) Or _
(doc.FormFields("RBITApp2").Result = App1) Or _
(doc.FormFields("RBITApp3").Result = App1) Or _
(doc.FormFields("RBITApp4").Result = App1) Or _
(doc.FormFields("RBITApp5").Result = App1) Or _
(doc.FormFields("RBITApp6").Result = App1)

... in a function. If one of those is true the whole thing is, so ...

function AppExists(doc as Something, app As SomethingElse) as boolean
  dim i as integer
  for i = 1 to 6
    if doc.FormFields("RBITApp" & i).Result = app then
      AppExists = True
      Exit For
    end if
  next
end function

So you're down to ...

If Application = App1 Or AppExists(Doc, App1) then 
  currentDb.Execute _
    "UPDATE [tbl_RIF-App Info] " & _
    "SET [" & App1 & "] = 0 " & _
    "WHERE [DM #] = " & DM
Else
  ![BDS-ST] = Null
End If

And it looks like the only thing that differs in those blocks is the App and the Field "![DBS-ST]", so one more loop and you're done.
But all that repetition suggests that you're working with columns instead of rows, and working with rows would be way easier.

 

[boblarson]

I currently have App1 - App90 and it continues to grow

From that statement and what your code looks like, it sounds to me like a very non-normalized database. You should not have repeating fields like that. They should be records (ROWS) in a table, not columns.

 

[rileyjm]

You all provided excellent feedback. This is a database I inherited so it's very un-normalized. I'm doing my best to clean it up w/o affecting the table structure too much. I was able to get the for loop to work as suggested which really cuts down on the amount of code to maintain. Thanks again.

 

[gemma-the-husky]

you may already have this sort of thing

dim x as long
for x=1 to 90
   fields("Base" & x) =  .....
next

or with while

dim x as long
dim max as long

max=90
x=0

while x<= max
   fields("Base" & x) =  .....
   x = x+1
wend