rst loop versus For Each

[auujxa2 via AccessMonster.com]

I'd like to use the values from rst (DeptQry) instead of the items selected.
Here is my current code for the items selected. Please let me know how to
change it from the items selected to DeptQry results. Thank you in advance.

Dim strSQL As String
Dim varItem As Variant
Dim frm As Form, ctl As Control
Set frm = Forms!frmSetup
Set ctl = frm!lstMyDepartments
Set db = OpenDatabase("F:\Vendor Matrix\Data\Source\Maintenance Database -
F09\MENS Fall FY09 Vendor Maintenance.mdb")
Set rstDate = db.OpenRecordset("MasterTbl")

For Each varItem In ctl.ItemsSelected

strSQL = "DELETE * FROM [MasterTbl] IN 'F:\Vendor Matrix\Data\Source\
Maintenance Database - F09\MENS Fall FY09 Vendor Maintenance.mdb'" & _
" WHERE [Department #] = " & ctl.ItemData(varItem) & ""

DoCmd.RunSQL strSQL

Next varItem

Else
End If

DoCmd.Quit

End Sub

 

[Marshall Barton]

I'd like to use the values from rst (DeptQry) instead of the items selected.
Here is my current code for the items selected. Please let me know how to
change it from the items selected to DeptQry results. Thank you in advance.

Dim strSQL As String
Dim varItem As Variant
Dim frm As Form, ctl As Control
Set frm = Forms!frmSetup
Set ctl = frm!lstMyDepartments
Set db = OpenDatabase("F:\Vendor Matrix\Data\Source\Maintenance Database -
F09\MENS Fall FY09 Vendor Maintenance.mdb")
Set rstDate = db.OpenRecordset("MasterTbl")

For Each varItem In ctl.ItemsSelected

strSQL = "DELETE * FROM [MasterTbl] IN 'F:\Vendor Matrix\Data\Source\
Maintenance Database - F09\MENS Fall FY09 Vendor Maintenance.mdb'" & _
" WHERE [Department #] = " & ctl.ItemData(varItem) & ""

DoCmd.RunSQL strSQL

Next varItem

Else
End If

DoCmd.Quit

End Sub

You'll have to explain what the DeptQry query is doing and
how it relates to the varItem values.

BTW, your code could be "cleaner":

strSQL = "DELETE * FROM MasterTbl & _
" WHERE [Department #] = " & ctl.ItemData(varItem)
db.Execute strSQL

 

[auujxa2 via AccessMonster.com]

the reason why I have the location of the database, is because I'm deleting
values selected in the lst, from a table in another database.

as to answer your other question:

DeptQry has only 1 field - Department #. which is the same value that the
list is pulling.

so I want the field: department # from DeptQry, to take the place of ctl.
ItemData(varItem) - which is the value from a multi-select list

I'd like to use the values from rst (DeptQry) instead of the items selected.
Here is my current code for the items selected. Please let me know how to

[quoted text clipped - 25 lines]

End Sub

You'll have to explain what the DeptQry query is doing and
how it relates to the varItem values.

BTW, your code could be "cleaner":

strSQL = "DELETE * FROM MasterTbl & _
" WHERE [Department #] = " & ctl.ItemData(varItem)
db.Execute strSQL

 

[auujxa2 via AccessMonster.com]

I'll give you somebackground. When someone selects their departments from a
list, I want those selected departments to open the source database table
(MasterTbl), and delete those department records.

The code that I have run after that, which is not posted on this thread,
appends the new department data to the master table after it's deleted.

I want whatever values that are in deptqry to be deleted from mastertbl,
instead of the selections from the lst, for reasons that would be too long to
type

I'd like to use the values from rst (DeptQry) instead of the items selected.
Here is my current code for the items selected. Please let me know how to
change it from the items selected to DeptQry results. Thank you in advance.

Dim strSQL As String
Dim varItem As Variant
Dim frm As Form, ctl As Control
Set frm = Forms!frmSetup
Set ctl = frm!lstMyDepartments
Set db = OpenDatabase("F:\Vendor Matrix\Data\Source\Maintenance Database -
F09\MENS Fall FY09 Vendor Maintenance.mdb")
Set rstDate = db.OpenRecordset("MasterTbl")

For Each varItem In ctl.ItemsSelected

strSQL = "DELETE * FROM [MasterTbl] IN 'F:\Vendor Matrix\Data\Source\
Maintenance Database - F09\MENS Fall FY09 Vendor Maintenance.mdb'" & _
" WHERE [Department #] = " & ctl.ItemData(varItem) & ""

DoCmd.RunSQL strSQL

Next varItem

Else
End If

DoCmd.Quit

End Sub

 

[Nicky]

....
auujxa2 via AccessMonster.com ha scritto: