After I filter my combo, I can't navigate

[gmazza via AccessMonster.com]

Hey there,
I have a form and subform and 6 combo boxes on the main form and the results
get filtered with the appropriate record showing up in the subform. Here's
the problem:
When I open the form it shows ALL records and I can navigate fine. When I
choose any combo box and the subform gets filtered, I can't navigate to any
of the records anymore. It just sits on the 1st record.

The query for the subform returns 13 records. If I choose a combo box from
the main form, thus
filtering the 13 records, now 5 show up for example. Of those 5, I cannot
navigate to any record but the first one. Because I can't navigate, I can't
enter any info except on the first record.
When the form first opens, 13 records appear, and I can navigate to any of
them. Seems after my filters are applied I can't navigate.

Any ideas?
Thanks!

 

[ruralguy via AccessMonster.com]

Do you have warnings turned off? Are you using the native Nav buttons?

 

[gmazza via AccessMonster.com]

Yes I am using the native nav buttons, as well as when I choose record
selectors to show on my form, same thing, can't get away with from the first
record.
As for warnings, I have no idea what they are set at as default, I just know
that I do not use them at all in my code.
Thanks!

Do you have warnings turned off? Are you using the native Nav buttons?

Hey there,
I have a form and subform and 6 combo boxes on the main form and the results

[quoted text clipped - 14 lines]

Any ideas?
Thanks!

 

[ruralguy via AccessMonster.com]

In the immediate window (^G) type: DoCmd.SetWarnings True
...and then see if you get any messages when you have the problem. Do you
have any code in your forms?

Yes I am using the native nav buttons, as well as when I choose record
selectors to show on my form, same thing, can't get away with from the first
record.
As for warnings, I have no idea what they are set at as default, I just know
that I do not use them at all in my code.
Thanks!

Do you have warnings turned off? Are you using the native Nav buttons?

[quoted text clipped - 3 lines]

 

[gmazza via AccessMonster.com]

Ok I tried that, and nothing came up.
Yes I have code in my form. I searched everywhere and I do not set warnings
anywhere.
Also, I have no macros.

