Text box Autofilling from 3 list box values.

[SMC]

Hello,

Description:

1 Text Box (bound)
3 List Boxes (unbound)

User will choose values for the 3 List Boxes.
List Box 1: Values 1-9
List Box 2: Values 1-9
List box 3: Values A-Z



Now, The Text Box needs to populate in such manner: "LB1""LB2"_"LB3"
I want to produce a 2 digit code, "space", then the LB3 value. ex; '12_A'

The Text Box value will then be saved into my records.

The values in this thread's example scenario have been simplified to focus on a method of achieving it.

I would like the Text Box to populate as I choose the value of List Box 3.

Thanks,

SMC

[jzwp11]

You would use this expression as the control source for the text box

=[LB1] & [LB2] & "_" & [LB3]

Now, since what is shown in the textbox is essentially a calculated value and in general, calculated values should not be stored; it is the list boxes that should be bound (each to a field) and the text box should be unbound. When you need to show the calculated value, you would just calculate it on the fly in queries, forms and reports.

[Missinglinq]

I have to disagree with you! While storing Calculated Fields is usually, quite rightfully, frowned upon, I think this case is one of the acceptable reasons! These three Listboxes have no intrinsic value, here, and are only being used to facilitate data entry. Storing three such Fields instead of a single Field just seems wasteful and unnecessary, to me.

I would, however, take into account the possibility that the users may not select from the Listboxes in the order the developer expects, and do something like this:

Private Sub LB1_AfterUpdate()
 Call ConCatFields
End Sub

Private Sub LB3_AfterUpdate()
 Call ConCatFields
End Sub

Private Sub LB2_AfterUpdate()
 Call ConCatFields
End Sub

Private Sub ConCatFields()
 If Nz(Me.LB1, "") <> "" And Nz(Me.LB2, "") <> "" And Nz(Me.LB3, "") <> "" Then
  Me.TargetTextBox = Me.LB1 & Me.LB2 & "_" & Me.LB3
 End If
End Sub

Also, not using the Control Source to do this will allow the user, if a mistake is made, such as selecting 'C' when they meant to select 'D,' to quickly edit the TargetTextBox to reflect this.

Just my opinion; to each his own!

Linq ;0)>

[jzwp11]

Missinglinq,

I see your point, perhaps I was reading into the post a little too much. I interpreted the list boxes of the post as a generic representations of 3 terms that actually have some meaning. I was working on another post where the poster was storing the individual terms as well as the concatenated value.

[SMC]

Thanks guys. Very helpful.

Missinglinq, another problem you have helped me with!

[Missinglinq]

...I was working on another post where the poster was storing the individual terms as well as the concatenated value.

Well, everything else aside, it is definitely a 'save either/or' situation, but not a 'save both' one!

SMC: Glad we could, once again, help!

Linq ;0)>