Searching for data using forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Enquiry database that contains 3 forms for the different types of
info held i.e. Customer, Enquiry Lines and Order details. You can go
backwards an forwards through these screens for any selected enquiry. Can
anyone suggest how I can automate a filter by form from a button on the
opening screen of the application and how I allow the user to go from screen
to screen within the search. Also, if you filter on a table and then say Yes
to keep the changes, you finish up with a table that only contains the
filtered data. Is there anyway of stopping this?
 
You can go
backwards an forwards through these screens for any selected enquiry. Can
anyone suggest how I can automate a filter by form from a button on the
opening screen of the application and how I allow the user to go from screen
to screen within the search.
Click to expand...

If you have a one-to-many relationship between Customer and Enquiry Lines
and a one-to-many relationship between Customer and Order details, then the
easiest way would be to create a main form with two subforms. Display the
Customer records on the main form and Enquiry Lines and Order details in each
of the two subform controls. Searching for and selecting a certain customer
would automatically filter the records in the Enquiry Lines and Order details
subforms for this customer.

One could automate the Filter-by-form feature by placing a button on the
main form with the following code:

Private Sub SearchBtn_Click()

On Error GoTo ErrHandler

RunCommand acCmdFilterByForm

Exit Sub

ErrHandler:

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

End Sub

.. . . where SearchBtn is the name of the button.

Alternatively, if the three forms are bound to related tables then the form
for the Customer table could have buttons that open the other two forms to
records filtered on the current Customer record. For example:

Private Sub OrdersBtn_Click()

On Error GoTo ErrHandler

DoCmd.OpenForm "frmOrders", , , "CustID = " & Me!txtCustID.Value

Exit Sub

ErrHandler:

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

End Sub

.. . . where the primary key of the Customer table is CustID, the foreign
key for the Order details table is CustID, the form bound to the Order
details table is named frmOrders, and the text box on the current form that
displays the CustID is named txtCustID.
Also, if you filter on a table and then say Yes
to keep the changes, you finish up with a table that only contains the
filtered data. Is there anyway of stopping this?
Click to expand...

Yes. Open the table in Design View, then right-click on the table's Title
Bar, then select Properties from the pop-up menu to open the Properties
dialog window. Delete the entry for the Filter Property, then close the
Properties dialog window and save the table. Open the table in Datasheet
View and all the records appear.

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.
 
Back
Top