Full Version: Search Combobox On Nested Subform
UtterAccess Forums > Microsoft® Access > Access Forms
Hi Everyone

I have a Main Form named "frmFastenersAndTypes"

With a Subform named "frmFastenerTypesSubform"

Withing the Subform I have a Nested Subform named "frmPartNumbersSubform"

In the Header of the Nested Subform the user needs to be able to Search for a PartNumber.

In the nested subform header I have a Combobox named "cboPartNr" with the following Row Source:-

SELECT tblPartNumbers.PartNumberID, tblPartNumbers.PartNumber FROM tblPartNumbers WHERE (((tblPartNumbers.PartNumberID)=[Forms]![frmFastenersAndTypes]![frmFastenerTypesSubform]![frmPartNumbersSubform].Form![PartNumberID]));

When used it only displays 1 part number but there are 3 that should be displayed.

Any thoughts on how to rectify this ??

Any help appreciated.
Is the nested subform set for continuous forms or datasheet?
Hi John


Try entering Like as oppose to equals for the criteria.

Hi RazMaddaz

Same result just 1 Part Number shows
In the Sub Form does it say one of three records?

Also, often when you make changes to a sub Form, the sub Form needs to be dragged into the main detail section again. Might you have changed the sub form from a Single Form, Datasheet type, etc.?

Larry Larsen
Hi Mike

Am I understanding right your want to populate the combo..!!

Why not just use the "PartNumberID" on the nested sub form as your reference..

SELECT tblPartNumbers.PartNumberID, tblPartNumbers.PartNumber FROM tblPartNumbers WHERE tblPartNumbers.PartNumberID = Me.PartNumberID

I would also assume the PartNumberID is linked back through the other sub forms..
Hi Raz

Here with a snapshot of the Form

Click to view attachment

As you see the record count is 3

PS I should have said the Nested subform is set as Single Form View
Hi Larry

The user wants to use the Combobox to Show only those Partnumbers that have been previously entered for this particular Fastener

The Main Form is displaying the fastener "Bolt"

The Subform is displaying "Hex" which is the fastener Type

The Nested Subform which is set as a Single Form is displaying the First partnumber saved ie 0309200

There are 2 other Partnumbers stored for this specific Bolt - Hex

The user want to show in the Combobox all 3 of the Part Numbers available and be able to select it rather than using the Navigation Buttons

Don't know if this is possible??
Hi Larry & Raz

I got it to work by changing the structure of the Form

I now have the Main Form
With the Subform1 frmFastenerTypesSubform"

I removed the nested Subform2 and placed it below the Subform1 and used an Unbound textbox to link Master and Child using this:-


Now the Combox has the following Row Source:-

SELECT tblPartNumbers.PartNumberID, tblPartNumbers.PartNumber, tblPartNumbers.FastenerTypeID FROM tblPartNumbers WHERE (((tblPartNumbers.FastenerTypeID)=[Forms]![frmFastenersAndTypes]![frmFastenerTypesSubform]![FastenerTypeID]));

Works a treat
Click to view attachment

Many thanks for your help

Larry Larsen
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.