Solved

Applying a filter to Main form and SubForm based on InputBox

Posted on 2006-06-26
2
577 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 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