Why does this code work?

[Guest]

hi to all

This question isn't about code that doesn't work, but about code that does
(but I'm not sure why??)

In my form's header, I have an unbound combobox (cboSelectCityState) that is
used to find matching records in a subform (master/child link is
[City-StateID]). From a posting found in this forum several weeks ago, I got
this to work perfectly using the following code:

Private Sub cboSelectCityState_AfterUpdate()
If Not IsNull(cboSelectCityState) Then
Me.RecordsetClone.Findfirst "[City-StateID] = " &
Me![cboSelectCityState]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

Since then, I also got it to work perfectly using:

Private Sub cboSelectCityState_AfterUpdate()
Me.Filter = "[City-StateID] = " & cboSelectCityState.Value
Me.FilterOn = True
End Sub

My questions are:
a) Why does the first code fragment find multilple records (as I wanted)
even though 'FindFirst' was used?
b) Are there advantages/disadvantages to either method?

 

[Marshall Barton]

This question isn't about code that doesn't work, but about code that does
(but I'm not sure why??)

In my form's header, I have an unbound combobox (cboSelectCityState) that is
used to find matching records in a subform (master/child link is
[City-StateID]). From a posting found in this forum several weeks ago, I got
this to work perfectly using the following code:

Private Sub cboSelectCityState_AfterUpdate()
If Not IsNull(cboSelectCityState) Then
Me.RecordsetClone.Findfirst "[City-StateID] = " &
Me![cboSelectCityState]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

Since then, I also got it to work perfectly using:

Private Sub cboSelectCityState_AfterUpdate()
Me.Filter = "[City-StateID] = " & cboSelectCityState.Value
Me.FilterOn = True
End Sub

My questions are:
a) Why does the first code fragment find multilple records (as I wanted)
even though 'FindFirst' was used?
b) Are there advantages/disadvantages to either method?

Both "versions" of the procedure are manipulating the main
form's selected record. The Link Master/Child properties
are filtering the subform records. The first version,
causes the main form to navigate to a record with the
desired CityState so the subform syncs to the combo box's
value (indirectly through the current record). The second
version filters the main form records to only the desired
CityState, so regardless of which record is current, the
subform is synced (indirectly) to the combo box's value.

 

[Guest]

Marshall - thanks for the prompt and clear reply. What you said is forcing
me to rethink my limited understanding of how subforms get their records.

This question isn't about code that doesn't work, but about code that does
(but I'm not sure why??)

In my form's header, I have an unbound combobox (cboSelectCityState) that is
used to find matching records in a subform (master/child link is
[City-StateID]). From a posting found in this forum several weeks ago, I got
this to work perfectly using the following code:

Private Sub cboSelectCityState_AfterUpdate()
If Not IsNull(cboSelectCityState) Then
Me.RecordsetClone.Findfirst "[City-StateID] = " &
Me![cboSelectCityState]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

Since then, I also got it to work perfectly using:

Private Sub cboSelectCityState_AfterUpdate()
Me.Filter = "[City-StateID] = " & cboSelectCityState.Value
Me.FilterOn = True
End Sub

My questions are:
a) Why does the first code fragment find multilple records (as I wanted)
even though 'FindFirst' was used?
b) Are there advantages/disadvantages to either method?

Both "versions" of the procedure are manipulating the main
form's selected record. The Link Master/Child properties
are filtering the subform records. The first version,
causes the main form to navigate to a record with the
desired CityState so the subform syncs to the combo box's
value (indirectly through the current record). The second
version filters the main form records to only the desired
CityState, so regardless of which record is current, the
subform is synced (indirectly) to the combo box's value.

 

[John W. Vinson]

Private Sub cboSelectCityState_AfterUpdate()
If Not IsNull(cboSelectCityState) Then
Me.RecordsetClone.Findfirst "[City-StateID] = " &
Me![cboSelectCityState]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

Since then, I also got it to work perfectly using:

