How to programmatically change the currently selected row in a continuous form / listbox

[SparklySpartan]

Hi there,

I wanted to ask this question because I was curious if this is possible for both listboxes and full on continuous forms

The thought of how this might be useful came up when I wrote some code to add a new row to a continuous form. Of course, by "adding a new row", I mean add a new record in the underlying table that would be selected by the continuous form and then requery-ing it.

It would be nice for the newly created row in the form to automatically become the selected one, so that, after adding the new record, the user can go on manually entering the rest of the data not automatically populated there.

I'm also curious if this can be done for listboxes. Thanks in advance for the help!

 

[theDBguy]

Hi. I think it's possible for both scenarios, but they would require different approaches. For example, if the listbox is not a multi-select listbox, you could simply assign the new record to the bound value of the listbox. And for continuous forms, you will have to "navigate" the form to the new record, usually by using its ID.

 

[SparklySpartan]

Hi. I think it's possible for both scenarios, but they would require different approaches. For example, if the listbox is not a multi-select listbox, you could simply assign the new record to the bound value of the listbox. And for continuous forms, you will have to "navigate" the form to the new record, usually by using its ID.

Hi DBguy, thanks for your answer!

What you said about listboxes makes sense. Haven't been messing around with multi-select listboxes yet, but for a single select one setting it to null deselects whatever has been selected. So then, in the same vein, setting a listbox to the bound value of one of its rows will cause that row to be selected?

In the project I'm working on the bound column for every listbox is always the ID, which is always going to be unique of course, so I could see that working. What if the bound column wasn't always the ID though? (Not that I would agree with that design, lol, but just for kicks)

Can you elaborate a bit on navigating the form to the new record?

Appreciate it!

 

[theDBguy]

setting a listbox to the bound value of one of its rows will cause that row to be selected?

Yes, it should.

What if the bound column wasn't always the ID though? (Not that I would agree with that design, lol, but just for kicks)

As you said, that's not advisable, so they would be limiting themselves if they use that approach

You can see this problem come up when people use a non-unique bound column where they wonder why the value goes back to the first item when they were clearly selecting the second one.

Can you elaborate a bit on navigating the form to the new record?

It's similar to the approach of creating a combobox using the wizard and selecting the third option - find record.

Essentially, when you add a new record using code, save it's ID and then use it to "navigate" the form to that ID.

Hope that makes sense...

 

[SparklySpartan]

You can see this problem come up when people use a non-unique bound column where they wonder why the value goes back to the first item when they were clearly selecting the second one.

Didn't know that, too funny.

Essentially, when you add a new record using code, save it's ID and then use it to "navigate" the form to that ID

Sorry I should've been a little more specific. I can add a record and know the added record's ID, I'm just unfamiliar with the technique you're referring to to navigate to a row in a continuous form using its ID.

I tried setting the continuous form to the ID like you recommended for listboxes (didn't work, it was a long-shot anyways XD)

I looked at the form.CurrentRecord property of my form which looked promising but it appears to be read-only.

DoCmd.FindRecord also looked like it might do what I want, but its documentation was pretty illusive to what it actually does... so I figured I'd ask what technique you had in mind.

Appreciate you!

 

[theDBguy]

I'm just unfamiliar with the technique you're referring to to navigate to a row in a continuous form using its ID.

Try it like I said, add a new combobox to your form using the wizard and select the third option. Then, look at the code it creates and apply it to your situation.

DoCmd.FindRecord also looked like it might do what I want, but its documentation was pretty illusive to what it actually does... so I figured I'd ask what technique you had in mind.

That's exactly the technique used by the combobox wizard.