Use listbox to display and select records on form

[JTBorton]

I'm very new to Access and I am trying to create a form to manage contact information in a Contacts data table. I would like to place a list box at the left side of the form with all of the names in the data table. When the user clicks on a name in the box, all of the record fields on the form will jump to that name. Likewise, if the user clicks the "next record" button, the listbox will change its list index to highlight that name. Can someone suggest how this might be done? Will it require VBA code? I have a bit of experience in Excel and Excel VBA, but am not sure how to approach this in Access, as I have recently discovered they have almost nothing in common.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[assets]

use a query this one based on a table called city.
SELECT City.CityID, City.City FROM City ORDER BY [City];

Never give up never give in.

There are no short cuts to anything worth doing

 

[assets]

This one for office application it is limited to 25 names.
Sub FDocument_close()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strPath As String
Dim doc As Document
Set doc = ThisDocument
strSQL = "SELECT LastName FROM Addresses ORDER BY LastName"
strPath = "G:\OMR\MRM\1.Management&Admin\Phone List\Phone Book Database.mdb"
Set db = OpenDatabase(strPath)
Set rst = db.OpenRecordset(strSQL)
Do While Not rst.EOF
With doc.FormFields("wfLastName").DropDown.ListEntries.Add Name:=rst(0)
End Withrst.Move
NextLoopSet db = NothingSet
rst = Nothing
End Sub

Never give up never give in.

There are no short cuts to anything worth doing

 

[JTBorton]

When I try to set the data control source of each form object to the query, it only displays "#Name?" when I run the form.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[assets]

In my example I was using data direct from a table. You can write a query with the fields you need and then select these.

Never give up never give in.

There are no short cuts to anything worth doing

 

[JTBorton]

Well, I created the query, but when I try to bind the data field from the query that I created, it will only display "#Name?" when the form runs.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[dhookom]

I would first create a subform based on your Contacts data table. Then, create a form that has no Record Source. Add a list box on the left side of the new form. The list box should display your contacts and be bound to the primary key field of your table. Name the list box something like lboContactID.

Set the Link Master/Child properties of the subform so that it synchronizes with the list box:
[tt][blue]
Link Master: lboContactID
Link Child: [primarykeyfieldname]
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[JTBorton]

Im not having much luck with it. When i set the Link Child to the [ContactID] (the key field) the list box becomes locked and will not allow me to select other names.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[dhookom]

A list box on a form should not be locked unless you set it to Locked or Disabled or set the form to not allow editing or something else. The list box should not have a cotrol source.

What are these properties:
Main Form
Record Source:
Editing properties...

List Box
Row Source:
Enabled:
Locked:

Subform
Record Source:
Link Master:
Link Child:


Duane
Hook'D on Access
MS Access MVP

 

[JTBorton]

Well, I've been manipulating it in different ways to try and make it work. Here is how I have it set so far. I tried to pretty-it-up to make it easier to read the actual data values you requested.

Main Form:
Record Source: Nothing, like you said.

List Box:
Row Source: qryContacts_ByFirstName (which is a query that simply gathers the contactID and combines the first and last name together in a "Sort Name" field. The first column is the Sort Name, and the second column of the query is the ContactID.)
Control Source: =[qryContacts_ByFirstName]![Source Name]
Row Source Type: Table/Query
Bound Column: 2 (The ContactID column of qryContacts_ByFirstName, or atleast I think that is what this is for...)
List Items Edit Form: frmContactsSubForm (The sub form)

Subform:
Source Object: frmContactsSubForm
Link Master: ContactID (from qryContacts_ByFirstName)
Link Child: ContactID (from tblContactList, the original data table)
Record Source: SELECT tblContactList.[First Name], tblContactList.University, tblContactList.[Last Name], tblContactList.Gender, tblContactList.[Home Phone], tblContactList.[Cell Phone], tblContactList.Email, tblContactList.Birthday, tblContactList.[Street Address], tblContactList.[Apt#], tblContactList.City, tblContactList.[Zip Code], tblContactList.[Out Of Town], tblContactList.[In College], tblContactList.[Degree / Certifications],


-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[PHV]

For me, the ListBox shouldn't have a ControlSource

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

PH's comment is correct. Plus you can't set a control source to a [queryname]![FieldName]. Your link master assumes the name of the list box is ContactID.

Duane
Hook'D on Access
MS Access MVP

 

[JTBorton]

Okay so I took the control source off of the list box and I have the ContactID value from the list box linked to the ContactID feild on the form. When I start the form, the subform displays information for the first person listed in the list box, but it will not change when I select a new name.

Main Form:
Record Source: SELECT qryContacts_ByFirstName.[Source Name], qryContacts_ByFirstName.ContactID FROM qryContacts_ByFirstName;
(It throws a fit if I don't put something here.)

List Box
Control Source: [Nothing]
Row Source: qryContacts_ByFirstName
Bound COlumn: 2

Subform
Record Source: SELECT tblContactList. bla bla bla....
Source Object: frmContactsSubform
Link Master: ContactID (From qryContacts_ByFirstName)
Link Child: ContactID (From tblContactList)

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[MajP]

You said

Link Master: ContactID (From qryContacts_ByFirstName)
Link Child: ContactID (From tblContactList)

should be

Link Master: [listBoxName]
Link Child: ContactID (From tblContactList)

where the listbox's boung field returns a contactID. Reread Dhookum's post of 27 Jun 10 23:09.

 

[JTBorton]

Holy smokes that got it! I guess I read Dhookum's post too fast and only saw the ContactID and not the full lboContactID. Thanks everyone!

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[JTBorton]

Okay so now that it is working I am finding another problem: The list box is filtered based on those who are not currently out of town. When I change a contact in the subform to being out of town, I want the name to be removed from the list box on the master form. I will handle these individuals in another form. How can I make the list box update when data in the subform is changed?

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[dhookom]

Requery the list box on the master form.

Duane
Hook'D on Access
MS Access MVP

 

[JTBorton]

Okay so I looked up how to requery in the microsoft help and it said to place Me.Requery in the GotFocus event procedure, which I did. Macros are enabled, but when I click the list box, nothing changes. I put a break point in the code to see if it executes; it never does. So I changed the command from the GotFocus event to the Click event. Still no go. Any clue what I might be doing wrong? Thanks again for all of your time and patience.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[dhookom]

You need to find the proper event which according to your earlier post

when data in the subform is changed

This should/could trigger code like:

  'I think this is correct and should be in some after update
  '  of the subform
  Me.Parent.lboContactID.Requery

Duane
Hook'D on Access
MS Access MVP

 

[JTBorton]

Your code causes the subform to go blank, but it does not remove the name from the list box. I found a less friendly work around by simply placing a refresh form data button on the master form. Wow, I feel like they have really taken away all of our freedom and forced us into little boxes with these access forms.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]