Using Loop to update a table

  • Thread starter Thread starter Carlos1815
  • Start date Start date
C

Carlos1815

I have a table with 18 fields I need updated whenever a control on a
continuous form is added, deleted or changed. I use this code
attached to a "Refresh" command button:

*********************************************
Dim strSQL[1] As String
Dim strTitle[1] As String

strTitle[1] = IIf(IsNull(DLookup("TopicTitle", "tblTopicTitle", "stg =
[1]")), "", DLookup_("TopicTitle", "tblTopicTitle", "stg = [1]"))

strSQL[1] = "UPDATE TopicTitle_AW SET TopicTitle[1] = '" & strTitle[1]
& "'"

DoCmd.RunSQL strSQL[1]
*********************************************

However, I need to make 18 copies of this code (and numbered
accordingly -- the numbers in the brackets are 1 thru 18) cover all 18
fields. This in and of itself is not bad; in fact, it works fine.
However, for every field that gets updated, a popup window asking to
to proceed comes up that I have to press "yes" for 18 times. Is there
a way I can use a loop function so I only have to press the Refresh
button once and Access will automatically update all 18 fields? I've
looked a few different methods and I can't get it to work like I want.

Thanks in advance!

Carlos
 
You don't need 18 copies of the code. You just need to execute the code 18
times and make changes for each iteration:

Dim strSQL As String
Dim strTitle As String
Dim lngX as Long

For lngX = 1 To 18
strTitle = Nz(DLookup("TopicTitle", "tblTopicTitle", "stg = [" &
lngX & "]"),vbNullstring)

strSQL = "UPDATE TopicTitle_AW SET TopicTitle[1] = """ & strTitle &
""""
Currentdb.Execute strSQL, dbFailOnError
Next lngX

Note the replacement of the RunSql. the Execute method is faster because it
bypasses the Access UI and goes directly to Jet and because it bypasses the
UI, it doesn't trigger the warning message. But, be sure you put an error
handler in your e code so if the Execute fails, it will error out.
 
Back
Top