Write data from List box to table

[Guest]

Hi,

I've created a form for entering data. On the form I have a text box which
the user types in an account number. I've set the 'OnExit' event to check if
there is data in the filed and to requery the next field which is a list box.

The list box field has the row source set up to query another table based on
the value in the 'Account number' filed.

My problem is that I don't know how to get the new value in the list box to
write to the underlying table.

Please can anyone help???
A really easy to follow example would be appreciated.

Thanks in advance.

 

[Guest]

Usually a bound combo box is used for this scenario. (You can still use a
list box if you want, but I'll give this example with a combo box so you may
get an idea of why a combo box is usually used.)

The combo box's Row Source Property is set to a query with multiple columns,
which includes a primary key. For this example, I'll use this SQL statement:

Me!cboProducts.RowSource = "SELECT ProdID, ProdName " & _
"FROM Products " & _
"WHERE (ProdID = " & Me!txtAcctNum.Value & ");"

.. . . where cboProducts is the name of the combo box, ProdID is the primary
key in the query, ProdName is the name of the product, and txtAcctNum is the
name of the text box displaying the account number.

The combo box's Bound Column Property is set to 1, the Column Count Property
is set to 2, and the Column Widths Property is set to 0";1" so that the first
column is hidden.

The combo box's Control Source Property is set to the column in the form's
underlying table, the foreign key, which is often called the same name as the
foreign table's primary key. In this example, it's ProdID (the foreign key)
in the the form's underlying table.

Instead of using the account number text box's OnExit( ) event, the
OnAfterUpdate( ) would commonly be used for requerying other controls on the
form based upon the newest value in the text box. When the account number is
changed, the combo box will be requeried, and the user can select the product
name (that's the column that's showing) in the combo box, which automatically
stores the ProdID (that's the bound column), for this record.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Amy Blankenship]

I think what he means is that the List box contains items from a query that
pulls a list of options related to the query on the form. He wants to be
able to check items in the list box and insert records in a junction table
for each option/form item pair. Unchecking it would, by the same token,
delete those records. This is way more intuitive than trying to use
comboboxes in a continuous form.

However, examples posted on the web related to listbox functionality are
extremely verbose and hard to follow. And usually don't do what's described
above, so if you can't follow them you can't fix them. So what he's looking
for is someone to write code that encapsulates the above functionality or
share it if they already have it.

He's not alone in his desire...I've watched this thread in the hope someone
will post back with an actual solution. I'm trying to avoid taking hours to
write this code myself if it's already available.

-Amy

 

['69 Camaro]

Hi, Amy.

I've watched this thread in the hope someone will post back with an actual
solution.

I'm sorry you don't think that this is "an actual solution," but Wiggy
disagreed with you an hour before you posted your message:

http://www.microsoft.com/office/com...ding&mid=b80b78f3-36f8-424b-8116-2ec37e12ac92

Once a question has been marked as having an answer, few people generally
add to the thread, because there are far more questions being asked than
there are people able to respond. Other people are waiting for their
questions to be answered, too.

He wants to be able to check items in the list box and insert records in a
junction table for each option/form item pair. Unchecking it would, by
the same token, delete those records.

Writing the foreign keys to a transaction table is generally done via bound
forms/subforms. That way adding and deleting these records requires no
code, unless the developer wants to put add and delete buttons on the
form -- and the Button Wizard can easily handle that if one wants to avoid
writing any code.

However, examples posted on the web related to listbox functionality are
extremely verbose and hard to follow.

List boxes certainly are more complex than, say, a text box bound to a
field. If you find other people's explanations hard to follow, then you can
experiment on your own to see what happens when certain properties in the
list box are changed. Examine other people's work to see what they've done
with list boxes and you'll probably get several new ideas.

And usually don't do what's described above

Few people are going to use a list box to write multiple fields to a
transaction table, so I can understand few people discussing it in
newsgroups or posting such solutions on their Web sites.

So what he's looking for is someone to write code that encapsulates the
above functionality or share it if they already have it.

Wiggy's got what he needs for this particular list box, but feel free to
post a new question of your own outlining your desires if you can't get a
bound subform to do what you want.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Amy Blankenship]

For anyone who's interested, I finally found an example that sort of does
this
http://www.rogersaccesslibrary.com/download3.asp?SampleName=ListboxSubform.mdb.

HTH;

Amy