How do i allow users to select a item for a listbox and then display related records

[xwnoob]

Hi,

Currently i have a listbox that has 9 columns. I want to cut it down so im intending to have two listboxes. First list box displays BorrowerID and then after making a selection the second listbox will display the child records that borrower has, which are the contact parties.


Borrower has a one to many relationship with contact party.

 

[John Big Booty]

You could use something along the lines of;

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "YourFormNameToOpen"
    
    stLinkCriteria = "[BorrowerID]=" & Me![BorrowerID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

BorrowerID must be held in the Bound column of your List Box otherwise use;

stLinkCriteria = "[BorrowerID]=" & Me.ListBoxName.Column([B][COLOR="Red"]x[/COLOR][/B])

Where x represents the column number of the column in the List Box that hold BorrowerID. Remember that the columns in a List box or Combo are numbered from zero on up.

 

[John Big Booty]

You could put that code in the On Double Click event of your List Box

 

[xwnoob]

You could put that code in the On Double Click event of your List Box

Sorry john what i meant was these two listboxes are on the same form. THe second listbox will be empty at first but once the user selects a value in listbox1 then listbox2 will be populated with the related child records

 

[JANR]

Sounds like you shuld be searching for the concept of Cascading combobox

Like this one: http://baldyweb.com/CascadingCombo.htm

JR

 

[xwnoob]

Sounds like you shuld be searching for the concept of Cascading combobox

Like this one: http://baldyweb.com/CascadingCombo.htm

JR

Thx i will try and see if it works

 

[xwnoob]

Hey i have tried using this code:

Private Sub listDrawdown_AfterUpdate()
With Me![ListLender]
If IsNull(Me!listDrawdown) Then
.RowSource = ""
Else
.RowSource = "SELECT [FacilityName],[TypeFacility] " & _
"FROM tblFacilitySub " & _
"WHERE [BorrowerIDFK5]=" & Me!listDrawdown
End If
Call .Requery
End With
End Sub

However, my lists returns all the values regardless of what i select, which is not what i want. I want to select Facilityname and typefacility based on a specific ID i selected.

 

[JANR]

See attached DB for an example of cascading list/combobox

JR

 

[xwnoob]

Hi Janr,
So far its looking good but how do you modify the code so that the cascading listbox would refer to another listbox in a subform?

Is it FormName.ListName.Requery ???

 

[JANR]

Bookmark this page: http://access.mvps.org/access/forms/frm0031.htm

The syntax would be:

Forms!SubformControl.Form.Listboxname.Requery

JR

 

[xwnoob]

Bookmark this page: http://access.mvps.org/access/forms/frm0031.htm

The syntax would be:

Forms!SubformControl.Form.Listboxname.Requery

JR

Hi,
My code still does not work. I dont really need to refer to any control so i just eliminated that part. Please help! The code is:
Forms!DrawdownLenderCommitmentsubform.Form.ListLender.Requery

 

[JANR]

Sorry about the delay, it was a slight error in the syntax I gave you, should have re-read the link

Did you look at the link I gave?

To refrence a control on a subform you can use the full form refrence or use Me. syntax

Full form refrence:

Forms!NameOfMainForm.NameOfsubformControl.Form.NameOfControl.Requery

Or:

Me.NameOfSubformControl.Form.NameOfControl.Requery

Substitue anything marked in red with your own names, the rest leave it as they are.

JR

 

[xwnoob]

Sorry about the delay, it was a slight error in the syntax I gave you, should have re-read the link

Did you look at the link I gave?

To refrence a control on a subform you can use the full form refrence or use Me. syntax

Full form refrence:

Forms!NameOfMainForm.NameOfsubformControl.Form.NameOfControl.Requery

Or:

Me.NameOfSubformControl.Form.NameOfControl.Requery

Substitue anything marked in red with your own names, the rest leave it as they are.

JR

Thx it worked its just that the nameofsubformcontrol or something related to control always confuses me.

 

[JANR]

No problem

JR