Moving through a Continuous Form's Recordset

  • Thread starter Thread starter Frank Reichenbacher
  • Start date Start date
F

Frank Reichenbacher

I have a continuous form bound to a query that lists all of my customers. I
want a text box at the top in the Form Header to allow the user to quickly
point the form to an account quickly instead of forcing them to scroll
through 2000+ accounts even for a well-known account.

In the AfterUpdate event of the text box I have this:

Dim strCustFNUM As String

strCustFNUM = Me!CustFNUM
Me.RecordsetClone.FindFirst "[CustFNUM] = " & strCustFNUM
Me.Bookmark = Me.RecordsetClone.Bookmark

This doesn't work, the form Bookmark is stuck at the first displayed record.
Is this some property of a continuous form that I wasn't aware of? Do I have
to use a subform?

Frank
 
I just posted the same question! And I tried the same thing you did. Makes
me feel better that someone else has the same problem! Good luck with your
project and I may be stealing any answers you get.
 
You need to use an *unbound* text box, with a Name different to the field
name.

Private Sub txtFindCust_AfterUpdate()
If Not IsNull(Me.txtFindCust) Then
If Me.Dirty Then 'Save before move.
Me.Dirty = False
End If
With Me.RecordsetClone
.FindFirst "[CustFNUM = " & Me.txtFindCust
If .NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

Note: If CustFNUM is a field of type Number, set the Format property of the
txtFindCust textbox to "General Number". If it is a Text type field, you
need extra quotes in the FindFirst line:
.FindFirst "[CustFNUM = """ & Me.txtFindCust & """"
 
My post is just before you two and it deals with
precisely the same dilemma. We are trying to iterate
through a recordset, but are unable to get it to
correlate to the created form controls. My situation
starts with an Access generated recordset and no matter
what I have tried, I can't get my code to work with that
recordset and tie into the form at the same time. I have
created my own recordsets in a million different ways
like you have, but the Access recordset won't let go of
the form so I can use it.

Scott
-----Original Message-----
I just posted the same question! And I tried the same thing you did. Makes
me feel better that someone else has the same problem! Good luck with your
project and I may be stealing any answers you get.


I have a continuous form bound to a query that lists
Click to expand...
all of my customers.
I
want a text box at the top in the Form Header to allow the user to quickly
point the form to an account quickly instead of forcing them to scroll
through 2000+ accounts even for a well-known account.

In the AfterUpdate event of the text box I have this:

Dim strCustFNUM As String

strCustFNUM = Me!CustFNUM
Me.RecordsetClone.FindFirst "[CustFNUM] = " & strCustFNUM
Me.Bookmark = Me.RecordsetClone.Bookmark

This doesn't work, the form Bookmark is stuck at the
Click to expand...
first displayed
record.
Is this some property of a continuous form that I
Click to expand...
wasn't aware of? Do I
have
to use a subform?

Frank
Click to expand...


.
Click to expand...
 
Allen Browne said:
You need to use an *unbound* text box, with a Name different to the field
name.
Click to expand...

You mean it can't read my mind?

How very disappointing.


strCustFNUM = Trim(Me!txtFacAcctNoSrch)
MsgBox strCustFNUM
Me.RecordsetClone.FindFirst "[CustFNUM] = """ & strCustFNUM & """"
Me.Bookmark = Me.RecordsetClone.Bookmark

Me!cmdSrchResultsSelect.SetFocus

Completed code, with a couple of tweaks and an adjustment to the textbox
input mask.

Thank you Allen!

Frank

Private Sub txtFindCust_AfterUpdate()
If Not IsNull(Me.txtFindCust) Then
If Me.Dirty Then 'Save before move.
Me.Dirty = False
End If
With Me.RecordsetClone
.FindFirst "[CustFNUM = " & Me.txtFindCust
If .NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

Note: If CustFNUM is a field of type Number, set the Format property of the
txtFindCust textbox to "General Number". If it is a Text type field, you
need extra quotes in the FindFirst line:
.FindFirst "[CustFNUM = """ & Me.txtFindCust & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Frank Reichenbacher said:
I have a continuous form bound to a query that lists all of my
Click to expand...
customers.
I
want a text box at the top in the Form Header to allow the user to quickly
point the form to an account quickly instead of forcing them to scroll
through 2000+ accounts even for a well-known account.

In the AfterUpdate event of the text box I have this:

Dim strCustFNUM As String

strCustFNUM = Me!CustFNUM
Me.RecordsetClone.FindFirst "[CustFNUM] = " & strCustFNUM
Me.Bookmark = Me.RecordsetClone.Bookmark

This doesn't work, the form Bookmark is stuck at the first displayed record.
Is this some property of a continuous form that I wasn't aware of? Do I have
to use a subform?

Frank
Click to expand...
Click to expand...
 
Back
Top