Existing Data in field of subform also filtered by combo box on main form

[Ramnik]

The results will be exactly the same my friend . There is no difference at all .

 

[Uncle Gizmo]

The results will be exactly the same my friend . There is no difference at all .

Ah! I see what's happened you have miss understood my simple instructions...

I said:

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.

But you have published an image not of the query results but of something else as indicated by the dropdown box I have highlighted:

Please Post an image of the actual query results: Include the name of the query in the image here is an example to help you:

NOTE the Name of the Query appears in the top left, I want to see something similar in the image you post...

 

[Uncle Gizmo]

I have had a flash of inspiration.....

I think the problem with the query not returning the results I was expecting is that you have the "Display Control" in your table set to "Combo Box"

See Image HERE:

What you need to do is change "Combo Box" to "Text Box" for all the fields in the table THEN (with bit of luck) we should see the cause of the problem....

BACK UP First!!!!

 

[Ramnik]

check it . But i don't think it will make any difference at all...


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


Thanks...

 

[Uncle Gizmo]

check it . But i don't think it will make any difference at all...

We are making progress. It's a process of elimination...

Next could you post an image of the subform, but NOT in Datasheet view, but in design View...

BTW, if the problem doesn't surface soon then I may need to look at your DB, so you may want to prepare a copy by removing confidential data, remove objects not related to the problem, and I will need it in Access 2007, or below format.

Don't forget, post the subform image in Design view.

 

[Uncle Gizmo]

check it . But i don't think it will make any difference at all...

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

Thanks...

Damn... I didn't spot that! You've gone back to the query with a join in it...

That's NOT what's required....

This is what I'm After:

In other words the records from tbl_PackDetail ONLY where the Criteria is: "S09INCFC4C"

Please post an image of the records returned, and I like your idea of posting an image of the query in design view, that's helpful, so post an image of that as well please...

 

[Ramnik]

Here are the images.
Hope we will find something about it.


Thanks.

 

[Uncle Gizmo]

In the following image I have circled a combo box.

Please post the SQL statement for the combo box....

See the image below on how to do this:

Select the Combobox, then open the property sheet and copy the text in the row named "Row Source"

 

[Ramnik]

I ave already sent it to you .
Here it is attached again.

Thanks

 

[Uncle Gizmo]

I ave already sent it to you .
Here it is attached again.

Thanks

No .. Thats not it... Please read my post carefully, I need the sql of the combo box it's self...

It will be a string something like:

SELECT Table2.ID, Table2.Textxxx FROM Table2;

 

[Ramnik]

SELECT tbl_MasterMfg.ProductId, tbl_MasterMfg.ProductName
FROM tbl_CategoryMaster INNER JOIN tbl_MasterMfg ON tbl_CategoryMaster.CategoryId = tbl_MasterMfg.Category
WHERE (((tbl_MasterMfg.TypeName)=[Forms]![frm_PackMaster]![cbo_FilterTypeM]) AND ((tbl_MasterMfg.TypeDesign)=[Forms]![frm_PackMaster]![cbo_FilterSubtypeM]) AND ((tbl_MasterMfg.TypeMaterial)=[Forms]![frm_PackMaster]![cbo_FilterMaterialM]) AND ((tbl_MasterMfg.TypeBrand)=[Forms]![frm_PackMaster]![cbo_FilterBrandM]) AND ((tbl_MasterMfg.TypeFinish)=[Forms]![frm_PackMaster]![cbo_FilterFinishM]) AND ((tbl_CategoryMaster.SerialNo)=[Forms]![frm_PackMaster]![Frame50]));

 

[Uncle Gizmo]

Why such a complicated sql statement for what is basically a lookup? It should be much simpler than that? Please explain?

 

[Ramnik]

Because its based on the combo boxes on the form , Thats why.

Its simple actually
Select [results[ from [tablename[ where [(filter1 & filter2 & ... )]

 

[Uncle Gizmo]

SELECT tbl_MasterMfg.ProductId, tbl_MasterMfg.ProductName
FROM tbl_CategoryMaster INNER JOIN tbl_MasterMfg ON tbl_CategoryMaster.CategoryId = tbl_MasterMfg.Category
WHERE (((tbl_MasterMfg.TypeName)=[Forms]![frm_PackMaster]![cbo_FilterTypeM]) AND ((tbl_MasterMfg.TypeDesign)=[Forms]![frm_PackMaster]![cbo_FilterSubtypeM]) AND ((tbl_MasterMfg.TypeMaterial)=[Forms]![frm_PackMaster]![cbo_FilterMaterialM]) AND ((tbl_MasterMfg.TypeBrand)=[Forms]![frm_PackMaster]![cbo_FilterBrandM]) AND ((tbl_MasterMfg.TypeFinish)=[Forms]![frm_PackMaster]![cbo_FilterFinishM]) AND ((tbl_CategoryMaster.SerialNo)=[Forms]![frm_PackMaster]![Frame50]));

Try this as the row source for your combo box:

SELECT tbl_MasterMfg.ProductId, tbl_MasterMfg.ProductName
FROM tbl_CategoryMaster

 

[Ramnik]

It will list all the rows of product Name, In this case it will a hell lot harder for user to find the product from 1000 products .

 

[Ramnik]

Is there any other method to filter the productName from combo boxes???????

 

[Uncle Gizmo]

It will list all the rows of product Name, In this case it will a hell lot harder for user to find the product from 1000 products .

Did the data display correctly when you used the simpler SQL statement in the row source?

 

[Ramnik]

Yes it does.
Obviously it will do it But now how would i select a new product from near 1000 products ????? How do i filter it?

 

[Uncle Gizmo]

Yes it does.
Obviously it will do it But now how would i select a new product from near 1000 products ????? How do i filter it?

Congratulations! We now we have it working OK! Your original question is answered.

Now you have another question!

I suggest starting a new thread.

In your new thread I suggest asking a question like:

I have a subform on a main form.
The subform has a combo box which selects and displays the data ok, however when selecting records with it, you have a list of 1000's to choose from! This is cumbersome and difficult to use.

Is there any way of having the combo box return a more limited set of Records?

For Your Information:
I have tried adding a set of combo boxes to the main form to limit the number of records returned, but found that this caused issues with displaying the data correctly.

 

[Ramnik]

Ohhhhhhhhh!!!!!! You are GREAT!!!!! Its been like 2 weeks i'm stuck in this problem and giving you as much info i can and after that what have u came up with ????????????

and you think you solved it ????????

Is it you call a solution ?

well i don't want to say anything .

Thanks for ur time .
see u next time ...