In the After Update of each of my combo boxes on my main form I have this
code:
Private Sub cboAssay_AfterUpdate()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.cboSample) Then
strWhere = strWhere & "SampleID = """ & Me.cboSample & """ And "
End If

If Not IsNull(Me.cboBinder) Then
strWhere = strWhere & "BinderNo = """ & Me.cboBinder & """ And "
End If

If Not IsNull(Me.cboDate) Then
strWhere = strWhere & "(QCDate = " & Format(Me.cboDate, conJetDate) &
") And "
End If

If Not IsNull(Me.cboAssay) Then
strWhere = strWhere & "AssayCode = """ & Me.cboAssay & """ And "
End If

If Not IsNull(Me.cboLot) Then
strWhere = strWhere & "LotNo = """ & Me.cboLot & """ And "
End If

If Not IsNull(Me.cboSOP) Then
strWhere = strWhere & "SOP = """ & Me.cboSOP & """ And "
End If

lngLen = Len(strWhere) - 5
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
Forms!QCRecords!QCRecordsSub!Text88 = strWhere
Forms!QCRecords!QCRecordsSub.Requery
End Sub


In the On Current of my subform I have:
If Not IsNull(Text88) Then
Me.Filter = Text88
Me.FilterOn = True
End If


Other than that, I have no more code.

Could I maybe send you my database and you can see this weirdness.
Its only 1 mb.

I just don't know what to do.

In the immediate window (^G) type: DoCmd.SetWarnings True
...and then see if you get any messages when you have the problem. Do you
have any code in your forms?

Yes I am using the native nav buttons, as well as when I choose record
selectors to show on my form, same thing, can't get away with from the first

[quoted text clipped - 8 lines]

 

[ruralguy via AccessMonster.com]

Go ahead an zip it up and send to Rural Guy at Wild Blue dot net.

Ok I tried that, and nothing came up.
Yes I have code in my form. I searched everywhere and I do not set warnings
anywhere.
Also, I have no macros.

In the After Update of each of my combo boxes on my main form I have this
code:
Private Sub cboAssay_AfterUpdate()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.cboSample) Then
strWhere = strWhere & "SampleID = """ & Me.cboSample & """ And "
End If

If Not IsNull(Me.cboBinder) Then
strWhere = strWhere & "BinderNo = """ & Me.cboBinder & """ And "
End If

If Not IsNull(Me.cboDate) Then
strWhere = strWhere & "(QCDate = " & Format(Me.cboDate, conJetDate) &
") And "
End If

If Not IsNull(Me.cboAssay) Then
strWhere = strWhere & "AssayCode = """ & Me.cboAssay & """ And "
End If

If Not IsNull(Me.cboLot) Then
strWhere = strWhere & "LotNo = """ & Me.cboLot & """ And "
End If

If Not IsNull(Me.cboSOP) Then
strWhere = strWhere & "SOP = """ & Me.cboSOP & """ And "
End If

lngLen = Len(strWhere) - 5
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
Forms!QCRecords!QCRecordsSub!Text88 = strWhere
Forms!QCRecords!QCRecordsSub.Requery
End Sub

In the On Current of my subform I have:
If Not IsNull(Text88) Then
Me.Filter = Text88
Me.FilterOn = True
End If

Other than that, I have no more code.

Could I maybe send you my database and you can see this weirdness.
Its only 1 mb.

I just don't know what to do.

In the immediate window (^G) type: DoCmd.SetWarnings True
...and then see if you get any messages when you have the problem. Do you

[quoted text clipped - 5 lines]

 

[BruceM via AccessMonster.com]

One problem I see is in how you reference the subform (I assume that's what
you are trying to do). You need to reference the Form property of the
subform control:

Forms!QCRecords!QCRecordsSub.Form!Text88 = strWhere
Forms!QCRecords!QCRecordsSub.Form.Requery

If the code is in the parent form you can simplify the syntax:

Me.QCRecordsSub.Form.Text88 = strWhere
Me.QCRecordsSub.Form.Requery

However, I wonder why you are filtering the subform at all. If the subform
record source is related to the main form record source, and the subform
control's Link properties are set to the joining field, the correct records
should show up in the subform.

A few other points about the filtering:

If you add Debug.Print strWhere (or MSgBox strWhere) as a new line of code
after each strWhere you can see if the string is being assembled properly.

There is no need I can see to format the date. I don't think you need the
parentheses there, but I don't think they hurt either.

You could save yourself some coding by putting a procedure such as this into
a form-level function, then calling the function rather than rewriting the
code:

Private Function FilterAll()

' Code Here

End Function

Then in the After Update event for each combo box:

Call FilterAll

Another thing I just realized is it seems you are applying the same filter to
both the main form and the subform record source. I think part of the
problem is that when you try to navigate to a new record in the subform you
are applying the filter all over again, which has the effect of requerying
the record source, which puts you at the first record. If you must filter the
subform's Record Source, do it in a main form event. Again, it should not be
needed at all if the tables are related properly and the subform control's
Link properties are set to the linking field.

Ok I tried that, and nothing came up.
Yes I have code in my form. I searched everywhere and I do not set warnings
anywhere.
Also, I have no macros.

In the After Update of each of my combo boxes on my main form I have this
code:
Private Sub cboAssay_AfterUpdate()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.cboSample) Then
strWhere = strWhere & "SampleID = """ & Me.cboSample & """ And "
End If

If Not IsNull(Me.cboBinder) Then
strWhere = strWhere & "BinderNo = """ & Me.cboBinder & """ And "
End If

If Not IsNull(Me.cboDate) Then
strWhere = strWhere & "(QCDate = " & Format(Me.cboDate, conJetDate) &
") And "
End If

If Not IsNull(Me.cboAssay) Then
strWhere = strWhere & "AssayCode = """ & Me.cboAssay & """ And "
End If

If Not IsNull(Me.cboLot) Then
strWhere = strWhere & "LotNo = """ & Me.cboLot & """ And "
End If

If Not IsNull(Me.cboSOP) Then
strWhere = strWhere & "SOP = """ & Me.cboSOP & """ And "
End If

lngLen = Len(strWhere) - 5
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
Forms!QCRecords!QCRecordsSub!Text88 = strWhere
Forms!QCRecords!QCRecordsSub.Requery
End Sub

In the On Current of my subform I have:
If Not IsNull(Text88) Then
Me.Filter = Text88
Me.FilterOn = True
End If

Other than that, I have no more code.

Could I maybe send you my database and you can see this weirdness.
Its only 1 mb.

I just don't know what to do.

In the immediate window (^G) type: DoCmd.SetWarnings True
...and then see if you get any messages when you have the problem. Do you

[quoted text clipped - 5 lines]