Using a listbox to filter a form

kfuegoj

New member
Local time
Today, 06:55
Joined
May 20, 2003
Messages
6
right I'm in need of a bit of help. I am pretty weak with my vba skills and I'm hoping someone can spell out how to accomplish the task I wish to accomplish.

Here's the scenario:

I have an Access 2002 db

table = tblcustomers
CustomerNO field = primary key
Lname = field I wish to populate my list box with
MainForm = frmcustomers
list0 = my list box

Presently I have have a listbox which is unbound that I have populated using the row source field in properties. (I would prefer to learn how to do this using ADO if possible)

What I would like is to be able to do is have the end user double click a last name and pull up the matching entry in a series of textboxes off to one side (I game for either using a subform or working with the main one for this display,which ever is easier) The user will then be able to update those fields.


Any help would be greatly appreicated.



Kelly Johnson MCP
Central City Concern
 
Create a query that uses criteria that refers to the listbox to select only the record selected by the listbox. Use the query as the RecordSource for the form.

Select .... From ... Where YourKey = Forms!YourForm!YourListBox;

In the double click event of the listbox, requery the form.

Me.Requery
 
Or you could do it the easy way:

strSource = "SELECT CustomerNo,LName FROM tblCustomers"
List0.Rowsource = strSource

You probably dont need the user to see the key field, don't wnat to confuse the poor sod, so set width of the first column of the list to zero. Also make sure that the first column is the bound column because you want to refer to the List0.Value when populating the form from the On_Click event.

You don't need the user to double-click. A single click on the listbox is OK.

In the code of the On_Click event, set up a recordset based on the fields you want in the unbound text boxes on your form. No need for a sub-form.

The reason I like to use a recordset is that it is easier to de-bug if something is going wrong. It is also easier to follow the logic of your code than trying to construct a long DLookup sentence.

If you need help with creating the recordset and populating your form, please come back.
 
A very simple example (less ADO) of what Summerwind and Pat are talking about it...

Regards,
Tim
 

Attachments

I want to thank you all for your help. I was making things much more difficult than they had to be. pono1 thank you for your example it helped me understand more about what I was doing wrong. For instance instead of just setting the list box source to the actual table, I defined specific fields using the SQL tool.

I didn't realize that I could simple refer to the current recordset with the me.recordsource. I thought I needed to create the adodb connection, populate a variable with the recordset etc... Of course I know someday I will eventually have to work with data that is outside of the mdb and would like to learn more about ado but for now this worked well .
 
I was womdering - how cold I replicate this but have it so that when you choose an item from the list box, another form opens? (the form fields are not on the same screen)
 

Users who are viewing this thread

Back
Top Bottom