Private Sub cboSelectCityState_AfterUpdate()
Me.Filter = "[City-StateID] = " & cboSelectCityState.Value
Me.FilterOn = True
End Sub

My questions are:
a) Why does the first code fragment find multilple records (as I wanted)
even though 'FindFirst' was used?
b) Are there advantages/disadvantages to either method?

The two code snippets are doing quite different things.

The first example simply finds the first record in your Form's
RecordsetClone which matches the combo box selected value, and
navigates to that record. If there are 10000 addresses in the query
upon which the form is based, you'll still have all 10000 available;
you'll just be positioned at the 2142nd or wherever the first address
with that City-StateID is found. There may well be other records in
the form with that same ID; the code will just find the first of them.

The second snippet instead puts a FILTER on the form's Recordset; with
the filter set, you will see *only* that subset of the form's records
which match the combo box (maybe one, maybe a dozen, however many
there are). The other records are still in the table but are filtered
out from this form's view until you set Me.FilterOn back to False.

John W. Vinson [MVP]

 

[efandango]

Marshall,

I have a similar problem; this code is something that you helped me
previously; but now I want, just like Sarah to filter on the selected
field/data:

I have tried to adapt Sarah's snippet to this code:

Private Sub txt_Getrounds_Present_Flag_Click()
With Parent.[frm_Getrounds].Form.RecordsetClone
.FindFirst "[GetRoundPoint] = """ & Me![Run_point_Venue] & """"
If Not .NoMatch Then
Parent.[frm_Getrounds].Form.Bookmark = .Bookmark
Parent.[frm_Getrounds].SetFocus
Parent.[frm_Getrounds].Form.[GetRoundPoint].SetFocus
End If
End With


BY adding these two lines:

Me.Filter = "Parent.[frm_Getrounds].Form.[GetRoundPoint] = """ &
Me![Run_point_Venue] & """"
Me.FilterOn = True

which gives me this:

Private Sub txt_Getrounds_Present_Flag_Click()
With Parent.[frm_Getrounds].Form.RecordsetClone
.FindFirst "[GetRoundPoint] = """ & Me![Run_point_Venue] & """"
If Not .NoMatch Then
Parent.[frm_Getrounds].Form.Bookmark = .Bookmark
Parent.[frm_Getrounds].SetFocus
Parent.[frm_Getrounds].Form.[GetRoundPoint].SetFocus

Me.Filter = "Parent.[frm_Getrounds].Form.[GetRoundPoint] = """ &
Me![Run_point_Venue] & """"
Me.FilterOn = True
End If
End With
End Sub

but when I run it, I get a parameter/input box pop-up and ask me for the
GetRoundPoint.

What am I doing wrong, and how can I fix it?

This question isn't about code that doesn't work, but about code that does
(but I'm not sure why??)

In my form's header, I have an unbound combobox (cboSelectCityState) that is
used to find matching records in a subform (master/child link is
[City-StateID]). From a posting found in this forum several weeks ago, I got
this to work perfectly using the following code:

Private Sub cboSelectCityState_AfterUpdate()
If Not IsNull(cboSelectCityState) Then
Me.RecordsetClone.Findfirst "[City-StateID] = " &
Me![cboSelectCityState]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

Since then, I also got it to work perfectly using:

Private Sub cboSelectCityState_AfterUpdate()
Me.Filter = "[City-StateID] = " & cboSelectCityState.Value
Me.FilterOn = True
End Sub

My questions are:
a) Why does the first code fragment find multilple records (as I wanted)
even though 'FindFirst' was used?
b) Are there advantages/disadvantages to either method?

Both "versions" of the procedure are manipulating the main
form's selected record. The Link Master/Child properties
are filtering the subform records. The first version,
causes the main form to navigate to a record with the
desired CityState so the subform syncs to the combo box's
value (indirectly through the current record). The second
version filters the main form records to only the desired
CityState, so regardless of which record is current, the
subform is synced (indirectly) to the combo box's value.