goto next record based on listbox

[waq963]

Hi, Is it possible to code a next and previous button to go to the next/previous record based on the records in a listbox? Thanks.

 

[HiTechCoach]

I like to use this:

Record Navigation

 

[missinglinq]

One has to ask, why would you want to? What exactly are you using this listbox for?

 

[waq963]

I know it sounds weird but it would be helpful if possible. Basically i have a table that creates a primary when data is entered in another form and this table also has 2 foreign keys. If i could go to the next record via one of the foreign keys that would be helpful. I use a combo box where a user selects an item and the listbox shows all the records for that item in that table. But because it goes to the next record via the primary key it skips some records for that product. I hope i have made sense.

 

[boblarson]

How about this sample.

 

[waq963]

That is definately on the right lines. When you click on the next button can it also change the text fields data according to the data selected in the listbox? Because that would be exactly what i need.

 

[boblarson]

That is definately on the right lines. When you click on the next button can it also change the text fields data according to the data selected in the listbox? Because that would be exactly what i need.

Can you give a specific example of what you would be looking for it to do?

 

[waq963]

I have 3 tables involved. I have students and questions table which are decomposed int a studQ table. So i select a Student from a combo box and the listbox shows all the questions that student answered. The user can dblclick a row in the in the listbox and it will populate textboxes that show the answer and score for that question. So i want be able to go to the next/Previous record. Not via the primary key though. But for the questions that student has answered and thats it. I Again i hope this makes things clearer.

 

[boblarson]

The code I provided will move back and forth through the items in the list box and only the items in the list box. But, the functionality of it searching and finding the record is due to providing the proper key in the list box so that you can provide the key to the search that is looking for that record. So, you would have to have in the listbox the StudQ primary key so it can move to that record.

 

[waq963]

If i've understood you correctly i shoud put the StudQ primary in the listbox, which makes sense. But how could i get it so that i can populate the textboxes when you click next also?

 

[boblarson]

If i've understood you correctly i shoud put the StudQ primary in the listbox, which makes sense. But how could i get it so that i can populate the textboxes when you click next also?

Now, all it has to be is in the rowsource for the listbox and the number of columns property set to the correct number. It doesn't have to show and can have a width of 0" which will keep it hidden.

But the code would be sort of like:

    Me.YourListBoxNameHere.Selected(Me.YourListBoxNameHere.ListIndex + 1) = True
    YourListBoxNameHere_DblClick

You might need this too, but perhaps not:
Me.Requery

 

[boblarson]

FYI - Running the list box's double click event from the next button should perform the same functionality as if the list box was double-clicked for real.

 

[waq963]

Right i shall definately give this a go tomorrow as it is getting quite late for me. I will post how it went. Thanks for the help, hopefully i cant get it working.

 

[boblarson]

hopefully i cant get it working.

I hope that's a typo and you really hope you CAN get it working

 

[waq963]

Hi, i tried it and the code and it crashes on Set rs = me.Recordset.Clone. The error states the 'object variable or with object variable not set'. Also i cannot call listbox_afterupdate because it says object is not optional. I am using access 2007.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![lstQ], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[boblarson]

Can you post your database?