Filtering records using a form

[Guest]

I want to use a form to allow users to edit previously entered items into a
form (now stored in a table called Master). However, these users only will
want to see the associated records that they entered as a filtered data set.
I have a field called Entered_By that is storing that information. What
would be the easiest way to allow this filtering to take place? Not sure if
I should just use a query and a combo box or what. I want to make sure once
they enter the 'Entered_By' field that all associated records come up and
then they can scroll through them to decide which one to edit.

Also does it make sense to have two separate forms - one strictly for new
data and a different one for editing/finding. Right now that is the route I
am taking, as to ensure the users start with a fresh blank form for new
entries, and do not accidentially type over another record.

Thanks.

 

[Guest]

Hi, Brad.

What
would be the easiest way to allow this filtering to take place? Not sure if
I should just use a query and a combo box or what.

Good choice. It's very little effort on your part. Create a query such as
the following:

SELECT DISTINCT Entered_By
FROM tblMyTable
WHERE (ISNULL(Entered_By) = FALSE)
ORDER BY Entered_By;

Replace tblMyTable with the name of your table. Save the query and name it.

Your form must be bound to the table or query that holds the records you
want to filter. Open your form in Design View and open the Properties dialog
window. Create a new, unbound combo box. On the "Data" tab of the
Properties dialog window, select the Row Source Property combo box and select
the name of your new query. Select the "Event" tab and select the
OnAfterUpdate( ) event's build button and paste the following code into the
form's code module:

Private Sub cboEntered_By_AfterUpdate()

On Error GoTo ErrHandler

DoCmd.OpenForm Me.Name, , , "Entered_By = '" & _
Me!cboEntered_By.Column(0) & "'"

Exit Sub

ErrHandler:

MsgBox "Error in cboEntered_By_AfterUpdate( ) in" & vbCrLf _
& Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where cboEntered_By is the name of the combo box.

Save and compile the code, then return to the form and close the Properties
dialog window and open the form in Form View. Select an item in the combo
box to display the filtered records.

Also does it make sense to have two separate forms - one strictly for new
data and a different one for editing/finding.

Usually not. Maintenance requires making changes to two forms, not one,
which can become a pain, not to mention will take twice as long to perform.
Instead, use a single form and set the form's Data Entry Property to Yes for
data entry so that the form opens on a single, blank record. Set this
property to No for viewing multiple records already in the database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thanks - Worked like a charm.

Hi, Brad.


Good choice. It's very little effort on your part. Create a query such as
the following:

SELECT DISTINCT Entered_By
FROM tblMyTable
WHERE (ISNULL(Entered_By) = FALSE)
ORDER BY Entered_By;

Replace tblMyTable with the name of your table. Save the query and name it.

Your form must be bound to the table or query that holds the records you
want to filter. Open your form in Design View and open the Properties dialog
window. Create a new, unbound combo box. On the "Data" tab of the
Properties dialog window, select the Row Source Property combo box and select
the name of your new query. Select the "Event" tab and select the
OnAfterUpdate( ) event's build button and paste the following code into the
form's code module:

Private Sub cboEntered_By_AfterUpdate()

On Error GoTo ErrHandler

DoCmd.OpenForm Me.Name, , , "Entered_By = '" & _
Me!cboEntered_By.Column(0) & "'"

Exit Sub

ErrHandler:

MsgBox "Error in cboEntered_By_AfterUpdate( ) in" & vbCrLf _
& Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

. . . where cboEntered_By is the name of the combo box.

Save and compile the code, then return to the form and close the Properties
dialog window and open the form in Form View. Select an item in the combo
box to display the filtered records.


Usually not. Maintenance requires making changes to two forms, not one,
which can become a pain, not to mention will take twice as long to perform.
Instead, use a single form and set the form's Data Entry Property to Yes for
data entry so that the form opens on a single, blank record. Set this
property to No for viewing multiple records already in the database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

['69 Camaro]

You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.