Solved Get value from first column of listbox (1 Viewer)

[stardustvega]

I am basically trying to create my own wizard using an Access form. On the first step, the user needs to make a choice between two types of events. Because this is a wizard, I don't want to store the value they choose in my database, but I *do* need to use the choice to call something else.

I am currently trying to do this with an unbound listbox. The listbox has two columns with one hidden (width = 0").

The listbox's name is EventTypeSelector.

This is my current code, which I want to run when a button is clicked.

Private Sub BtnChooseEventType_Click()
test = Me.EventTypeSelector.Column(0)
MsgBox (test)
End Sub

When I run this, I get an "Invalid use of null" error.

I have also tried this variation:

Private Sub BtnChooseEventType_Click()
test = Me!EventTypeSelector.ItemsSelected
MsgBox (test)
End Sub

That results in an "Wrong number of arguments or invalid property assignment" error.

(If this is a bad way to go about my end goal, I would welcome alternative suggestions too.)

 

[theDBguy]

Is it a multi select listbox? When you click on the button, is something selected in the listbox? Have you tried using the ItemData property?

 

[stardustvega]

Is it a multi select listbox? When you click on the button, is something selected in the listbox? Have you tried using the ItemData property?

It is not a multi-select listbox.

I thought that I had something selected since I set a default but apparently, that doesn't work to set the value and returns a null value. If I actually select the default item, it does work. That kind of sucks though...maybe I can set the value when the form loads instead of using the listbox's default value to do it.

I'm looking at the documentation on ItemData and I'll be honest, I'm scratching my head a little as it seems like the examples I can find all involve loops, which feels like it doesn't apply here. Do you have any examples of using it in this way I can look over for reference or can you point me to where I can look?

 

[GPGeorge]

"I thought that I had something selected since I set a default but apparently, that doesn't work to set the value and returns a null value"

As you just learned, setting a default only says to Access "IF and when a new record is started, use this as the default for this control." It would be a bad tangle to have a value automatically actually selected whether or not a new record was intended. You COULD, though, do that in this case if your intention is to always have something selected.

 

[stardustvega]

As you just learned, setting a default only says to Access "IF and when a new record is started, use this as the default for this control." It would be a bad tangle to have a value automatically actually selected whether or not a new record was intended. You COULD, though, do that in this case if your intention is to always have something selected.

I don't think it should matter in this case because this particular form isn't updating to a table so it isn't creating a new record at all. I'm basically using it to put together a wizard. Neither the form nor anything on it is actually bound to anything.

What's meant to happen is that the user is asked, "Do you want to create X or Y", and based on the answer, the form for X or the form for Y will open. Basically, I know what the most common answer is so I want that one selected by default.

EDIT: Sorry, I understand now--you were explaining why Access works that way. Yes, that makes sense now and I see why it would be designed that way. Thank you for explaining it as I was really confused about why it wouldn't work the way I was expecting.

 

[GPGeorge]

Well, that's not the point, though. However, I said that badly anyway. You're not "starting a new record" so much as you are dirtying the form in anticipation of doing something with the values in the controls. And the point is the same. That Default value is poised there, waiting, just in case you do decide you want something to happen with it.

As suggested, just assign the proper value to the control when the wizard starts and don't depend on the Default property. As a general rule, that fits the profile of controlling things rather than letting them happen, anyway. It's slightly more work, but you know for sure what's going to happen if you do it yourself.

 

[theDBguy]

Hi. To follow up on what George was saying, to automatically select something in the listbox, you could try assigning the value to it. For example:

Me.ListboxName = 0 'or whatever value you want from the bound column

Hope that helps...

 

[stardustvega]

Just for documentation purposes, this is what I ended up doing.

First, I made it so that when this form is opened, I set a default value for the listbox:

Private Sub Form_Load()
Me.EventTypeSelector = 1
End Sub

Second, I added in error handling for null values in case something went wrong, like this:

Private Sub BtnChooseEventType_Click()
test = Me.EventTypeSelector.Column(0)

If IsNull(test) Then
    msg = MsgBox("Do you want to create the default event type?", vbQuestion + vbYesNo + vbDefaultButton1, "Confirm Default")
    If msg = vbYes Then
        test = 1
    Else
        msg = MsgBox("Error: Please choose an event type.", vbCritical, "Error")
        Exit Sub
    End If
End If

MsgBox (test)

End Sub

That way it wouldn't throw an error even if the initial event didn't work.

 

[stardustvega]

Well, that's not the point, though. However, I said that badly anyway. You're not "starting a new record" so much as you are dirtying the form in anticipation of doing something with the values in the controls. And the point is the same. That Default value is poised there, waiting, just in case you do decide you want something to happen with it.

Sorry, I misunderstood it the first time and was confused. I understand what you mean now and I understand why Access uses the 'default' value in that way.

 

[GPGeorge]

Sorry, I misunderstood it the first time and was confused. I understand what you mean now and I understand why Access uses the 'default' value in that way.

No problem. In fact, I said it poorly the first time.