Forms - Filter on Open

[Guest]

How do I design a form that prompts a user to enter info to find a specific
record in a table?

 

[Allen Browne]

Add an unbound text box to the form. Use the AfterUpdate event procedure of
this control to find the matching record.

The code will be something like this:

Sub txtFind_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.txtFind) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.txtFind
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Note: If the CustomerID field is a Text field (not a Number field), you need
extra quotes:
rs.FindFirst "[CustomerID] = """ & Me.txtFind & """"

 

[Guest]

I did learned from this answer you gave but I just wondering about the dirty
whats the use of that anyway and what does the result of that. I hope you
could ellaborate it much more so I would understand it better.

Thanks Allen.

Add an unbound text box to the form. Use the AfterUpdate event procedure of
this control to find the matching record.

The code will be something like this:

Sub txtFind_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.txtFind) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.txtFind
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Note: If the CustomerID field is a Text field (not a Number field), you need
extra quotes:
rs.FindFirst "[CustomerID] = """ & Me.txtFind & """"

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

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

How do I design a form that prompts a user to enter info to find a
specific
record in a table?

 

[Allen Browne]

Before you can move to another record, Access must save any pending edits in
the current record. By saving explicitly, you trigger the sequence of events
that must occur before the move, and in doing so you avoid some potential
issues and misleading messages that are triggered if the record cannot be
saved (e.g. required field missing, duplicate index, ...)

Setting the form's Dirty property is one way to force a save and nominate
which form you are saving, regardless of whether it has focus or not.
RunCommand acCmdSaveRecord saves the record in whatever form has focus, and
Me.Refresh does not trigger a trappable error in some versions of Access, so
you don't know if it succeeded or not.

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

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

I did learned from this answer you gave but I just wondering about the
dirty
whats the use of that anyway and what does the result of that. I hope you
could ellaborate it much more so I would understand it better.

Thanks Allen.

Add an unbound text box to the form. Use the AfterUpdate event procedure
of
this control to find the matching record.

The code will be something like this:

Sub txtFind_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.txtFind) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.txtFind
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Note: If the CustomerID field is a Text field (not a Number field), you
need
extra quotes:
rs.FindFirst "[CustomerID] = """ & Me.txtFind & """"

How do I design a form that prompts a user to enter info to find a
specific
record in a table?