The first snippet of code is taken from the form's module where a user has selected a filter set from a combo box
Code:
Case 9014 ' Add parking stalls to the display
Call AppStalls 'Refresh table
Call ShowStalls(True) 'Make the tb's visible
Me.Filter = ""
Me.RecordSource = "QRegistryPrkgDisp" 'Need the registry joined DISTINCT with stalls
Exit Sub
From the same module, the user has requested use of one of the app's functionality dealing with email addresses taken from the current RecordSource
Code:
Private Sub cmdEMAToCB_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' We need to append additional criteria to the existing RecordSource to exclude records
' that do not include EMAs.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Me.cmdTexting.Visible = False 'Can't do both at the same time
Me.cmdEMAToCB.Width = EMARunWidth 'Adjust the display a bit
bolEMASequence = True 'Indicate a sequence has started
If Me.FilterOn Then 'Applies to both QRegistry AND QGroupings (need the addition)
Me.Filter = Me.Filter & " AND not isnull(EMA)"
Else
Me.Filter = "not isnull(EMA)" 'E.g., RecordSource might be W/PrkgStalls
Me.FilterOn = True
End If
Me.Requery
frm_EMAtoCB ("Clone") 'Get full or filtered set (EMAtoCB will now have a clean DOA Recordset to process)
End Sub
Also from the same module, the function that differentiates between the two possible paths taken so as to determine which path option the user has taken. In the current situation, the user simply clicked on the command button, as shown above.
Code:
Public Function frm_EMAtoCB(Optional QSet As String)
'*=*=*=(V10.2 Update:)*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' We can get here two ways, via the original command button or the newly created entry
' in the R-Click popup via =EMAtoCB("QRegSelects"). With this new interface, the user
' can request the email addresses be placed on the clipboard whenever they have been
' "Selected". Baring a "Selected" Recordset, we simply clone our RecordSource, filtered
' or not. (Mail server limitations will be in accord)
'
' In either case, our Recordset includes FmtEMAs:
' """" & [LastName] & ", " & [FirstName] & """<" & [EMA] & ">,"
'
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim I As Integer
Dim strSQL As String
If QSet = "Clone" Then
Set rsRegEMAs = Me.RecordsetClone
strRstType = QSet
Else
'A little tricky here because we need to apply whatever filter is in affect
'First
If Not IsNull(Me.Filter) Then
strSQL = "SELECT * FROM QRegSelectFltrd WHERE " & Me.Filter
MsgBox strSQL
Set rsRegEMAs = DBEngine(0)(0).OpenRecordset(strSQL)
Else
Set rsRegEMAs = DBEngine(0)(0).OpenRecordset(QSet)
End If
End If
lngEMACount = rsRegEMAs.RecordCount
intEMAPartCnt = 1
If lngEMACount = 0 Then
MsgBox "No records found that contain e-Mail Addresses."
Exit Function
End If
'DEBUG: ADD QSET TO CAPTION TO VERIFY PATH TAKEN ABOVE
Me.cmdEMAToCB.Caption = lngEMACount & " in " & QSet '" EMAs found"
'*=*=*=*=(Okay, what method to use?)=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Me.lblMethod.Top = Me.lblPartitions.Top 'Makes Design View less confusing for us
Me.cmdClipboard.Top = Me.lblPartitions.Top 'old farts :-)
Me.cmdCompose.Top = Me.lblPartitions.Top '
Me.lblMethod.Visible = True
Me.cmdClipboard.Visible = True
Me.cmdCompose.Visible = True
End Function
The results, as shown in the next screenshot, reflects the count returned by lngEMACount = rsRegEMAs.RecordCount
(As noted in the code, the " in Clone" stemmed from the temporary use of the command button Caption)
The next screenshot, taken from the same display as the first, reflects the correct number of records in the "clone", 37.
Finally, a screenshot of the data properties of the form.
The most perplexing aspect of this caper is that during step-by-step DEBUG through the code that the lngEMACount = rsRegEMAs.RecordCount has in fact returned the correct value a couple of times.
The DOA Recordset is (Public rsRegEMAs As DAO.Recordset) in the form's module so as to extend its scope to a couple of other forms ONLY called by the current form. I.e., the current form will be in the Forms Collection as Open any time those others are opened.
Finally, last but not least, is where did the count "22" came from? It is the number of records currently displayed on the form's screen. If I drag the borders up to where, say, only 15 records are displayed, then that's the number that will be returned by "RecordCount" and shown in the command button caption.
In case the question comes up:
Code:
Option Compare Database
Option Explicit
Dim strGrpName As String
Dim strTemp As String
Dim SortColumn As String
Dim SortField As String
Dim SearchColumn As String ' Set to the field name when searching a column
Dim SearchLeftPos As Integer ' Left position of search window based on column detected
Public rsRegEMAs As DAO.Recordset
Dim rsRegTexting As DAO.Recordset
Dim intCmdsVisible As Integer
Dim lngEMACount As Long
Dim intEMAPartCnt As Integer
Dim lngPartitionSize As Long
Dim lngLastPartition As Long
Dim bolEMASequence As Boolean
Dim strRstType As String 'Indicator of EMA & Texting type of Recordset
Dim strBasseFldr As String 'RDS folder name
Dim EMAMaxSize As Integer 'Maximum number of EMA's per partition (Obtained from Settings)