Loop query-based form to execute sql statements 1

[kenadys]

Is it possible to loop through records on a query-based form to execute sql statements to update a linked table?

I have a continuous form with a on click button that executes the sql statements. This process works fine, but
I have anywhere from 1500 to 3000 records that need to be updated. It's just repetitive and time consuming. Any suggestions would be appreciated. Thanks.

 

[PHV]

The UPDATE sql instruction has a WHERE clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kenadys]

Here is the SQL statements I have in the onclick event procedure of the button. So for each record on the continuous form I click this button to execute these statements. I am working with 2 tables a.) DBA_SD_SOLN_CODE_DETAIL_ACTUAL b.)updates (query underlying the form)

'COMPANY 01 SQL STATEMENTS
Dim STRSQL10 As String
Dim strsql11 As String
STRSQL10 = ("UPDATE DBA_SD_SOLN_CODE_DETAIL_ACTUAL SET SALES_PRICE = TXT_NP WHERE UNIT_COST = (SELECT MAX(UNIT_COST) FROM DBA_SD_SOLN_CODE_DETAIL_ACTUAL WHERE COMPany_CODE ='01' AND SOLN_CODE = TXT_SC)AND COMPANY_CODE = '01';")
strsql11 = ("UPDATE DBA_SD_SOLN_CODE_DETAIL_ACTUAL SET SALES_PRICE = '0' WHERE UNIT_COST <> (SELECT MAX(UNIT_COST)FROM DBA_sd_soln_code_detail_actual WHERE COMPany_CODE = '01' AND SOLN_CODE = TXT_SC) AND ITEM_CATEGORY <> 'C' AND COMPany_CODE = '01' AND SOLN_CODE = TXT_SC;")

'COMPANY 02 SQL STATEMENTS
Dim STRSQL20 As String
Dim STRSQL21 As String
STRSQL20 = ("UPDATE DBA_SD_SOLN_CODE_DETAIL_ACTUAL SET SALES_PRICE = TXT_NP WHERE UNIT_COST = (SELECT MAX(UNIT_COST) FROM DBA_SD_SOLN_CODE_DETAIL_ACTUAL WHERE COMPany_CODE ='01' AND SOLN_CODE = TXT_SC) AND COMPANY_CODE = '02';")
STRSQL21 = ("UPDATE DBA_SD_SOLN_CODE_DETAIL_ACTUAL SET SALES_PRICE = '0' WHERE UNIT_COST <> (SELECT MAX(UNIT_COST)FROM DBA_sd_soln_code_detail_actual WHERE COMPany_CODE = '02' AND SOLN_CODE = TXT_SC) AND ITEM_CATEGORY <> 'C' AND COMPany_CODE = '02' AND SOLN_CODE = TXT_SC;")



If COMP_CODE = "01" Then
DoCmd.RunSQL (STRSQL10)
DoCmd.RunSQL (strsql11)
UPDATED = 1

Else
If COMP_CODE = "02" Then

DoCmd.RunSQL (STRSQL20)
DoCmd.RunSQL (STRSQL21)
UPDATED = 1

End If
End If

My question...
Is there a way to automate this, instead of me clicking
through 1500-3000 records to run the sql. Is it possible
to put this into a loop.

 

[PHV]

You may browse the Me.RecordsetClone object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kenadys]

I tried this do until loop, but it stops after the
first record is updated. It won't move on to the next record(s).

Me.RecordsetClone.MoveFirst
If Me.RecordsetClone.RecordCount > 0 Then
Do Until Me.RecordsetClone.EOF
If COMP_CODE = "01" Then
DoCmd.RunSQL (STRSQL10)
DoCmd.RunSQL (strsql11)
UPDATED = 1
Else
If COMP_CODE = "02" Then
DoCmd.RunSQL (STRSQL20)
DoCmd.RunSQL (STRSQL21)
UPDATED = 1
End If
End If
Me.RecordsetClone.MoveNext
Loop
End If
Me.RecordsetClone.Close
End Sub

 

[PHV]

Add this line before if COMP_CODE...
Me.Bookmark = Me.RecordsetClone.Bookmark

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kenadys]

It totally worked. Thank You SOOOO Very Much!