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
> Query Criteria Based On Two Fields For Two Forms, Access 2016    
 
   
JAchord
post Aug 19 2019, 01:09 PM
Post#1



Posts: 138
Joined: 11-July 14



Can anyone help me with this query. I have a form that gives item information. I need to see what jobs this item was used in based on the original ItemIDKey and the SupercededIDKey for items that are obsolete. I have another form that is identical to the first. The first form list all items used in the construction of the original item. The second form opens to show the information for one of these items when the item is double clicked. Both forms use the same "WhereUsedSFM" subform based on the same query. I just added the supercededIDKey. I tried changing the master and child link for the subform for two criteria but this is an "and" link, I need an "or" link.

CODE
SELECT Jobstbl.CompleteJob, Jobmatl.MatlItemIDKey, Jobstbl.item, Jobstbl.JobItemIDKey, Jobmatl.sequence, Jobstbl.[ord-num], Jobmatl.[bom-seq], Jobstbl.JobIDKey
FROM Jobstbl RIGHT JOIN (Itemstbl INNER JOIN Jobmatl ON Itemstbl.ItemIDKey = Jobmatl.MatlItemIDKey) ON Jobstbl.JobIDKey = Jobmatl.MatlJobIDKEY
GROUP BY Jobstbl.CompleteJob, Jobmatl.MatlItemIDKey, Jobstbl.item, Jobstbl.JobItemIDKey, Jobmatl.sequence, Jobstbl.[ord-num], Jobmatl.[bom-seq], Jobstbl.JobIDKey
HAVING (((Jobmatl.MatlItemIDKey)=[Forms]![ItemDatafrm]![ItemIDKey] Or (Jobmatl.MatlItemIDKey)=[Forms]![ItemDatafrm]![SupercedeItemIDKey] Or ((Jobmatl.MatlItemIDKey)=[Forms]![ItemDatapopupfrm]![ItemIDKey] Or (Jobmatl.MatlItemIDKey)=[Forms]![ItemDatapopupfrm]![SupercedeItemIDKey])));

This post has been edited by JAchord: Aug 19 2019, 01:16 PM
Go to the top of the page
 
theDBguy
post Aug 19 2019, 01:15 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,577
Joined: 19-June 07
From: SunnySandyEggo


Hi. What's wrong with it?

--------------------
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
 
JAchord
post Aug 19 2019, 01:17 PM
Post#3



Posts: 138
Joined: 11-July 14



Sorry accidently hit submit the first time before I was ready. I am using the query builder. See updated original post.
Go to the top of the page
 
JAchord
post Aug 19 2019, 01:18 PM
Post#4



Posts: 138
Joined: 11-July 14



Ok thoughts are to scattered right now. The query works fine. The problem is It ask for the criteria for the form that is not open. How can I keep it from doing that.
Go to the top of the page
 
theDBguy
post Aug 19 2019, 01:20 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,577
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks for the additional information. To be honest, it's hard to see what you mean without being familiar with your database. However, I don't think you need to use a Totals query for this, since it doesn't look like you're performing any aggregation of the data.

--------------------
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
 
JAchord
post Aug 19 2019, 01:34 PM
Post#6



Posts: 138
Joined: 11-July 14



No the groupby is just to keep from having duplicates. Groupby is much quicker than Unique values. Over 1.3 mill records on one of the tables slows things down. The query works fine. I just need to figure out how to keep it from asking for the criteria for the form that is not open. I can use VBA to detect when a form is open but is there a way to do this in a query? I think I really need to start using form instances instead of using two separate forms. It is really annoying to have to update both forms anyway any time I make a change. I have never done this and am not sure how to get it done in 2016.
Go to the top of the page
 
theDBguy
post Aug 19 2019, 01:37 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,577
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just a thought, but maybe you could use a DLookup() instead of a Form reference.

--------------------
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
 
JAchord
post Aug 19 2019, 03:19 PM
Post#8



Posts: 138
Joined: 11-July 14



I will have to try that.
Go to the top of the page
 
theDBguy
post Aug 19 2019, 03:27 PM
Post#9


Access Wiki and Forums Moderator
Posts: 76,577
Joined: 19-June 07
From: SunnySandyEggo


Hi. Good luck! If it doesn't work, you might consider posting a sample db, so we can better understand your requirements. Cheers!

