title: Getting CurrentRecord on a dynamic recordset on Access Form
body: <p>I have this code for setting the value for an unbound textbox control, which I call txtCurrRec.  It's to display "x of y records" on a form, instead of relying on the navigation control record count.  Problem is I set the control source for the form, based on a toggle button.  By default (on load) the toggle is true and the filter is on.  If the toggle is pressed a function determines what the sql is and what the txtCurrRec should be.  It gets the total records right (the y of that statement) but if the form is using the filtered SQL (an int field = -1) the x value shows the record count based on the table values, not the filtered set.</p>
  
  <p>This is the public function code:</p>
  
  <pre><code>Private Function ABOnly()
  Dim pSQL As String
  
  If Me.tgABRec = True Then
      Me.tgABRec.Caption = "AB Recs Only"
      Me.tgABRec.BackColor = RGB(221, 217, 195)
      Me.tgABRec.HoverColor = RGB(221, 217, 195)
      pSQL = "SELECT * From tblAllRecs WHERE ABRecs = -1"
      Me.txtCurrRec = CStr(Me.CurrentRecord) &amp; " of " &amp; _
      DCount("ID", "tblAllRecs", "ABRecs = -1") &amp; " Records"
  Else
      Me.tgABRec.Caption = "ALL RECORDS SHOWING"
      Me.tgABRec.BackColor = RGB(221, 217, 195)
      Me.tgABRec.HoverColor = RGB(221, 217, 195)
      pSQL = "SELECT * From tblAllRecs"
      Me.txtCurrRec = CStr(Me.CurrentRecord) &amp; " of " &amp; _
      DCount("ID", "tblAllRecs") &amp; " Records"
  End If
  
  End Function
  </code></pre>
  
  <p>It is the CStr(Me.CurrentRecord) that seems to not work properly with the dynamic SQL. Does anyone know how I can get that to work properly?</p>