Control Source Question

[JDK9AB]

I have two list boxes and one text box. In general, I would like to set the control source of the text box as:

form.text1.controlsource = form.list1.column(0,i) & " - " &_ form.list1.column(0,i)

i is a variable determined by a loop.

If list1.column(0,1) = read and list2.column(0,1) = this, then my goal is for the textbox to read:

read - this

Now this is easily done if I set the control source manually by right-clicking on properties and setting the control source. However, I need to do it using code so that the variable i can be manipulated since the same row will not be displayed each time.

My only idea so far (that obviously didn't work) was to add more quotation marks. This is what I tried:

form.text1.controlsource = form.list1.column(0,i) & "" - "" &_ form.list1.column(0,i)

If anyone has any ideas, thanks in advance.

Justin

 

[scriverb]

Why don't you just change the value of i independent of this control source and let the control just do it thing. What are you going to do with i.

Please be a little more specific about what you are trying to do with these three controls.

Bob Scriver

 

[JDK9AB]

This is a small piece of a huge form, so I appologize if I haven't been specific enough.

Here is the general setup of the 3 items. The two list boxes are next to each other on my main form. They are populated by values input in another small form. Once the main form is complete, I click a command button. A long list of code is performed, and some of the code populates a form that will be eventually become a report. In that form is my textbox that I am wanting to populate.

I simply would like to combine the same two lines from both listboxes into the textbox with a dash in the middle. I use the variable i to allow myself to determine the line I need from each listbox in a previous set of code. However, I wasn't exactly specific on how I define the control source. I actually store the control source of the two listboxes and the rows I want out of them in a string. Then I set the textbox control source with that string. Therefore, I don't actually pass the i into the control source of the textbox and it really is not contributing to my general problem.

 

[scriverb]

Then I guess I don't understand the need to create this string of the representation of the cancatenation of the values. Just use it as is. Don't think of me a thick headed tonight but is this code going to change due to data in the record. I looks to be very static and the i value will pick off the correct data and cancatenate it together properly for you.

Enlighten me a little more please.

Bob Scriver

 

[JDK9AB]

Ok, lets just forget about the i. Obiously the rows defining the textbox are going to change. If they were going to stay the same, I wouldn't have to worry about using VBA for this issue. However, this is not the problem I'm trying to solve.

My problem is how to get the two values in the listboxes into the textbox with a dash in the middle. It's that simple. I am looking for different methods for populating the textbox with my data since I can't figure out a way to do it myself.

 

[scriverb]

Okay, now we can solve this for you. Are the two list boxes set to only one selection?(Multi Select property set to None) I am going to assume that it is. Please post the Row Source property for each of the text boxes. If there are more than one column in the Row Source please indicate the number of columns, column widths, and bound column property entries for both listboxes.

I will then get back with you with the VBA code to cancatenate the values.

Bob Scriver

 

[JDK9AB]

Both list boxes are have a multi select set to none. I don't set a row source indicator for the any of the boxes. They all have a row source type of value list. Both list boxes only have 1 column.

Thanks.

Justin

 

[scriverb]

I have this exact setup in a test form as you described and the control source for a txtControl is the following:
=[List1].[Column](0) & " - " & [List2].[Column](0)

This works great. As you make selections from either list box the txtControl changes with the following format:
xxxxxxx - xxxxxxx

Hope this is what you are looking for.

Bob Scriver

 

[JDK9AB]

Of course the control source works if you just type it in. However, what I have run in to is setting the control source using VBA because the row I need out of the list boxes varies. Since the control source contains quotes, when you store the string in VBA, it stores it without the quotes. Therefore, it will error out because what was entered as:
=[List1].[Column](0) & " - " & [List2].[Column](0)
becomes:
=[List1].[Column](0) - [List2].[Column](0)
This will obviously error if its the definition for the control source.

Thanks for your help Bob. I'm going to keep working on this.

Justin

 

[scriverb]

Either we are not communicating or I am terribly dense here and that has happened in the past. But, when you say:
because the row I need out of the list boxes varies. I can make no sense out of that. When the items is picked in a listbox the property Bound Column which is set to 1 selects the data from column 1 and the row selected and makes that the value of the listbox. No need to reidentify the row in code. Just refer to the listbox to get the value selected.

Actually, because there is only one column of data from the Value List the column property is not necessary because the list box has the Bound Column set to 1. Just referencing the list box provides the selection made by the user.

=[List1] & " - " & [List2]

My question still is why do you need to change this controlsource by code after you have typed in in correctly?



Bob Scriver

 

[JDK9AB]

I think we're not communicating properly. Let me add to the problem to show you why your solution, which will work in some cases, doesn't apply here.

Depending on the two list box entries, they will need to be displayed in one of 13 different text boxes. In addition to that, there can be multiple entries in the list box. Also, the conversion from the list box to the text boxes occurs in one set of code. Finally, I have 7 sets of the 2 list boxes and 7 sets of the 13 textboxes to match.

So, for example, say the list boxes have these values:
list box 1 list box 2
row 1: x1x1 x2x2
row 2: y1y1 y2y2
row 3: z1z1 z2z2

Each entry (row) will end up in 1 of 13 boxes. So, for example:

text box 1: x1x1 - x2x2
text box 2: y1y1 - y1y2
text box 3: z1z1 - z2z2

or:

text box 5: x1x1 - x2x2
text box 3: z1z1 - z2z2
text box 1: y1y1 - y2y2

This is the reason that I have to do everything in the code. If I don't do it this way, I can't gaurentee that values are in the right text box.

So, back to the original problem. How do I, using VBA if possible, take the same row from two list boxes and combine their values with a dash in the middle in a textbox. Is it possible to use the .value command? If so, how without erroring out? Etc. etc..

Justin Kohlman

 

[jebry]

Hi Justin!

Don't set the control source, just set the value and the text box will display what you want.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[JDK9AB]

Well Jeff, it turns out it was that simple. That was the first thing I had tried before, but unfortunately I got errors each time. Now it worked for me. I can't explain it, but thanks.

Bob, I wanted to thank you for the time and effort in trying to answer my question. It was very much appreciated.