subroutine not executing on filter fail - why?

  • Thread starter Thread starter Kurt Wimberger via AccessMonster.com
  • Start date Start date
K

Kurt Wimberger via AccessMonster.com

Hi all:

I am having an issue with Access nav buttons, events and a filter.

I have a form that filters a subform using Form_Current() as the user
scrolls through the main form using the Access nav buttons. Inside the same
event I call a subroutine to update an unbound field to display some
details of what is about to happen. Works great 99% of the time.

But if the particular filtering happens to return no records in the
subform, then the unbound txt field is not updated. It shows the data from
the last time the filtering had records returned.

Let's say the user then clicks 'next record' on the main form 3 more times
without the subform filter returning any records. On the 4th click the
subform returns records but the txt field doesn't update. It will update as
you click to the next record, but it seems to hesitate on the first record
that returns a filtered record after a record that returns no filtered
records.

So it seems to me that the txt field update is somehow being tied to the
success (or return of data) from the subform filter. I don't know why that
should be since I call them individually.

I even went so far as to build a separate cmdButton to do the 'Next Record'
nav and added the update script to its click event, but it performs the
same way. And, I should mention that the txt field update code is executed
(or should be) BEFORE the filter, so I would have thought the filter's
success would have nothing to do with it.

Any thoughts?

Kurt
 
I just read today about .AccessConnection vs .Connection when using ADO
rs's bound to forms. Is it possible, no mattter how unlikely, that the
wrong choice of connection could be causing this issue?

Thanks
 
Kurt said:
I am having an issue with Access nav buttons, events and a filter.

I have a form that filters a subform using Form_Current() as the user
scrolls through the main form using the Access nav buttons. Inside the same
event I call a subroutine to update an unbound field to display some
details of what is about to happen. Works great 99% of the time.

But if the particular filtering happens to return no records in the
subform, then the unbound txt field is not updated. It shows the data from
the last time the filtering had records returned.

Let's say the user then clicks 'next record' on the main form 3 more times
without the subform filter returning any records. On the 4th click the
subform returns records but the txt field doesn't update. It will update as
you click to the next record, but it seems to hesitate on the first record
that returns a filtered record after a record that returns no filtered
records.

So it seems to me that the txt field update is somehow being tied to the
success (or return of data) from the subform filter. I don't know why that
should be since I call them individually.

I even went so far as to build a separate cmdButton to do the 'Next Record'
nav and added the update script to its click event, but it performs the
same way. And, I should mention that the txt field update code is executed
(or should be) BEFORE the filter, so I would have thought the filter's
success would have nothing to do with it.
Click to expand...


Kurt, I can't help you with ADO or ADP stuff, but I wanted
to mention that no one's going to be able to help you figure
out what your code is doing unless you post a Copy/Paste of
the relevant VBA procedure(s?).
 
Yeah, that wasn't too smart on my part. Here's the code:

'*****************************************************************
Code from the main form:

'-- Module level vars.
Dim intSpaces As Integer
Dim rsMatch As New ADODB.Recordset
Dim strSQL As String
Dim oConn As New ADODB.Connection
Dim strMatch As String
Dim strFilter As String

Private Sub Form_Load()
If ("" & Me.Strain) = "" Then
MsgBox "Found a blank."
End If
End Sub

'----------------------------------------------------------------------
(1) As the user moves through records on the main form, the Form_Current()
event
Is used to drive the results on the subform by filtering an exisiting rs on
the subform.
BUT – if that filter returns no records, then a (2) field on the subform is
not updated.
'---------------------------------------------------------------------------
-----------------------------------
Private Sub Form_Current()
Call subSetMatchDisplay

strFilter = fncBuildFilter()

Call Me.sbfrmMatchesFr.Form.subFindMatch(strFilter)
End Sub

Private Sub cmbSpaces_AfterUpdate()
Call subSetMatchDisplay

strFilter = fncBuildFilter()

Call Me.sbfrmMatchesFr.Form.subFindMatch(strFilter)
End Sub

Private Function fncBuildFilter() As String
intSpaces = CInt(Me.cmbSpaces)
strMatch = Left(Me.Strain, intSpaces)

fncBuildFilter = "mAccession LIKE '" & strMatch & "*'"
End Function

'---------------------------------------------------------------------------
-----------------------
(2) Here is the code that basically places the filter string into a field
on the subform
so the user can see what they are filtering for. This is the field that
won't update if
the filtering code above (1) returns no records.
'---------------------------------------------------------------------------
-------------------------
Private Sub subSetMatchDisplay()
intSpaces = CInt(Me.cmbSpaces)
strMatch = Left(Me.Strain, intSpaces)

Me.sbfrmMatchesFr.Form.txtMatchString.Value = strMatch & "*"
End Sub

'*****************************************************************
Code from the subform:

'-- Module level vars.
Dim strSQL As String
Dim oConn As New ADODB.Connection
'-----------------------------------------------------------------------
' Set oConn on form load.
'-----------------------------------------------------------------------
Private Sub Form_Load()
Set oConn = CurrentProject.AccessConnection
End Sub

Public Sub subFindMatch(strFilter As String)
Me.Filter = strFilter
Me.FilterOn = True

If Me.Recordset.RecordCount = 0 Then
Dim intSpaces As Integer
Dim strMatch As String

intSpaces = CInt(Forms("frmCompareAccessions").cmbSpaces)
strMatch = Left(Forms("frmCompareAccessions").txtStrain, intSpaces)

Me.txtMatchString.Value = strMatch & "*"
End If
End Sub


Wondering if it has to do with the order the subs/functions are called in?
 
Kurt said:
Anyone?
Click to expand...

I think it's been long enough that should try re posting
your question and code. Put something like "Repost" or
"Second Try" in the subject line.

One other comment, that's a lot of code. Your question
stands a better chance of being analyzed if you could strip
out any irrelevant parts.
 
Back
Top