Find Record From Listbox

[jesusoneez]

I've enclosed a screenshot as it tells the story pretty well by istelf.

I would simply like to be able to click any item in the listbox (populated from a query simply showing all records in a table). Once an item is clicked in the listbox, I'd like the form to go to that record (this is to enable quick finding and deleting of records).

I've been playing for a while, and can't figure out how to make Access go to the record that's selected in the listbox.

Thanks for any help.

Steve

 

[Kempes]

Set the list box row source to have the record number in column one, then the field value in column two.

Column count = 2
Column width = 0cm;5cm for example.

This will allow you to select the record number from the list box although display the Field you require.

Add a command button called GO or SELECT or something similar.

On Click event enter this

Docmd.findrecord Yourlistboxnamehere

This should allow the user to select a value from the listbox, then hit the GO button to show that record.
This should work

 

[jesusoneez]

I've added this to a command button as suggested;

Private Sub cmdFind_Click()
    DoCmd.FindRecord lbxUserTypeLB
End Sub

But it results in the following error after selecting something from the listbox;

Run-time error '2162':

A macro set to one of the current field's properties failed because of an error in a FindRecord action argument.

Hitting "Debug" highlights the "DoCmd.FindRecord lbxUserTypeLB" line.

I was hoping to have the form find the record as you click the item in the listbox, but a command button would be OK as you suggested...not sure I've got the syntax right, and the code example in Access help is a bit confusing...

Note that there is only one field in the query that the listbox is referencing as they are all unique items, I didn't see the need in having a record number.

 

[Jibbadiah]

Even easier... select combo control... add to form... the wizard should offer the opportunity to "find a record on my form based on a value I select in my combo box"... choose this option.

J.

 

[jesusoneez]

I'll do that if I really have to, but there's only about 6 records, and only ever will be about six records, and I thought it'd be nice to have them all on-screen at the same time. I've got the form so when a record is added, it immediately shows in the listbox, and if a record is deleted it again updates the listbox.

 

[jesusoneez]

Even easier... select combo control... add to form... the wizard should offer the opportunity to "find a record on my form based on a value I select in my combo box"... choose this option.

Erm...creating a listbox with the wizard does this as well I've just discovered (much to my embarassment).

Thanks all!

 

[Jibbadiah]

... do you really need a "quick lookup" for just 6 records?! Sounds like a little over-engineering.

 

[jesusoneez]

I just don't like Access's in built navigation bar.



If someone happens to want to add another UserType, I want them to be able to see what's already there instantly, and have quick-click access to edit/save/delete. This is the best way, although it will be a rarely used screen.

On the other hand, it's also a learning experience and will come in handy for a lot of more often used screens.

Thanks again.