Help Get Data From ListBox To TextBoxes

[ColinKJ]

Hi All,

I'm very new to Access.

I'm starting to build a form, and I want to get two fields of data from the selection in the ListBox, one into Text1, and the other into Text2.

The List0 is from a query BU_Contract_List Which has the Contract No & Contract Name.

Contract No is the BoundColumn. the Control Source forText1 is set to =[List0], and that works fine.

When the form opens, only Text1 & Text2 are visible, when you double click in Text1, List0 is displayed. When you double click a selection in List0, the BoundColumn value, Contract No is displayed in Text1, and the List0 is hidden again.

I want to know how I get the Contract Name in Column2 of the List0 into Text2.

Can anyone help please.

 

[Norie]

Colin

Why are you displaying the data twice?

Once in the listboxes and again in the textboxes?

 

[ColinKJ]

Hi Norie,

I'm not displaying it Twice.

When the form opens, there are 2 empty text boxes. One Contract No, the other Contract Name. When you Double click in the Contract No Textbox, the Listbox is made visible to enable a contract selection.

The Listbox contains a list of the available contracts, when I double click a selection in the ListBox, I want the selected Contract No to populate the Contract No TextBox, and the Contract Name to Populate the Contract Name TextBox, then the ListBox is hidden again until required again to select a different Contract.

 

[Norie]

Colin

What happens next?

Are you doing something with the values in the texboxes?

eg opening a form, running a query, printing a report

 

[ColinKJ]

Hi Norie,

The Textboxes will serve two purposes, first, in there own write to display the Contract Details of the contract under review, and second, the Contract No will be used in a query to populate a sub-form.

I haven't got there yet, but I will also have two further Textboxes to display the appropriate Account Manager Name, and the appropriate Business Unit to which the Contract belongs.

 

[Norie]

Colin

Sorry, but I don't understand why you have the listboxes and textboxes.

Can't you just use what's selected in the listboxes to display the other data and the subform?

Or even use comboboxes in place of the texboxes and listboxes.

Is there some reason the contract no etc have to be put in the textboxes?

Am I missing something here?

 

[ColinKJ]

Hi Norie,

As I said in my original post, I am new to Access. I am more used to creating sheet based forms in Excel, and I frquently use Textboxes to display information.

In this instance, apart from the Contract No, which will be used for a query,(and for which I shall be seeking help later), I am looking at displaying the Contrct No, Contract Name, Account Manager Name, and Business Unit on the Form.

They could be displayed in Lables or in Textboxes.

 

[Norie]

Colin

Apologies, I think I misunderstood and thought you had 2 listboxes.

Anyway if all you want to do is get the value from the 2nd column of a listbox you just need this.

List0.Column(1).Value

Columns have a 0-based index so 0 is column 1 and 2 is column 2 etc.

Sorry if I've confused you.

Still think using a combobox might be an idea though.

 

[ColinKJ]

Hi Norie,

I've just tried the following, put I get an error message"Oject Required":

Private Sub List0_DblClick(Cancel As Integer)
Text2.SetFocus
List0.Visible = False
Text4.SetFocus
Text4.Value = List0.Column(1).Value
 
End Sub

I get the Contract No from the List0 in Text2 because in the Text2.Control Source, I have =List0.

 

[Norie]

Colin

That's the bit I don't really understand.

Usually the ControlSource is a field in the table/query that is the record source of the form.

Anyway try this:

Private Sub List0_DblClick(Cancel As Integer)
   Text4.Value = List0.Column(1)
   Me.List0.Visible = False
End Sub

Note .Value was a typo in the original code I posted.