UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filter Issue, Any Version    
 
   
2ME
post Nov 4 2019, 08:42 AM
Post#1



Posts: 683
Joined: 13-May 10
From: Egypt


Hi,

I have a table for tests (TestID, Test), a tables for processes (ProcessID, Process), a table for products (ProductID, ItemDesc), and Conjunction table (TestID, ProcessID, ProductID)

and I have a main form to insert Quality Report (Contains ProcessID, ProductID) and Subform (contains TestiD) which been filter against products and Processes

the problem here, is that if I insert a quality report and removed the row of a test from conjunction table the row is there but the tests disappear from the combobox

is there any suggestion to help me figure this out

Best Regards

--------------------

M.E.M
Go to the top of the page
 
theDBguy
post Nov 4 2019, 08:50 AM
Post#2


UA Moderator
Posts: 76,792
Joined: 19-June 07
From: SunnySandyEggo


Hi. It me easier to understand what you mean if you could post a demo version of your db.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2ME
post Nov 4 2019, 09:15 AM
Post#3



Posts: 683
Joined: 13-May 10
From: Egypt


in the form image attached the testID in subform is filtered a gainst ProductID and ProcessID in main form

if I removed the a specific row from the Conjunction table (Processid, productid, testid) the testid combobox in subform doesn't view the test but the row exists.

the row source of testID
SQL
SELECT tbl_Test.TestID, tbl_Test.Test, tbl_Conjunction.ProcessID
FROM tbl_Test INNER JOIN tbl_Conjunction ON tbl_Test.TestID = tbl_Conjunction.TestID
WHERE (((tbl_Conjunction.ProcessID)=[forms]![Edit quality report].[processid]) AND ((tbl_Conjunction.ProductID)=[forms]![Edit quality Report].[productid]));

This post has been edited by 2ME: Nov 4 2019, 09:22 AM
Attached File(s)
Attached File  Form.png ( 77.71K )Number of downloads: 5
 

--------------------

M.E.M
Go to the top of the page
 
theDBguy
post Nov 4 2019, 12:05 PM
Post#4


UA Moderator
Posts: 76,792
Joined: 19-June 07
From: SunnySandyEggo


Are you not using the linked master/child fields for your subform? If you do that, couldn't you then take away the filter? Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2ME
post Nov 4 2019, 01:17 PM
Post#5



Posts: 683
Joined: 13-May 10
From: Egypt


I am using the linked master/child fields (InspID) for the subform, I couldn't take away the filter as Each product has its own tests and if I removed the filter I will select a test from huge number of tests, that's why when I select a product and process in main form and filter tests based on controls on main form would be easier

my problem that if I removed a test from the conjunction table I have a row with hidden test which is weried

I really appreciate your help

--------------------

M.E.M
Go to the top of the page
 
theDBguy
post Nov 4 2019, 01:24 PM
Post#6


UA Moderator
Posts: 76,792
Joined: 19-June 07
From: SunnySandyEggo


Hi. When you say it's weird, are you expecting the record to be deleted as well when you delete the test? If so, do you have referential integrity turned on with cascade delete?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2ME
post Nov 4 2019, 01:37 PM
Post#7



Posts: 683
Joined: 13-May 10
From: Egypt


"are you expecting the record to be deleted as well when you delete the test?"
I expect the test will be viewed and override the filter as it is already saved in the table of the subform

I have a thought to add date from and date to fields to the conjunction table and never delete a test

--------------------

M.E.M
Go to the top of the page
 
theDBguy
post Nov 4 2019, 01:46 PM
Post#8


UA Moderator
Posts: 76,792
Joined: 19-June 07
From: SunnySandyEggo


Hi. From the sound of your descriptions, you are expecting the impossible. How can something show up if you have deleted it? For example, here's what I understand is happening. Let's say you have a lookup table for Tests like so:

ID, TestDesc
1, Is it tall?
2, Is it heavy?
3, Is it big?
4, Is it wide?
5, Is it small?

Then, you have the junction table like:

ID, ProductID, TestID, Result
1, 1, 1, Yes
2, 1, 2, Yes
3, 1, 3, Yes
4, 1, 4, No
5, 1, 5, No

So, if you change your Tests table and delete, say ID # 4, then your junction table would still have the row with ID #4 show up with a TestID of 4 because it's a separate table you haven't updated. Now, if TestID is a Combobox on your junction table (or your subform), then it would show up as "blank" because there is no matching ID in the Tests table, so there's no Desc value to display in the combo.

Hope that makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2ME
post Nov 4 2019, 02:07 PM
Post#9



Posts: 683
Joined: 13-May 10
From: Egypt


Sorry if can't make it clear

I have a table for tests (TestID, Test), a tables for processes (ProcessID, Process), a table for products (ProductID, ItemDesc), AND Conjunction table (TestID, ProcessID, ProductID)

I Have two other tables, the first table for main Quality record (inspid, processID, Product ID, ....) and table for sub quality record (ispid, TestID, Scrap, Defect)

for example

tbl_test
TestID Test
1' Visual Test
2 degreasing
3 length

tbl_Process
ProcessID Process
1 Production
2 Final inspection

tbl_Products
ProductID Product
1 Nozzle
2 nuts and bolts
3 terminal

tbl_conjunction

ProductID Processid testID
1 1 1
1 1 2
1 1 3

when i insert the quality record in main form I select the processid and productid then using filter on subform to select the tests

Ex, If I select Product 1 and process 1 I will get tests 1,2 and 3 in the subform and that works correctly

what happen is when I save the record of quality and subquality form

The Problem appears when I delete for example thisvrecord ProductID Processid testID from table (tbl_conjunction)
1 1 1
The test still there in the table of subquality but not viewed on test filter of subform




--------------------

M.E.M
Go to the top of the page
 
theDBguy
post Nov 4 2019, 02:14 PM
Post#10


UA Moderator
Posts: 76,792
Joined: 19-June 07
From: SunnySandyEggo


I think the only way to fully understand what you're saying is if you could post a sample db with test data and tell us how to duplicate the problem.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th December 2019 - 04:50 PM