solieajm
asked on
Applying a filter to Main form and SubForm based on InputBox
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_Clic k
Dim Temp As Long
Temp = InputBox("Enter the Order ID", "OrderID")
DoCmd.ApplyFilter , ("OrderID = Temp")
'With Me.tblOrdersDetail_Subform 1.Form
'.Filter = "strOrderID"
'.FilterOn = True
'End With
Exit_Find_Order_Number_Cli ck:
Exit Sub
Err_Find_Order_Number_Clic k:
MsgBox err.Description
Resume Exit_Find_Order_Number_Cli ck
End Sub
Thanks.
Private Sub Find_Order_Number_Click()
On Error GoTo Err_Find_Order_Number_Clic
Dim Temp As Long
Temp = InputBox("Enter the Order ID", "OrderID")
DoCmd.ApplyFilter , ("OrderID = Temp")
'With Me.tblOrdersDetail_Subform
'.Filter = "strOrderID"
'.FilterOn = True
'End With
Exit_Find_Order_Number_Cli
Exit Sub
Err_Find_Order_Number_Clic
MsgBox err.Description
Resume Exit_Find_Order_Number_Cli
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.