Subform Query

  • Thread starter Thread starter boba
  • Start date Start date
B

boba

Hi. I am having a problem. I have a main form (NewSampleRequest) with a
subform (SampleRequestItemsSubform) which handles the transactions for
the main form. Basically we have "Items" which need to be added to a
Sample Request, so I do this in a subform. However, these Items can
belong to multiple "Sets", so the user needs to be able to select a set
in the subform as well. So we created a query which narrows down the
list of available sets to drop down list to only those related records
in a table called "Transactions". Everything works perfectly if I do it
stand alone in the form "SampleRequestItemsSubform". However when I do
it as a subform, I get a Runtime Error 2450. Please advise how I can
fix the problem.

Here is the code for the two subform fields in question.

Item ID Field-After Update

Private Sub cmbSampleRequestItemItemID_AfterUpdate()

[Forms]![SampleRequestItemsSubform].Recalc

End Sub

Set ID field-Row Source

SELECT Transactions.SetID, Transactions.ItemID
FROM Transactions
WHERE
(((Transactions.ItemID)=[Forms]![SampleRequestItemsSubform]![SampleRequestItemItemID]));

Thank you.
 
The Forms collection contains currently open forms. When you open the
subform by itself, it is a member of this collection so
[Forms]![SampleRequestItemsSubform] works. When you open it as a subform, it
is not a member of the Forms collection, so
[Forms]![SampleRequestItemsSubform] generates an error "Can't find the form
referenced in your code...".

If your are only referencing the subform from the subform itself, simply
change "[Forms]![SampleRequestItemsSubform]" to "Me". If that doesn't
address all your problems, the following has a lot of subform syntax
examples which you may find useful:
http://www.mvps.org/access/forms/frm0031.htm

When referencing a subform from outside the subform you have to use the Form
property of the control that contains the subform. Access' wizards usually
give that control the same name as the subform, so this can be confusing
(one reason to always rename the control yourself...). But the point is that
when used as a subform the subform's name becomes useless. It can't be used
for anything. The name of it's container control may be the same, but that's
only coincidence and can easily disguise what you're really doing.

HTH,
 
Back
Top