Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 14th, 2005, 06:41 AM
Authorized User
 
Join Date: Jun 2003
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default loop through listbox and update record


I want to reassign salesperson associated with customers. I have an unbound form with two cboBoxes called cboFrom and cboTo. When cboFrom is selected it displays records associated with this salesperson in a multiselect listBox named lstDisplay. When I click a cmdButton I use the codes below to reassing to the salesperson selected in cboTo.

If I select a record or some records from lstDisplay all the records are being assigned to the salesperson in cboTo instead of only the ones I select from the multiselect listbox. What am I doing incorrectly.

Dim intCount As Variant

For Each intCount In lstDisplay.ItemsSelected

strSQL = "UPDATE tblAfterSoldFollowup SET SalespersonID = " & cboAssignTo & _
" WHERE SalespersonID = " & lstDisplay.ItemData(intCount)

MsgBox lstDisplay.ItemData(intCount)
CurrentProject.Connection.Execute strSQL

Next intCount
lstDisplay.Requery
 
Old August 5th, 2005, 03:43 AM
rgw rgw is offline
Registered User
 
Join Date: Aug 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I would assume that the table where you are reassigning the salesman is keyed with its own primary key, and this is what should be selected in the multi-select list box.

Your SQL string is selecting all the records for a particular salesman to be updated to the value in the AssignTo combo.

I created a small test db with a salesman table whose primary key is used to indicate the assigned salesperson in the FollowUp table. The listbox has its source set to a select query with a criteria of "SalesmanID = [cboFrom]" and the bound column is the primary key of the FollowUp table.

Your strSQL should have a criterion section of "WHERE FollowUpID = " & lstDisplay.ItemData(intCount)

Regards

Rod





Similar Threads
Thread Thread Starter Forum Replies Last Post
problem to fetch record using while loop Manoah Classic ASP Professional 9 July 31st, 2008 10:04 PM
T-SQL Update Loop Gyto SQL Language 3 February 2nd, 2007 05:52 AM
How to Update a Listbox on Different Form boxwalah C# 2 February 24th, 2006 12:22 PM
counting record numbers in a loop sniffer Classic ASP Basics 2 October 21st, 2004 11:49 AM
update records by the loop mateenmohd SQL Server 2000 2 July 16th, 2003 06:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.