Updating ListBox and Search Functions

[lavey]

Hey guys me again
MS Access 2010

Please look at the attached picture to visual what I'm trying to say. Also, this is all one question but with multiple parts as they all tie in together.



I have made a a query in a list box to show all of the information relevant to the table (Equipment List). I have also created a search function so that the user can type in what he is looking for and it will be displayed.

Now what I want to know is how to:

1. When a user clicks/double clicks on one of the fields in the List box/search box, how can I get all of the information associated to that item to display in the fields above it. I can't work out any code for that and cannot find anything specific on the net. I want the information for the item that comes up to be editable ie. the user can alter the information

- I have tried using something along the lines of:
=[SearchResults].[column](5)

This however only displays exactly what is from that column, whereas I have close to 30 columns and I think I can only use/display 16 in my query? Additionally, using this method I wasn't able to edit the information.

1.1. In my ListBox, the headings/field names aren't showing above the fields. For example, there should be the word/heading "Equipment Type" above chemical injection and heat exchanger - how do I achieve this?

1.2. Is it possible to create a similar search function in this listbox that is similar to that of excel? Eg, so I can select a column in the listbox and search only characters specific to that column?

1.3. I want my listbox to update whenever I click the "save record" button. I believe I need to do a requery but i'm not sure where. I have

Me.SearchResult.Requery

on AfterUpdate and GotFocus events for both the Listbox and the form itself, and it is not updating.

Thanks guys

[June7]

1. look at the Column Heads property or use label controls above the listbox

2. columns of listbox are not selected, rows are selected

3. code in button event: Me.listboxname.Requery

Review: http://www.allenbrowne.com/ser-62.html

[lavey]

Thanks June,

I managed to work out the requery within minutes after I posted this, but thank you!

And also for the column heads, thanks for that don't know how I missed that.

Do you have any tips on the first point in my thread? that's the most problematic thing for me at the moment.

Many thanks

[June7]

Could bind the form to the table and use listbox as input for search criteria and use the code example from the referenced link.

If you don't want to bind form then will need lots of VBA code to save and retrieve data.

[amrut]

When a user clicks/double clicks on one of the fields in the List box/search box, how can I get all of the information associated to that item to display in the fields above it.

The following will work when a row in list box is clicked.
- Create a backup first so that if this doesn't work, you are not screwed !!
- Create a form and add the listbox you have to this form (you can copy and paste from here) and remove it from this form.
- Make sure the related field(listbox and form above) is the first column of list box preferable primary key.
- Add your existing form to the new form ( as sub form)
- On the data Tab of the sub form, set Link Master field to YourListboxName and child field to the corresponding field name on the form.

Now, whenever you click on a row in list box, related data will be displayed on the form