OpenForm using Where Criteria to subform

Is there any way to use DCmd.OpenForm to open a Single View form showing all the matching Components on the subform.

For example, I have an Orders Form that has a subform containing component numbers.  I want the Orders form to come up for say 10 records if there are 10 Orders with the component number that I'm looking for.  Here is my code which doesn't work:

  stDocName = "Orders"
  stLinkCriteria = "[Orders].[Orders Subform].[Component #]=" & "'" & Me![Component #] & "'" 'no worky
  DoCmd.OpenForm stDocName, , , stLinkCriteria
schmir1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

p912sCommented:
Is the component number a number and not text? try this...

stLinkCriteria = "[Orders].[Orders Subform].[Component #]=" & Me![Component #]

nico5038Commented:
Base your subform on a query with a "SELECT TOP 10" to limit the rows.
When opening you pass the filter (WHERE-clause) like:
stLinkCriteria = "[Component #]='" & Me![Component #] & "'"
when the Component # is a text fiedl, else:
stLinkCriteria = "[Component #]=" & Me![Component #]

Nic;o)
nico5038Commented:
Hmm, guess I should read your question better.
The Orderform with Order Components subform is a bit contradicting.
Best to have a Components form based on all components and a linked subform with orders.
Now selecting a component when opening the Component form will show automatically all orders in the subform.

Clear ?

Nic;o)
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

dqmqCommented:
Assuming the recordsource for the Orders subform includes a column called "[Component #]"  and the control on the orders subform has the same name, then:



stDocName = "Orders"
stLinkCriteria = "[Component #]=" & [Orders].[Orders Subform].[Component #]
DoCmd.OpenForm stDocName, , , stLinkCriteria
schmir1Author Commented:
p912s
--------
Component # is a string so my original statement is probably closer:
  stLinkCriteria = "[Orders].[Orders Subform].[Component #]=" & "'" & Me![Component #] & "'" 'no worky

I get a dialog as follows for either yours or mine:
  Enter Parameter Value                              
  Orders.Orders Subform.Component #
             OK                Cancel

nico5038
----------
Are you talking about redesigning the Orders form?  The Orders Subform is linked to the Orders form by ID.  I do also have a separate Component form but that's a whole separate deal.  The Orders form is for the user to Order components.  Hope I'm not misreading your question?

dgmg
---------
Not sure but isn't yours backwards.  I need to open the Order form which has a [Orders Subform] with [Component #] object.
nico5038Commented:
Hmm, guess the component selected in the orderdetail should give a list with orders also having this component.

In that case the fastest way is also to add a subform based on the orderdetail ordernumbers and component #'s like:
select distinct ordernumber, [component #] from tblOrderDetails;

Next place a (hidden) field txtComponent
In the orderdetails place in the OnCurrent event the code:
Parent.txtComponent = me.[Component #]
Finally single-click the "order-component" subform and enter for the linkage field properties under the Data tab:
Linkage Child fields: [Component #]
Linkage Master field: txtComponent

Now selecting a row in the orderdetail subform will automatically show the orders also having this component.

Nic;o)
schmir1Author Commented:
Maybe I should explain my requirements in more details.  I've got a Build of Materials form which contains component numbers.  I want to have a button on this form that will bring up all the orders that match this component number (so the user can verify that the component has been ordered).  This would be simple except the component numbers are contained on the Orders Subform.  So I want to be able to display all the Orders that have this component number on the Orders Subform.  Here is the code I tried on the button event but it doesn't work:

Private Sub cmdOrders_Click()
  Dim stDocName As String
  Dim stLinkCriteria As String

  stDocName = "Orders"
  stLinkCriteria = "[Orders].[Orders Subform].[Component #]=" & "'" & Me![Component #] & "'"  'no worky
  DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Maybe what I need to do is not possible?  I've already got queries the user can use but they don't give the user as clear a picture.
nico5038Commented:
You need a form based on the query I gave:
select distinct ordernumber, [component #] from tblOrderDetails;

Now use e.g.:
  stDocName = "frmDistinctOrderdetails"
  stLinkCriteria = "[Component #]='" & Me![Component #] & "'"  '
  DoCmd.OpenForm stDocName, , , stLinkCriteria

Nic;o)
schmir1Author Commented:
Not sure if you are saying this but if the set the RecordSource (with button code) for the Order Form to:

SELECT Orders.* FROM Orders INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]
WHERE ((([Orders Subtable].[Component #])="0100030B042"));

Where 01000030B042 is the component # that I want to filter on then all is good.

I do want to use my existing form and not make a new one.

I don't need to use stLinkCriteria if I use the above, do I?
nico5038Commented:
Create a new form named frmDistinctOrderdetails based on the query:
SELECT distinct [Orders Subtable].[Orders ID], [Orders Subtable].[Component #] FROM [Orders Subtable]
as this query for this new form.

Then use the above activation code.

Nic;o)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
The way I imagine it, your Orders Subform contains a list of the components. Now you want to click a button and see a list orders (For a selected component, I would guess). Let's backup and look at a couple design alternatives:

1. The mainform has a subform with components listed in one place and orders listed in another.

2. The mainform has a subform that alternates between listing components and orders (when the button is pressed).

3. The mainform has a subform for components and clicking the button opens a popup a form for orders

4. The mainform has a subform for components and a second subform for orders.

5. The mainform has a subform for components, which in turn has a subform for orders.


Each approach has pros/cons for both the user and the developer. I feel like we're chasing our tail because we don't really understand your vision.
schmir1Author Commented:
I needed information that is on the order form (Order Date) as well as the subform (Component #) so I found the following solution (using the Orders form's RecordSource) works for me:

Private Sub cmdOrders_Click()
  Dim stDocName As String
  Dim stLinkCriteria As String
  Dim strQuery As String

  strQuery = "SELECT Orders.* FROM Orders INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]" & _
             " WHERE [Orders Subtable].[Component #] =" & "'" & Me.txtComponentNum & "'" & _
             " ORDER BY Orders.[Order date] DESC"
  stDocName = "Orders"
  DoCmd.OpenForm stDocName, , , stLinkCriteria
  Forms![Orders].RecordSource = strQuery
End Sub

Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.