Retrospective help - Issues with Listbox control in 2010

[extrobe]

Hello, I'm after a little bit of 'retrospective' help / advice on something.

I have an Access application which has been developed through successive versions of access (from 95 onwards!), most recently in 2013 (and also tested in 2016).

It's mainly lots of forms interacting with data in a SQLS database, and makes extensive use of Listbox Controls.

Recently, I've had cause to use it in 2010 again. However, whilst it initially fired up seemingly fine, it constantly crashed - as in access itself was crashing out. ("Microsoft Access has Stopped Working")

It took a good day to locate the source(s) of the issues, but eventually sort of traced it to the populating of the listbox controls - whenever I set the RecordSet, RowSource or the RowSourceType it was crashing.

Thinking I perhaps had an iffy library or similar, I did a repair on access, de-compiled my database, re-linked any additional references etc, but to no avail.

So, back to my code. It appears to be mostly cases where I set something that has already been set that it takes issue with it.

eg, if I set as part of my loading of the Listbox,

Me.JobList.RowSourceType = "Table/Query"

but then run that module again (to re-populate), it would crash.

Changing this to

If Me.JobList.RowSourceType <> "Table/Query" Then
    Me.JobList.RowSourceType = "Table/Query"
End If

gets around this particular instance of the issue.

In another case, I was setting the rowsource as an access query

lstJobDetail.RowSource = "SELECT x,y,z Where JobID=" & currentJobID

and again, if RowSource had already been set, it would crash.

Instead, I was able to use this...

If lstJobDetail.RowSource = "" Then
lstJobDetail.RowSource = "SELECT x,y,z Where JobID=" & currentJobID
Else
lstJobDetail.Requery
End If

(arguably a better approach anyway in this case)

I tested with both direct queries (as above), saved query objects - same issue

So, though a series of workarounds, I seem to be back in business (though not tested all forms yet), and perhaps in some cases the 'workaround' is the better method anyway, but can anyone suggest why this was giving me such a hard time?

Below is the full module of one of the above examples

Thank you for reading

Private Sub GetJobList()
'This sub populates the main List View with the jobs for that day.
'It's highly parameterised, picking up various tick-box, current user, and day of week options from the form
'JobList = SELECT * FROM JobList(incAutoRunJobs,incProcessedJobs,incCurrentUserOnly,incExpiredJobs,DayValue,WeekValue,incCurrentDateOnly) ORDER BY SortKey SortDirection
'March 2016, David Aldred
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String

'Build the parameterised query string
SQL = "SELECT w,x,y,z FROM dbo.tfnJobList(" & Me.chkAuto.Value & "," & Me.chkSP.Value & ",'" & IIf(Me.chkOMJ.Value = -1, CurrentUser, "%") & "'," & Me.chkSEJ.Value & "," & Me.txtDayNo & "," & Me.txtWeekNo & "," & Me.chkSDO.Value & ")"

'There shouldn't ever not be a sortkey assigned, as one is set on startup, but just in case,
'we'll append the ORDER BY clause only when the sortkey is populated
If Me.txtSortKey.Value <> "" Then
SQL = SQL & " Order by [" & Me.txtSortKey.Value & "] " & Me.txtSortDirection
End If

'establish the connection and recordset objects
Set cn = New ADODB.Connection
cn.Open ccGetPrimaryDSN()
Set rs = New ADODB.Recordset

'open the recordset and run the query
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.Open SQL
End With

'assign the recordset to the listview
'OK, hell knows why, but in 2010, if you try to set the rowsourcetype when it's already set, id nukes access. Joy. But, we can test for its current state instead.
If Me.JobList.RowSourceType <> "Table/Query" Then
Me.JobList.RowSourceType = "Table/Query"
End If

Set Me.JobList.Recordset = rs

'close the connections
rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing

End Sub

 

[Ranman256]

theres no reason to swap the combo between Query or value list.
you can swap the bound query so the lists change out.

if you need your way, create 2 of them 1 visible, while the other is not.

 

[extrobe]

theres no reason to swap the combo between Query or value list.

Absolutely - I don't actually change it - I was setting it at 'run time' just so as to ensure it was correctly set - I can probably hard-set it in the properties and just leave it. (Edit: I've now made this change)

But doesn't explain why it nukes access whenever I set it.

And even if i don't set it (as per the second example), re-setting the query still triggered the crash.

In that case, using .requery works ok, but suspect I'll have a couple of forms yet to test which does need to change the underlying query.

 

[arnelgp]

why not change this part to recordsource:

Set Me.JobList.Recordset = rs

with:
Me.JobList.RowSource = SQL

 

[extrobe]

why not change this part to recordsource:

Set Me.JobList.Recordset = rs

with:
Me.JobList.RowSource = SQL

In that example, the query is executing a stored procedure in SQLS, and returning the results to a recordset object.

So unless I misunderstand, then I wouldn't be able to do that.

Again, just confused as to what was going so badly wrong that it's killing access. Wondering if it's just a buggy dll, as works fine in 2013, but want to check I'm not doing something fundamentally wrong, or overlooking something.

When I launched in 2010, I did have to switch the Excel 16 library back to 14, and had to re-register mscomct2.ocx (though I really should de-commission whatever is still using that!) - but don't think these actions would be causing the issue.

Perhaps it'll remain a mystery - but has been a royal PITA to figure out