List box with two control sources

[tom1252]

Is it possible to get a list box to have two control sources?

 

[rodich]

Do you mean about two fileds to show in list box like the picture?
It is possible if you use Row Source Type as Table/Querry and Row Source as SQL
for exapmle "SELECT Products.ProductID, Products.ProductName, Products.UnitPrice
FROM Products
ORDER BY Products.ProductName;"

or

Row Source Type as Value list
Row Source as your values, for example "1;add;2;update;3;delete"
Column Count =2
Column Width = 1cm;2cm

 

[tom1252]

Yh i have this.

The problem is that the form is bound to a table.

I want the control source to be to two fields in the table. At the monet it is linked just to one so when entering data it only stores one of the value.

 

[smig]

if you mean from two different tables I guess you better create a qery that will join both tables

 

[tom1252]

I am rubbish at explainning things.

There are two fields that are in a list box and they are both in the same table.

 

[rodich]

.... just to one so when entering data it only stores one of the value.

Are you sure?
you can to access the items of list box by column and row
MsgBox Me.myList.Column(1, 1)
MsgBox Me.myList.Column(1, 2)
....
MsgBox Me.List90.Column(i, j)


Or (if I understand you wrongly and the upper idea is not proper!)

Your columns property are = 0cm;2cm and you mean that the value is only one

 

[tom1252]

I have a list box which has two coloumns one price and one item code. I want to display both of them. How can i set it up so that both of the two fields which are in the same table are both written to that table?

 

[smig]

It's realy hard to understand what you you want here.
they are both in the table, so why should you write them into the table ? or do you mean writing both of them into another table ?

writing both values into another table is not normaly advised, cause you creat a redundent data - you double your data.
though there are case you do want to do it, like copying the price together with the product into a table that store buyer data, cause you want to keep the price he paid in case price of product will be changed.
even for this there are some more "correct" ways to do it, though they are more omplex like keeping all the price history of the product.

now after all this philosopy if this is what you want - copy both columns into another table you can do it as rodich said -
first column = Me.myList.Column(0)
second column = Me.myList.Column(1)

use the change events of the List Box to copy the data to the correct fields, like:

me.FieldName = me.ListBox.Column(0)

but if you only want to display both column.... also as rodich said -
in the number of columns property set it to 2, and set the width of the columns, like 2;5
don't forget to bound your data to the correct column.

 

[wiklendt]

your listbox ought to be bound to a query - that is, it's record source ought to be a query of your design, not a table (the same goes for your form) - the FORM may already be bound, but you can bind a listbox independent of the form.

i am a little confused as to what you want to do with this listbox.... are you creating a record search list, and when you click you display the chosen record? if not, what are you hoping to achieve (i understand that you want to DISPLAY certain fields, but WHY?)

 

[wiklendt]

It's realy hard to understand what you you want here.
they are both in the table, so why should you write them into the table ? or do you mean writing both of them into another table ?

writing both values into another table is not normaly advised, cause you creat a redundent data - you double your data.
though there are case you do want to do it, like copying the price together with the product into a table that store buyer data, cause you want to keep the price he paid in case price of product will be changed.
even for this there are some more "correct" ways to do it, though they are more omplex like keeping all the price history of the product.

now after all this philosopy if this is what you want - copy both columns into another table you can do it as rodich said -
first column = Me.myList.Column(0)
second column = Me.myList.Column(1)

use the change events of the List Box to copy the data to the correct fields, like:

me.FieldName = me.ListBox.Column(0)

but if you only want to display both column.... also as rodich said -
in the number of columns property set it to 2, and set the width of the columns, like 2;5
don't forget to bound your data to the correct column.

i wouldn't recommend this without first knowing why you want to do what you are doing, and without knowing what you current table/relationship structure is.

 

[tom1252]

There is no change event on list box

ANd do i have to have two text boxes linked to the table so that data is inputed in that way.

Also where do enter the following

first column = Me.myList.Column(0)
second column = Me.myList.Column(1)

 

[boblarson]

In the After Update event of the listbox (listbox should be bound to one field and you can have a hidden text box bound to the other field). In the After Update event of the listbox, put:

Me.YourHiddenTextBoxName = Me.YourListBoxName.Columns(1)

to store the second column value (combo's and listboxes are zero-based for column numbering).

 

[wiklendt]

you'll have to upload your database. your description makes no sense... maybe the file itself will reveal more.

i suspect your tables are not normalised.

 

[boblarson]

you'll have to upload your database. your description makes no sense... maybe the file itself will reveal more.

i suspect your tables are not normalised.

It makes sense to me. He has two fields in a table and wants to store the product ID and the product price. My previous post should do that. And this is a case where it is okay to store the value because the price may change over time.

 

[wiklendt]

I have a list box which has two coloumns one price and one item code. I want to display both of them. How can i set it up so that both of the two fields which are in the same table are both written to that table?

what tables are you talking about (what are ALL the tables you are using in this problem) and how do they currently relate to each other?

sounds to me like you have probably an orders table (tblOrders, say) and you want to record which item is being ordered (by storing the item code) and how much it costs in each order (at the time of ordering).
am i close to what you want to be doing?

first, if you want to save two separate values to a table, you need two separate fields to save them to, otherwise you will make it WAY too complex to then do anything with that data.

second, you should be storing the itemID as a foreign key - NOT the product code - in your orders table.

third, you need to store the price separately, not refer to it from the items table, in case the price goes up in the future, but you want to know how much they paid in the past. i do this by making my price control in my orders form have the default value of the price control of the item.

so if you are ordering a particular item, the 'historical' or "paid" price of the item at time of ordering takes its value from the list price of the item.

so you can set your default value of paid price would be something like:

=[Forms]![frmOrders].[lstItems].Column(1)

(remembering that access often counts in base zero... so the first column is "0", the second is "1", etc...)
(edit: oops, fixed the code)

this will save the value from the list price column of the listbox to the paid price control of the order (which will save to the field in the table that is the final storage place of that control).

so you need two fields in your order table: ItemID (FK) and PricePaid.

 

[wiklendt]

It makes sense to me. He has two fields in a table and wants to store the product ID and the product price. My previous post should do that. And this is a case where it is okay to store the value because the price may change over time.

i agree with you bob, and i store past prices also in my ordering table, however my impression from the OP's description was that he was somehow trying to store data into a table that came from that very same table (circular reference? hence my inquiring into his table structure)... and not only that, but trying to store two things in one field (meaning that later he'll ask about how to separate the two values again so he can use them in something else, like a report to create invoices or another query....) (edit: so was just clarifying to him that he needed two fields to store two separate bits of data)

(edit2: please note it is extremely possible i'm wrong and should just butt out!) ;P

 

[smig]

It makes sense to me. He has two fields in a table and wants to store the product ID and the product price. My previous post should do that. And this is a case where it is okay to store the value because the price may change over time.

exactly what I said

but I also think it's not clear what he want to do -
once he said - I want to display both of them
next he said - both of the two fields which are in the same table are both written to that table?
saving them to that table makes no sense to me

I guess there are two questions here -
1. how to show both fields
2. how to save both field
right ?

 

[georgedwilkinson]

I cannot for the life of me imagine why you're doing this.

What is your business goal with regards to these/this field(s)/control sources?

 

[schmoe]

Is it possible to get a list box to have two control sources?

For posterity's sake, I will just offer that I faced a similar issue and wound up putting three listboxes side-by-side, all synched up to look and act like one. Each one sent its selection to whereever I wanted it to go, and all three were requeried on any one of them being selected (in my case, to update any changes, because the first click event was a field value toggle in an underlying table).