--------------------
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
 
JAchord
post Aug 20 2019, 08:42 AM
Post#10



Posts: 138
Joined: 11-July 14



That didn't quite work either unfortunately. It works great with one form. I guess I will just have to use two forms with two separate queries. I was trying to clean the DB up by removing duplicates. I tried Allen Browns method of using client groups but that just opens the same form with the same record filter. I need a way to filter the next instance for a new record. I guess I can move over to the Forms section and ask about that.
Go to the top of the page
 
JAchord
post Aug 20 2019, 09:36 AM
Post#11



Posts: 138
Joined: 11-July 14



Well having a different query didn't work since both forms use the same subform. crazy.gif I should have thought of that. Guess I could make duplicate subforms. That doesn't seem like the proper way to handle this.
Go to the top of the page
 
theDBguy
post Aug 20 2019, 11:19 AM
Post#12


Access Wiki and Forums Moderator
Posts: 76,577
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you need more help with this, you might consider posting a sample copy of your db, so we can give it a try for you.

--------------------
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
 
JAchord
post Sep 6 2019, 08:55 AM
Post#13



Posts: 138
Joined: 11-July 14



Hey DBguy. I think I figured it out but I keep getting type mismatch in the SQL string. Does not make sense seeing as one value is an autonumber and the rest are just copies of that auto numbers for different fields. I did remove the master child link for this form. instead passing the filter to the sub-form when the master form opens. It was the only way I could think of using "or" instead of "and".

CODE
Private Sub Form_Load()
Dim JobfiltercbxSQL As String
Dim whereusedSQL As String

JobfiltercbxSQL = "SELECT Jobstbl.JobItemIDKey, Jobstbl.CompleteJob " & vbCrLf & _
"FROM Jobstbl " & vbCrLf & _
"WHERE (((Jobstbl.JobItemIDKey)=[ItemIDKey]));"
Me.JobFiltercbx.RowSource = JobfiltercbxSQL

whereusedSQL = "[MatlItemIDKey]=" & [ItemIDKey]  & "Or [MatlItemIDKey]=" & [SupercedeItemIDKey]

Me.WhereUsedSFM.Form.Filter = whereusedSQL
Me.WhereUsedSFM.Form.FilterOn = True

DoCmd.Hourglass False

End Sub
Go to the top of the page
 
theDBguy
post Sep 6 2019, 09:38 AM
Post#14


Access Wiki and Forums Moderator
Posts: 76,577
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you got it sorted out. Good luck with your project.

--------------------
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
 
JAchord
post Sep 6 2019, 10:04 AM
Post#15



Posts: 138
Joined: 11-July 14



Actually I don't have it sorted out. Do you see a syntax issue with the whereusedSQL line in the code. could it be because one the linked feild is an autonumber and one master field is an autonumber and the other is a number field?
Go to the top of the page
 
theDBguy
post Sep 6 2019, 11:11 AM
Post#16


Access Wiki and Forums Moderator
Posts: 76,577
Joined: 19-June 07
From: SunnySandyEggo


Hi. When you concatenate strings with variables, it's safer to include extra spaces to avoid run-on sentences. Hope it helps...

--------------------
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
 
JAchord
post Sep 6 2019, 11:19 AM
Post#17



Posts: 138
Joined: 11-July 14



I figured it out. So the method to have a subform based off two or more forms is as follows. No parent to child link, and place similar code as this in the form load event. This also has a method of making a combo box with a filtered rowsource based on a subform.

CODE
Dim JobfiltercbxSQL As String
Dim whereusedSQL As String

JobfiltercbxSQL = "SELECT Jobstbl.JobIDKey, Jobstbl.CompleteJob " & _
"FROM Jobstbl " & _
"WHERE (((Jobstbl.JobItemIDKey)=[ItemIDKey]));"
Me.JobFiltercbx.RowSource = JobfiltercbxSQL

whereusedSQL = "(((Jobmatl.MatlItemIDKey)= [itemidkey] Or (Jobmatl.MatlItemIDKey)= [SupercedeItemIDKey]))"

Me.WhereUsedSFM.Form.Filter = whereusedSQL
Me.WhereUsedSFM.Form.FilterOn = True

DoCmd.Hourglass False
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2019 - 05:09 PM