Solved Select column value in a listbox

[craigachan]

moke123 -Very nice idea, but should a certain filtered list exceed the number of buttons or form fields then what?

 

[Gasman]

Coincidentally, I was asking in another forum, how one would handle an option group, where the values came from a table, like one would do for a combo/listbox.?
I have only ever used them with hardcoded values like Male/Female, but with that subject these days, there now appear to be many more options
?

 

[craigachan]

SOLVED - Works Perfectly how I initially wanted it to. A couple of things to take note of.
1. It won't work if you set the listbox.columnwidths with code. you must do it thru the access application interface.
2. Following MajP's examples for FindAsYouTypeListbox, in the actual form code I had to public declare CLB as ColumnListBox at the top of the forms code

The pertinent form code to initialize the class module:

Private Sub Form_Load()
    Set CLB = New ColumnListBox
    CLB.Init Me.lstDx
.......

And my Click Code:

Private Sub lstDx_Click()
    'MsgBox CLB.ClickedColumn
    'MsgBox CLB.ClickedColumnValue
    Forms!BillingTxLU!Dx = CLB.ClickedColumnValue
    DoCmd.Close acForm, Me.Name
End Sub

Thank you everyone, especially MajP for the ideas and help.

 

[moke123]

I noticed in post# 19 where you posted your code, that you only had Option Compare Database. You should always also declare Option Explicit underneath that. You'll save yourself a lot of frustration.

1. It won't work if you set the listbox.columnwidths with code. you must do it thru the access application interface.

I haven't tested it but you may be able to, provided you do it prior to initializing the class. Try it.

 

[craigachan]

Moke123. Thank you. I'll give it a try. I'm still trying to understand Class Modules and how they work. You make a lot of sense.

 

[MajP]

I'm still trying to understand Class Modules and how they work

This thread provides some step by step on how to build your own classes to do similar things.

Creating Pseudo Custom Controls with Class Modules

This demo is in response to a recent thread on creating "custom controls" https://www.access-programmers.co.uk/forums/threads/is-it-possible-to-create-custom-controls.320996/ Unlike other development environments access does not have a way to make a user defined or custom control. However, but...

www.access-programmers.co.uk

 

[MajP]

@craigachan
FYI. When using an object you can do Declare/Initialize and Instantiation in one or two steps. I did it in one step you did it in two. This may have taken you time to figure out. This is done using the NEW keyword.

Two steps

Private CL as ColumnClickBox  ' Declare variable only

Private Sub Form_Load()
    Set CLB = New ColumnListBox 'Instantiation
     ...
end sub

One Step

Private CL as NEW ColumnClickBox  ' Declare and Instantiations

Private Sub Form_Load()
     ...
    'NO CODE NEEDED HERE to Set = New Columnlistbox
end sub