CODEWITHSUNDEEP
vbams-accessms-access-2010
Kaja
Kaja

Reputation: 3057

Linking result of a query that related to a Listbox value to textboxes?

I have a problem in MS Access and I don't know how can I solve this problem.

I have a listbox on my Form. If the use changes the value of the Listbox,a SQL query should be run and the result should be displayed is some Textboxes. For example:

The user select 'A' value From the list box then this query should be run:

SELECT XValue, YValue,Wert FROM Mytable WHERE Name='A' and ID=fzgID

then I want to display XValue, YValue and Wert in three textboxes. The problem is may be I have more than one pair for this combination (XValue, YValue,Wert).

How can I do that? How can I Link the list box and the query to the textboxes?

thank you so much

Upvotes: 0

Views: 1847

Answers (2)

Bobort
Bobort

Reputation: 3218

Depending on the structure of your data, I would do something like this. Set the table/query of your listbox to have all the data you want to display.

SELECT ID, Name, XValue, YValue, Wert FROM Mytable

In the Format tab of the property sheet of the listbox, set the column count to 2, and the column widths to 0";2" or your preference. This will hide the other values in the listbox rows.

In the three textboxes, set the control source like so.

Textbox1.ControlSource: =[Listbox1].[Columns](3)

Textbox2.ControlSource: =[Listbox1].[Columns](4)

Textbox3.ControlSource: =[Listbox1].[Columns](5)

You may need to adjust the numbers. A listbox has a property called Columns that enable you to access the values at different columns of the listbox. If you don't want to or cannot have all the data in the listbox, then you can use the DLookUp function in each textbox.

Textbox1.ControlSource: =DLookUp("XValue", "Mytable", "ID=""fzGID"" And Name=""" & [Listbox1] & """")

The reference to [Listbox1] will pull the value of the bound column. If the bound column is not the data you are looking up by then you will need to reference a column (i.e. [Listbox1].[Columns](2)).

Upvotes: 0

MiVoth
MiVoth

Reputation: 1012

Every ListBox has a ListBox_Click() procedure, which is run when an element of the listbox is clicked.

You can get, what you want, if you do something like this:

Sub ListBox1_Click()
    Dim valueOfListbox As String
    valueOfListBox = ListBox1.Value
    ' **** here comes  your code ****
    ' get the actual value from the listbox and do the query

    ' change the values of the textboxes to the result of the query
End Sub

If your listbox is named different than "ListBox1" you have to change the name of the procedure. For example if the listbox is named "blaBox" it has to be blaBox_Click().

Upvotes: 1

Related Questions