Existing Data in field of subform also filtered by combo box on main form (1 Viewer)

[Ramnik]

Hello everyone,
I have a form with subform (datasheet) .

On my subform i have one field which shows the
productId ( which is filtered from combo boxes on main form) .
productId contains two columns
1 bound column(id) ( hidden with width 0 )
2 column (desription) ( shown with width 2 )

Now after entering the data when i come back to the form again . It hides the data from the productId field on subform , because data in combo box (on main form) doesn't match. Although the row is showing up , only the text in field (productId) is hidden.

Now when i change data in combo box on main form to match the ProductId , then the productId field text shows again.

after changing combo box it hides . What i need is the existing data should not hide when i change the combo box selection.

see pic 1 where product matches and pic 2 where not.
please note i am using master child relationship on the form/subform.

Please help and ask for more info if needed.

Thanks .

 

[Uncle Gizmo]

"Plane" does not appear on the spanner either?

 

[Uncle Gizmo]

"Plane" does not appear on the spanner either?

Ah! it's a Plane finish spanner....

Combination, Full Polish, Chrome vanadium, Pittsburgh?, Chrome Finish...

 

[Uncle Gizmo]

COM.FP.CV.PIT.CHR.1/4

So does selecting Pittsburgh or Plane change the coded entry:

COM.FP.CV.PIT.CHR.1/4
COM.FP.CV.PLA.CHR.1/4

PIT or PLA ???

 

[Ramnik]

No the ProductId doesn't change after updating the combo box . It shows again when changing the combo box to PIT again . What i need is to not to hide when the filter is applied . Thanks

 

[Uncle Gizmo]

Please answer all these questions, even if your answer is: Huh?

1)
I read your question as if it was working, but something has changed and now it doesn't work. Is this the case?

2)
You Say: >>> please note i am using master child relationship on the form/subform.<<<
Which fields are common? ie, Child and Master?

 

[Ramnik]

everything is working fine . But i don't want the productId for existing data hidden as i change product type through combo boxes.

The connected fields are the Pack Id which is Hidden column in subform (datasheet).

 

[Ramnik]

Please look at attachments again. Only the product Id relevant to combo boxes show. Although every other type is present(but hidden.

 

[Uncle Gizmo]

>>>The connected fields are the Pack Id which is Hidden column in subform (datasheet).<<<

Please show the Pack id's and post an image or images..

 

[Uncle Gizmo]

How does the "Filter product by" control the records returned in the subform.

Please post the code...

 

[Ramnik]

Hello ,
Attached are the images which includes row source for productId, table design and form with master child relation.

 

[Ramnik]

I think I could not provide required information for this question .

 

[Uncle Gizmo]

If I understand the problem, and I'm not 100% sure because it's too complicated (the way you presented it).

From this image:

It looks like your subform results set is controlled by criteria. The criteria is provided by the combobox selections under "Filter Product By".

Am I Correct?

 

[Uncle Gizmo]

In This Image:
"PITTSBURG" is Selected and you can see the product ref...

In This Image:
"PlANE" is Selected and you cannot see the product ref...

But in both cases 9 records are returned, is this correct? Do you have exactly 9 records "PLANE" and 9 records "PITTSBURG".. ?

If you do, then delete a couple of records from one or other of the groups and see if 7 and 9 records are returned when you make your selection.

BACK UP FIRST!!!!

 

[Ramnik]

<<<<It looks like your subform results set is controlled by criteria. The criteria is provided by the combobox selections under "Filter Product By".

Am I Correct?>>>>>

Yes you are correct, the criteria is provided by the filter combo boxes.

<<<<<<But in both cases 9 records are returned, is this correct? Do you have exactly 9 records "PLANE" and 9 records "PITTSBURG".. ?>>>>>>

No the records are only 9 ( i.e. only pittsburgh ) . After updating the combo boxes only the product name hides although the records still the same ( i.e pittsburgh).
If i have the plane products then if filter is "plane" it will show the product name but if filter is other "e.g. pittsburgh" it also hides the product name ....


The condition is "after updating the combo boxes the product name show/hide according to filter although the entire row is there".
only the product name field hides. ( which is product id ( width 0) and product Name (width 2).

Thanks

 

[Uncle Gizmo]

PLEASE DISREGARD MY PM.... My PC was playing up, not the forum...

I have attached a sample database which shows how the criteria in the sub form should control the number of records returned by the sub form, as controlled by the combo on the main form...

People suffer from the misconception that if a combo box does not show anything, then the record set controlled by the combo box should return all the records. However, if there is no value in the combo box, then no records will be returned because there's no match.

Anyway have a look at my example and you should see why there is an issue with your set up which I can't quite identify.

Its not behaving exactly how I would expect it to, there is either something your database is doing that you haven't explained or it's something I don't know. Getting to grips with both or either should prove interesting....

 

[Ramnik]

Thanks, But my master/child relationship is behaving exactly how i need .
The problem is only with the product Id field on subform which is filtered by combo boxes on main form .The previously entered rows for particular set remains there even after updating the filter , But the particular field (Product Name ) which has two columns in it , hides the text only . It shows data only when a relevant filter is applied according to product , although data is always there .

 

[Uncle Gizmo]

Thanks, But my master/child relationship is behaving exactly how i need .

Well, not exactly is it!

Lets try a different approach, use the packid from here..... http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=52971&d=1394600263

As the criteria in a query.

Have the query return the following fields from tbl_MasterMfg. packid, id, product, quantity, description,

publish an image of the records returned.

Also publish the sql text of the query.

 

[Ramnik]

Here is what you need

sql

SELECT tbl_PackDetail.PackId, tbl_PackDetail.Id, tbl_PackDetail.MfgProductId, tbl_MasterMfg.ProductName, tbl_PackDetail.Description, tbl_PackDetail.Qty, tbl_PackDetail.Remarks
FROM tbl_PackMaster INNER JOIN (tbl_MasterMfg INNER JOIN tbl_PackDetail ON tbl_MasterMfg.ProductId = tbl_PackDetail.MfgProductId) ON tbl_PackMaster.PackId = tbl_PackDetail.PackId
WHERE (((tbl_PackDetail.PackId)="S09INCFC4C"));


and see the attached images ...
Thanks

 

[Uncle Gizmo]

Here is what you need

sql

SELECT tbl_PackDetail.PackId, tbl_PackDetail.Id, tbl_PackDetail.MfgProductId, tbl_MasterMfg.ProductName, tbl_PackDetail.Description, tbl_PackDetail.Qty, tbl_PackDetail.Remarks
FROM tbl_PackMaster INNER JOIN (tbl_MasterMfg INNER JOIN tbl_PackDetail ON tbl_MasterMfg.ProductId = tbl_PackDetail.MfgProductId) ON tbl_PackMaster.PackId = tbl_PackDetail.PackId
WHERE (((tbl_PackDetail.PackId)="S09INCFC4C"));


and see the attached images ...
Thanks

No not that ... Don't want the joins....

Try:


SELECT tbl_PackDetail.PackId, tbl_PackDetail.Id, tbl_PackDetail.MfgProductId, tbl_MasterMfg.ProductName, tbl_PackDetail.Description, tbl_PackDetail.Qty, tbl_PackDetail.Remarks
FROM tbl_PackMaster
WHERE (((tbl_PackDetail.PackId)="S09INCFC4C"));