Difficulty with code..

[Waxaholic]

I don't know if this is the right place to ask for this kind of help but any guidance would be appreciated. I am a novice at Access vba by the way. I am trying to create/modify an existing Drill-Down code for my own purposes. The way it should work is after making a selection from qryCombo2, the lstAlbum field is populated. This part works fine. Now double-clicking an Album from lstAlbum should fill the lstTrack field with the appropriate tracks. Everything is fine but the dblclk part is buggy. For some reason it will only work on a field (lstAlbum) that contains 1 single word and not multiple words. Even then it acts as a Parameter based query and requests input. I do not want any parameter based pop-ups. Here is the code:

-----------------------------------------------------------

Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT DISTINCT Album, Genre, Bitrate, Mode " & _
"FROM qryCombo2 WHERE Artist = '"
Private Const strSQL2 = "' ORDER by Album ASC;"
Private Const strSQL3 = "SELECT TrackTitle, TrackIndex, Bitrate, " & _
"Mode, Location FROM qryDrillDown2 WHERE Album = "
Private strSQL As String

Private Const strMsg1 = "Select an Artist from the list"
Private Const strMsg2 = "Double-click an Album to display it's associated tracks"
Private Const strMsg3 = "Track Listing for Album: "
Private Const strMsg4 = "Tracks"

Private Sub FillList()
strSQL = strSQL1 & Me!cboArtist.Value & _
strSQL2
Me!lstAlbum.RowSource = strSQL
Me!lstAlbum.Requery
Me!lblAlbum.Caption = "Albums from " & _
Me!cboArtist.Value
If Me!lstAlbum.ListCount = 0 Then
Me!lblAlbum.Caption = "No " & Me!lblAlbum.Caption
Me!lblTrack.Caption = strMsg2
End If

End Sub

Private Sub cboArtist_BeforeUpdate(Cancel As Integer)
If Me!cboArtist.Value <> &quot;&quot; Then
Call FillList
Else
Me!lblAlbum.Caption = strMsg1
End If
With Me!lstTrack
.RowSource = &quot;&quot;
.Requery
End With
Me!lblTrack.Caption = strMsg4
End Sub


Private Sub Form_Activate()
If Me!cboArtist.Value > 0 Then
Call FillList
Else
Me!lblAlbum.Caption = strMsg1
End If
With Me!lstTrack
.RowSource = &quot;&quot;
.Requery
End With
Me!lblTrack.Caption = strMsg4
With Me!lstAlbum
.RowSource = &quot;&quot;
.Requery
End With
End Sub

'Upon DoubleClick of an Album title, all Tracks for that Album are generated in the Track details chart

Private Sub lstAlbum_DblClick(Cancel As Integer)
If Me!lstAlbum.Value <> &quot;&quot; Then
With Me!lstTrack
strSQL = strSQL3 & Me!lstAlbum.Value & &quot;;&quot;
.RowSource = strSQL
.Requery
End With
Me!lblTrack.Caption = strMsg3 & Me!lstAlbum.Value
End If
End Sub

-----------------------------------------------------------
Any help would be much appreciated,

Brian

 

[jebry]

Hi Brian!

Try this:

Private Sub lstAlbum_DblClick(Cancel As Integer)
Dim Rowcount As Integer
RowCount = Me!lstAlbum.ListIndex
If Me!lstAlbum.Column(0, RowCount) <> &quot;&quot; Then
strSQL = strSQL3 & Me!lstAlbum.Column(0, Rowcount) & &quot;;&quot;
With Me!lstTrack
.RowSource = strSQL
.Requery
End With
Me!lblTrack.Caption = strMsg3 & Me!lstAlbum.Column(0, RowCount)
End If
End Sub

hth
Jeff Bridgham
bridgham@purdue.edu

 

[GComyn]

HI... I've had the same problem.. what I usually do is something like this:


Try this:
in the Declarations area, I put:
Const DblQuote as String = &quot;&quot;&quot;&quot;

Then change the code to look like this:

Private Sub lstAlbum_DblClick(Cancel As Integer)
Dim Rowcount As Integer
RowCount = Me!lstAlbum.ListIndex
If Me!lstAlbum.Column(0, RowCount) <> &quot;&quot; Then
strSQL = strSQL3 & DblQuote & Me!lstAlbum.Column(0, Rowcount) & DblQuote & &quot;;&quot;
With Me!lstTrack
.RowSource = strSQL
.Requery
End With
Me!lblTrack.Caption = strMsg3 & Me!lstAlbum.Column(0, RowCount)
End If
End Sub


That should work... I've not created the tables/form needed to do the testing though, so it may not.

Gwydion

 

[Waxaholic]

Still not working properly. The lstAlbum double-click function selects the listing above it and only the single-word names display anything after calling for parameter based info twice. Question: the Me!lstAlbum.ListIndex, is ListIndex common or is it needing to be substituted with and point to a specific column header in the lstAlbum listbox? Just taking stabs in the dark here as i have been stuck on this for a few weeks now poking away at this and that in the code to no avail.

Thanks,

Brian

 

[Waxaholic]

Ok now, I inserted the code GComyn gave and it is starting to work. Only prob is that the selection from lstAlbum display the album above the selcetion in the lstTrack listbox. So, for some reason it is -1 in the selection process. Any ideas?

Thanks,

Brian

 

[Waxaholic]

I modified it a little and now it works. This is what i ended up with.

Private Sub lstAlbum_DblClick(Cancel As Integer)
Dim Rowcount As Integer
Rowcount = Me!lstAlbum.ListIndex
If Me!lstAlbum.Value <> &quot;&quot; Then
strSQL = strSQL3 & DblQuote & Me!lstAlbum.Value & DblQuote & &quot;;&quot;
With Me!lstTrack
.RowSource = strSQL
.Requery
End With
Me!lblTrack.Caption = strMsg3 & Me!lstAlbum.Value
End If
End Sub


It works but do either of you see any issues in the code. If not, I am very much appreciative for you expertise.

Thank you, thank you,

Brian