Set/filter listbox's rowsource without requerying. 1

[ponderdj]

Hello,

I am using Access 2000. I have a list box, called lstCircuits on a form. I would like to find a way to set the rowsource one time, then filter/unfilter depending on command buttons underneath the listbox. I am running into problems because.

a) You cannot set a listbox's rowsource equal to a recordset in 2000. (right?)

b) I used this code, but ran into problems because strRowSource contained too many characters to set to the rowsource of the listbox:

Dim qdf1 As DAO.QueryDef
'Dim rst1 As DAO.Recordset

Set qdf1 = CurrentDb.QueryDefs("QRY_FRM_ENTRY_RS")

Set rst1 = qdf1.OpenRecordset()

rst1.Filter = "STATUS = 'DISCONTINUED'"

Set rst1 = rst1.OpenRecordset

Do While Not rst1.EOF
  For intlngcounter = 0 To rst1.Fields.Count - 1
    strRowSource = strRowSource & rst1.Fields(intlngcounter).Value & ";"
  Next intlngcounter
  rst1.MoveNext
Loop

'I get a too many characters error on this line
Me.lstCircuits.RowSource = strRowSource

Any ideas, or will I just have to requery everytime I want to set the rowsource?

 

[TheAceMan1]

How are ya ponderdj . . .

Basically what you can do is [blue]default[/blue] the RowSource to an [blue]SQL returning all records[/blue], then in code using the same SQL as a base, you call a [blue]common routine[/blue] from the buttons that [blue]appends the proper criteria (or not) to the SQL[/blue], upon which you update the RowSource!. Something like:

[blue]   Dim SQL As String, Criteria As String
   
   SQL = "SELECT NameID, FirstName, LastName " & _
         "FROM TableName;"
   
   If btnName = "ButtonName1" Then
      Criteria = "[NameID] = " & Me!NameID
   ElseIf btnName = "ButtonName2" Then
      Criteria = "[FirstName] = 'Kalvin'"
   End If
   
   Me!ListboxName.RowSource = SQL & Criteria & ";"[/blue]

Post back sample/actual criteria for the buttons you'll be using . . .

See Ya! . . . . . .

 

[ponderdj]

AceMan,

Thanks for the response. It looks like your solution is how I'll be handling it. I was hoping not to have to run the SQL query again, just run one query when the form opens (takes in all posible records) that has different criteria applied to it as they click the buttons (filters down the records already loaded into the form). I'm guessing that it isn't possible in 2000 because of the listbox limitations, though.

I think that my solution will look like this:

   Function ReturnList(lblName As String)
   Dim SQL As String, Criteria As String
   
   SQL = "SELECT KEYID, STATUS_ID " & _
         "FROM TBL_BASELINE"
   
   If lblName = "lblDiscontinued" Then
      Criteria = "WHERE [Status_ID] = '1'" 
   ElseIf lblName = "lblPending" Then
      Criteria = "WHERE [Status_ID] = '2'"
   End If
   
   Me!lstCircuits.RowSource = SQL & Criteria & ";"
End Function

And I'll call it like this:

Private Sub lblPending_Click()
Call ReturnList("lblPending")
End Sub

Does this look like a good solution? Is there any way to avoid "hitting" the table every time the user clicks lblPending or lblDiscontinued, etc?

 

[TheAceMan1]

ponderdj . . .

The code looks fine, yet you apparently have some fear of running the SQL more than once . . .

By any chance is this due to the DB residing on a network?

See Ya! . . . . . .

 

[formerTexan]

Ponderdj,

There is at least one other way to avoid the calls to the tables: use a ListCallBack function and array based listboxes. Load the array initially and then users can sort and/or select to their satisfaction without further reference to the tables.

Cheers,
Bill

 

[ponderdj]

AceMan,

Yes, you are correct in that the DB is residing on a network. That's why I'm trying to avoid running the SQL more than once.

formerTexan,

You wouldn't be able to provide an example based on my code by chance, would you?

 

[ponderdj]

Also, formerTexan, I think your solution would still run into the problem I was having when bringing the SQL data that was returned into a recordset. (example in my initial post)
That is, listboxes in Access 2000 have a fairly small character limit that they can be assigned. In my case the recordset, in your case the array, would contain too much information to add to the listbox like that.
This is one case where I'd like to be wrong, though, if anyone can show me otherwise.

 

[TheAceMan1]

[blue] . . . listboxes in [purple]Access 2000[/purple] have a [purple]fairly small character limit[/purple] that they can be assigned.[/blue]

This only applies when using a [blue]Row Source Type[/blue] of [purple]Value List[/purple], the limit of which is 2K (2048) characters.

[blue]Keeping the default SQL[/blue], you'll have to switch to [blue]Filtering[/blue]. Something like:

[blue]   Me.Filter = "[FirstName] Like 'Bet*'"
   Me.FilterOn = True[/blue]

Here table access only occurs when the form is opened . . .

See Ya! . . . . . .

 

[ponderdj]

AceMan,

Correct me if I'm wrong, but listboxes do not have a .filter property. I beleive that the .filter filters the recordset of the form, which I do later in my code, depending on what's displayed as the first record in the listbox. Like this:

'Set Form Recordsource
 
  
  If Me.RecordSource = "" Then
  RecordSource = SQL & ";"
  Else
  End If

With Forms!FRM_REC_ENTRY! 
    .Filter = ""
    .Filter = "(TBL_BASELINE.CSANOSPACE)='" & Me.lstCircuits.Column(0, 1) & "' "
    .FilterOn = True
End With
End Function

 

[TheAceMan1]

ponderdj . . .

Yes your correct . . . I was thinking form . . . sorry about that!

Assuming the db is split (front/back ends), [blue]consider a local table in the frontend[/blue] with only the fields you need! When the db is opened, the table can be loaded . . .

Your thoughts . . .

See Ya! . . . . . .

 

[formerTexan]

Greetings Folks,

As AceMan noted, array based listboxes won't have any character limits you are likely to run into, however the local table approach will be simpler to implement. Why didn't I think of that?

Cheers,
Bill

 

[ponderdj]

AceMan,

It seems that your idea will be the one I decide to go with. Fortunately, this DB will only have a couple of users. It concerns me, though, that in the future if I were to have multiple people entering records at the same time, we might run into some problems with people overwriting data. How often would we have to "sync" the tables so that they are seeing the latest data, and not missing updated records because their local table was valid as of when they opened the DB?

Any ideas?

 

[TheAceMan1]

ponderdj . . .

After looking this thread over, [blue]synchronizing when the form is open is a better choice for ya . . .[/blue]

See Ya! . . . . . .

 

[ponderdj]

AceMan,

How would you deal with the problem that any updates they would perform would update their local table? One user could pull in thier table, edit Records A and B. Another user could pull in thier table before the first user's records are written to the backend and edit Record C. User one then closes the form and updates the backend with A and B. User 2 then closes the form, and updates the backend with C only, overwriting User 1's changes to A and B.

Any ideas?