Listbox selection to table

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hi

Using Access 2002 I have a list box on one form. The listbox lists Company
names.

What I want to achieve is that when a company name is selected in the list
box the company name is added to a table.

What is the best way to do this - an example of the code would be very much
appreciated.

TIA

Tom
 
is the form bound to that table? if so, just bind the listbox control to the
field, by setting the control's ControlSource property to the name of the
field.

if the form is *not* bound to the table, then.... is a table used as the
RowSource of the listbox control on the form? if so, then you have the
company names stored in a table - why are you trying to store them again in
another table. can you explain what you're trying to achieve?

hth
 
Tina

The listbox rowsource is unbound. The recordsource for the listbox is set
after company type is selected from a combobox on the same form.
The listbox is being used to select only some Campanies from the rowsource.
The selected company name is added to a tempory table. A email is then sent
to all companies within that tempory table.

Tom
 
The listbox rowsource is unbound. The recordsource for the listbox is set

i'll assume you meant to say "The listbox RecordSource is unbound. The
RowSource for the listbox is set...". in that case, you can open a recordset
based on the temp table, in a VBA procedure, to add the value from the
listbox to the table. if it's applicable to your process, you might consider
setting the listbox control to MultiSelect, and then looping through the
selected values to add all of them to the temp table. code for first
solution below.

hth

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("TempTablename", dbOpenDynaset)
' the "set rst..." above goes all on one line, regardless of line wrap in
this post.

rst.AddNew
rst("FieldName") = Me!ListboxControlName
' replace FieldName with the correct name
' of the field in the table, of course, and
' ListboxControlName with the correct
' name of the listbox control on the form.
rst.Update

rst.Close
Set rst = Nothing
 
Tina

The listbox, named Members has a recordsource of "SELECT MailMemberQ.Company
FROM MailMemberQ"

When using rst("Company") = Me!Members from your sample code

A null/blank entry is added to the temp table

What am I doing wrong?

Tom
 
Tom said:
Tina

The listbox, named Members has a recordsource of "SELECT MailMemberQ.Company
FROM MailMemberQ"

When using rst("Company") = Me!Members from your sample code

A null/blank entry is added to the temp table

What am I doing wrong?
Click to expand...

First get your terminology correct. ListBoxes don't have a RecordSource. They
have a RowSource and a ControlSource. The RowSource determines what you see in
the list. The ControlSource (if not blank) is the field in the *form's*
RecordSource that the ListBox will save its value to.
 
Tina

By using:

rst("Company") = Me!Members .Column(0)

instead of

rst("Company") = Me!Members

The company name is now being added to the temp table

Thanks for your help

Tom
 
oh, duh, was i half asleep, or what? of course i should have said
ControlSource rather than RecordSource in my post! <slaps self upside the
head>
 
you're welcome :)


Tom said:
Tina

By using:

rst("Company") = Me!Members .Column(0)

instead of

rst("Company") = Me!Members

The company name is now being added to the temp table

Thanks for your help

Tom
Click to expand...
 
Back
Top