Solved

Applying a filter to Main form and SubForm based on InputBox

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

792 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