Cascading Listbox Controls (1 Viewer)

[nfi2020]

Hello,

I'd be very grateful for some help, here.

I'm floundering about in Access, based on what I can remember of VB and SQL from twenty years ago, so not a good start...

I have a form which has two ListBox controls on it - the first, lstModule is populated from a local database when the form is opened, using the following RowSource property:

SELECT module.ID, module.code, module.title
FROM [module]
ORDER BY module.[code], module.[title];

I have a second ListBox control on the same form which isn't bound to a table, called lstAssessment.

In the AfterUpdate event of lstModule, I have the following code/expression:

=[lstAssessment].[RowSource]="SELECT id,title,description FROM assessment WHERE moduleID = " & [lstModule].[Value]

The functionality I'm going for is that, when you select an item in lstModule, lstAssessment updates itself.

The problem is that lstAssessment never updates and is always empty. I've tried putting the following in lstModule_onMouseUp to force lstAssessment to update:

=[lstAssessment].[requery]

...but with no effect.

Through extensive msgbox debugging, I have found that the correct value is being passed by [lstModule].[Value] and that, whilst I can pull values back from lstAssessment in the lstModule events, I can't seem to push values onto it (e.g. I can retrieve the defaultValue property, but can't set it).

If anybody can explain what I'm doing wrong, I'd be very grateful.

Thanks

 

[bob fitz]

You say you are using:
=[lstAssessment].[RowSource]="SELECT id,title,description FROM assessment WHERE moduleID = " & [lstModule].[Value]

in the AfterUpdate event of the first listbox (lstModule). Try:
Me.[lstAssessment].[RowSource]="SELECT id,title,description FROM assessment WHERE moduleID = " & Forms!YourFormName.[lstModule]

Be sure to put it in the forms code module. The "=" sign that you used suggests to me that you are putting it in the controls property sheet alongside of the AfterUpdate property.

 

[Gasman]

I would have thought you would use

Me.lstAssessment.RowSource = "SELECT id,title,description FROM assessment WHERE moduleID = " & Me.[lstModule]

Here I am using a listbox to select data for a report, so if you are using a listbox, what benefit does that have over a combo if only selecting one item.?

Private Sub cmdReport_Click()
On Error GoTo cmdReport_Click_Err

Dim ctl As Control
Dim varItem As Variant
Dim strParam As String, strRptQuery As String

Set ctl = Me!lstCrew
' Now select what records from listbox
If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
        strParam = strParam & ctl.ItemData(varItem) & ","
        'Debug.Print ctl.Column(2, varItem) 'Use to get any column in row in format column,row
    Next varItem
  Else
    MsgBox ("At least one name required")
    Exit Sub
End If

' Need to pass the criteria as a string
strParam = " IN (" & Left(strParam, Len(strParam) - 1) & ")"

Debug.Print strParam
strParam = "[QryServed].[Crew]" & strParam
DoCmd.OpenReport "rptServedWith", acViewReport, , strParam


cmdReport_Click_Exit:
    Set ctl = Nothing
    Exit Sub

cmdReport_Click_Err:
    MsgBox Error$
    Resume cmdReport_Click_Exit

End Sub

 

[nfi2020]

You say you are using:
=[lstAssessment].[RowSource]="SELECT id,title,description FROM assessment WHERE moduleID = " & [lstModule].[Value]

in the AfterUpdate event of the first listbox (lstModule). Try:
Me.[lstAssessment].[RowSource]="SELECT id,title,description FROM assessment WHERE moduleID = " & Forms!YourFormName.[lstModule]

Be sure to put it in the forms code module. The "=" sign that you used suggests to me that you are putting it in the controls property sheet alongside of the AfterUpdate property.

Hi Bob,

thanks for your help with this.

I am, indeed, putting my code in the controls property sheet, not the form code module - I want the second listbox to update only when the first listbox is changed, not when anything else on the form happens - clearly, I haven't really understood how this is working

Whereabouts in the form code module should I put the AfterUpdate event code?

Thanks

 

[bob fitz]

When looking at the Property sheet for the lstModule listbox AfterUpdate event, at the far right of the space you will see a dropdown arrow and next to that an ellipse (... three dots in a square).

Click the arrow and select Event Procedure. Then click the ellipse which will open the code window. Copy the code and Paste it between the lines:

Private Sub lstModule_AfterUpdate()

Paste the code here

End Sub

 

[nfi2020]

Thanks Bob!

...although, I have to admit, my first response to it working was "Ha!..I...er...oh...I thought that's what I was already doing."

I know I should go and read a book/help-file to find this out, but could you possibly explain what the difference is between me just typing into the property sheet and defining that property as being an "Event Procedure", please?

Thanks very much for your help - I've been trying to figure this out all morning!

 

[bob fitz]

I'm sure that a book would explain more eloquently than I can and there are other members who are much better at such explanations but since you've asked, I'll try.

You have to to set an "Event" property to Event Procedure for the code that you have in the code module to be called and for it to run.
You can set some other properties, like the Row Source property found on the "Data" tab with a string expression. Some properties like the "Control Source" property which are most often bound to a field in the forms "Record Source" (Usually a table or query but which can also be a string expression) can be set with an expression preceded with an "=" sign which is why I said in post #2:

Be sure to put it in the forms code module. The "=" sign that you used suggests to me that you are putting it in the controls property sheet alongside of the AfterUpdate property.

I hope this attempt at explaining helps. Please post back if you have any other question.

 

[nfi2020]

Thanks very much for taking the time to explain that - I think I appreciate the difference between a text string that simply sets a value and one that has to execute - I can see why it would have to be a piece of procedural code rather than just a string on a property sheet, now.

Thanks!

 

[bob fitz]

Glad you found the code and my explanation of use