Solved

Applying a filter to Main form and SubForm based on InputBox

Posted on 2006-06-26
2
581 Views
Last Modified: 2007-12-19
I have a main form and a subform in Access.  I would like to apply a filter to both based on an input box.  The input box will prompt for the orderID.  OrderID is a field in both the main and subform.  I have the following but it keeps asking me for Temp.

Thanks.

Private Sub Find_Order_Number_Click()
On Error GoTo Err_Find_Order_Number_Click
Dim Temp As Long
Temp = InputBox("Enter the Order ID", "OrderID")
DoCmd.ApplyFilter , ("OrderID = Temp")
'With Me.tblOrdersDetail_Subform1.Form
'.Filter = "strOrderID"
'.FilterOn = True
'End With
Exit_Find_Order_Number_Click:
    Exit Sub

Err_Find_Order_Number_Click:
    MsgBox err.Description
    Resume Exit_Find_Order_Number_Click
 
End Sub
0
Comment
Question by:solieajm
2 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16987866
I think I understand what you're trying to do....but there's another way to do this.
Your main form is based on a recordset. this recordset is either a table or a query of some kind. It in turn is linked to your subform using OrderID I'm assuming. By the very nature of subforms, it will limit it's results based on the Linked field on the main form.

That being said, what you need to do with the OrderID you have the user input is to change the underlying recordset for the main form...ie, filter using the OrderID as criteria.
Ex:

Temp = InputBox("Enter the Order ID", "OrderID")
strSQL = "Select * from SomeTable where OrderID =" & Temp
me.main.recordset = strSQL
me.main.requery


You could produce the same results by creating a dropdown combo box on your main form that displays all the OrderID values and lets you select one. this in turn will create a new recordset for the main form using the item you select to filter the results as mentioned above. Part of the problem you're having is moving that entry from your pop-up form back to the main form.
0
 
LVL 10

Accepted Solution

by:
kirenievs earned 250 total points
ID: 16988339
If main and sub are propely linked you do not need to set filter on the subform

Private Sub Find_Order_Number_Click()
On Error GoTo Err_Find_Order_Number_Click

Dim Temp As Long
Temp = InputBox("Enter the Order ID", "OrderID")
With Me
.Filter = "OrderID=" & Temp
.FilterOn = True
End With

Exit_Find_Order_Number_Click:
    Exit Sub

Err_Find_Order_Number_Click:
    MsgBox err.Description
    Resume Exit_Find_Order_Number_Click
 
End Sub

You think you should also should think about how to handle this if the user push Cancel or Ok without any values or with non integer values entered.

If you still need to filter the sub, this might work:

Private Sub Find_Order_Number_Click()
On Error GoTo Err_Find_Order_Number_Click
Dim Temp As Long

Temp = InputBox("Enter the Order ID", "OrderID")

With Me.tblOrdersDetail_Subform1.Form
   .Filter = "OrderID=" & Temp
   .FilterOn = True
End With

Exit_Find_Order_Number_Click:
    Exit Sub

Err_Find_Order_Number_Click:
    MsgBox err.Description
    Resume Exit_Find_Order_Number_Click
 
End Sub
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question