[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Applying a filter to Main form and SubForm based on InputBox

Posted on 2006-06-26
2
Medium Priority
?
593 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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