List Box

[Guest]

Hi

I have two list boxes which I am trying to use as part an email function for
an app I am creating. One list box is populated with names of current
customers and has the following row source

SELECT IIf(IsNull([tblCustomers]![LastName]),"x",[tblCustomers]![LastName] &
", " & [tblCustomers]![FirstName]) AS Name, tblCustomers.Company
FROM tblCustomers
WHERE (((tblCustomers.Email) Is Not Null))
ORDER BY IIf(IsNull([tblCustomers]![LastName]),"x",[tblCustomers]![LastName]
& ", " & [tblCustomers]![FirstName]);

I would like to be able to copy customers from this list box to the other
list box by selecting a customer and clicking a command button. Has anyone
ever done this before? I recall reading an article where someone done similar
but I cant remember where it is.

TIA

 

[Steve Schapel]

Edgar,

I can think of two approaches to this.

1. Make a table which will be the Row Source of the second listbox.
Use code on the command button to run an Append Query to add the
information to this table for the customer currently selected in the
first listbox. Requery the second listbox.

2. Add a Yes/No field to the tblCustomers table, and use this to select
which records are included in the Row Source of the second listbox. Use
code on the command button to run an Update Query to set the value of
this selector field to -1 for the customer currently selected in the
first listbox. Requery the second listbox.

By the way, as an aside, the word Name is a Reserved Word (i.e. has a
special meaning) in Access, and as such should not be used as the name
of a field or control or database object, even as an alias for a
calculated field in a query.

 

[PC Datasheet]

Add another field named Selected (Yes/No) to TblCustomers. Set the default
value to False and update your existing records to False. Create a duplicate
listbox on your form with the same rowsource. In the original listbox
modifiy the Where clause to:
WHERE (((tblCustomers.Email) Is Not Null)) And TblCustomers.Selected = False
In the new listbox, modify the rowsource to:
WHERE (((tblCustomers.Email) Is Not Null)) And TblCustomers.Selected = True

Put the following code in the Afterupdate event of Listbox1:
Me!Selected = True
Me!Listbox2.Requery
Me!Listbox1.Requery

Put the following code in the Afterupdate event of Listbox2:
Me!Selected = False
Me!Listbox1.Requery
Me!Listbox2.Requery

The listboxes will now act like the Wizard listboxes moving the selected
field back and forth between the listboxes.