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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Link From Report To Subform, Access 2016    
 
   
Jackerman
post Jan 6 2020, 02:22 PM
Post#1



Posts: 13
Joined: 6-January 20



Hello all,

I am trying to link a Master Report (Production Report Birdseye view w/ CO & WO) to the underlying master form (LiveJobs). In the form there is a subform (Estimate Items Subform) Which handles the 'Items' in the order. Then there is a subform within that form (ProductionComponentSubform) that handles the components that make up the items and tracks each of those pieces via work orders. On the Master Report there is a field (Estimated hours for current week) which relates to the subform (ProductionComponentSubform) and the hours associated with the work order. I am trying to create an onClick event that takes the user from the report to the exact entry on the form to make changes to the hours. I have been able to open the form to the correct Job Number and set focus to the correct subform but cant seem to get the final piece of the puzzle where it will find the correct 'Work Order'. Here is the code as it stands now which ends up with the result "No Match Found" . I know my naming convention is screwed up already so we can check that box off and hopefully move to a solution for the task at hand. FYI the version of Access that I am working on is 365 but I selected 2016 since I figured that was the closest version in the ComboBox. Thanks all!!

Private Sub Estimated_hours_for_current_week_Click()
Dim strWhere As String
Dim DocName As String
DocName = "LiveJobs"
strWhere = "[Job Number]=" & "'" & Me![Job Number] & "'"
DoCmd.OpenForm DocName, acNormal, , strWhere
Forms![LiveJobs].[Estimate Items Subform].SetFocus
Forms![LiveJobs]![Estimate Items Subform]![ProductionComponentSubform].SetFocus
Forms![LiveJobs]![Estimate Items Subform]![ProductionComponentSubform].Form![txtWorkOrder].SetFocus
Dim strCriteria As String
Dim Rst As DAO.Recordset
strCriteria = "[Work Order]= " & "'" & Me![Work Order] & "'"
Debug.Print strCriteria
Set Rst = Forms![LiveJobs]![Estimate Items Subform]![ProductionComponentSubform].Form.RecordsetClone
Rst.MoveFirst
Rst.FindFirst strCriteria
If Rst.NoMatch Then
MsgBox "No match found"
Else
Forms![LiveJobs]![Estimate Items Subform]![ProductionComponentSubform].Form.Bookmark = Rst.Bookmark
End If
End Sub
Go to the top of the page
 
theDBguy
post Jan 6 2020, 02:26 PM
Post#2


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

Are you actually talking about a report object embedded within a form with a subform as well? If you're trying to manipulate the subform based on a result from the report, are you able to duplicate the calculation on the report to the subform? Just curious... I really don't have a clue yet what your form looks like.

--------------------
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
 
Jackerman
post Jan 7 2020, 08:48 AM
Post#3



Posts: 13
Joined: 6-January 20



Hey DBGuy,

Thanks for responding! No, the report is not embedded in the form. The report is a stand alone report that our team will use to manage production. It is a broad overview of all the jobs and items/components that make up those jobs. It brings together a labor hour planning module and all of the hrs of production that are assigned to each component which can be planned for the next 8 weeks. When we are in a meeting and the production manager is reviewing the report I want him to be able to click on the hours planned for a specific component in a specific week and link back to that specific entry in the form 'LiveJobs' so he can change those hrs if necessary and then jump back to the report, re query and continue on. Right now I have it to the point where I can click on the field in the report and it will open the form, find the job based on the job number in the report and then set focus on the subform (items) and then the next level subform(components) but I cant get it to find the Work Order for the component and then setfocus on the specific 'week of' for the planned hours. I hope that all makes sense. I attached a couple of jpg's for visual. In 'Prod overview' you can see the '10' that is circled. I want to be able to click on that and have it take me to the '10' that is circled in 'Live Job form 2' that is attached. As you will see I have circled the 'job number'
and the corresponding 'work order' plus the specific week that these hours fall under. I also attached the other 'Live jobs 1' jpg to show how the 'Item' subform and 'Component' subforms look since the other jpg was shifted right and you couldn't really get a good look at the form in #2. Thanks for your help with this.
Attached File(s)
Attached File  Live_Job_Form_1.JPG ( 204.14K )Number of downloads: 4
Attached File  Live_Job_Form_2.JPG ( 203.65K )Number of downloads: 3
Attached File  Prod_overview.JPG ( 168.15K )Number of downloads: 3
 
Go to the top of the page
 
theDBguy
post Jan 13 2020, 11:57 AM
Post#4


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks for trying to explain, but without knowing anything about your db file or seeing it in action, I'm still at a lost. If you can "manually" do what you want to happen after you click on the report, then we should be able to "automate" it. I just need to know those steps you would have taken to make it happen manually.

--------------------
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
 
Jackerman
post Jan 13 2020, 01:35 PM
Post#5



Posts: 13
Joined: 6-January 20



Maybe the code will help:

Private Sub Estimated_hours_for_current_week_Click()
Dim strWhere As String
Dim DocName As String
DocName = "LiveJobs"
strWhere = "[Job Number]=" & "'" & Me![Job Number] & "'"
DoCmd.OpenForm DocName, acNormal, , strWhere

Forms![LiveJobs].[Estimate Items Subform].SetFocus
Forms![LiveJobs]![Estimate Items Subform]![ProductionComponentSubform].SetFocus

Dim strCriteria As String
strCriteria = "[Work Order]= " & """ & Me![Work Order] & """

Forms![LiveJobs]![Estimate Items Subform]![ProductionComponentSubform].Form![txtWorkOrder].SetFocus

DoCmd.FindRecord Forms![LiveJobs]![Estimate Items Subform]![ProductionComponentSubform].Form![txtWorkOrder], , True, , True

This gets me to the 'Job Number' I need to be on and gets me to the secondary subform but falls short of searching the 'Work Orders' and finding and going to the 'Work Order' I should be on. Hopefully this helps.
Go to the top of the page
 
theDBguy
post Jan 13 2020, 01:44 PM
Post#6


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


Hi. Are you saying the last line of that code is not working?

--------------------
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
 
Jackerman
post Jan 13 2020, 02:41 PM
Post#7



Posts: 13
Joined: 6-January 20



Yessir! I need to use the defined strCriteria to find the [Work Order] on the secondary subform and go to that record. Main form is 'Live Jobs' - 1st subform is 'Estimate items subform' and the 2nd subform is 'ProductionComponentSubform' which is where the [Work Order] is located.
Go to the top of the page
 
theDBguy
post Jan 13 2020, 02:49 PM
Post#8


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


Okay, that's making sense now. Yes, you'll need a criteria, so you can't use the FindRecord method because I don't see a criteria argument for it. Try using SearchForRecord instead.

--------------------
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
 
Jackerman
post Jan 13 2020, 02:57 PM
Post#9



Posts: 13
Joined: 6-January 20



Tried that...still cant get past the first 'Layer'....it goes to the first 'work order' of the first 'Job number'. I had set the criteria in the previous code. I tried the searchfor command with 'strCriteria' and as "[Work Order]= " & """ & Me![Work Order] & """. same result each time.
Go to the top of the page
 
theDBguy
post Jan 13 2020, 03:00 PM
Post#10


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


Did you try "stacking" the criteria? For example, in the first level, you might have something like:

"FirstLevelField=JobNumber"

So, then in the second level, you might try something like:

"FirstLevelField=JobNumber AND SecondLevelField=WorkOrder"

Just a thought...

--------------------
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
 
Jackerman
post Jan 13 2020, 03:07 PM
Post#11



Posts: 13
Joined: 6-January 20



Gives me a "Run-Time error 13 - Type mismatch"

Private Sub Estimated_hours_for_current_week_Click()
Dim strWhere As String
Dim DocName As String
DocName = "LiveJobs"
strWhere = "[Job Number]=" & "'" & Me![Job Number] & "'" And "[Work Order]= " & "'" & Me![Work Order] & "'"
DoCmd.OpenForm DocName, acNormal, , strWhere
Go to the top of the page
 
theDBguy
post Jan 13 2020, 03:32 PM
Post#12


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


At least you're getting somewhere instead of a syntax error. You just need to fix the type mismatch issue. If the field is a number, make sure you don't enclose the value in quotes and vice versa.

--------------------
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
 
Jackerman
post Jan 13 2020, 03:54 PM
Post#13



Posts: 13
Joined: 6-January 20



I took out the "[Job Number]=" & "'" & Me![Job Number] & "'" And portion and only used the [Work Order] = """ & Me![Work Order] & """ portion to test. The result yielded no messages but was a blank form. Here's the current code.

Private Sub Estimated_hours_for_current_week_Click()
Dim strWhere As String
Dim DocName As String
DocName = "LiveJobs"
strWhere = [Work Order] = """ & Me![Work Order] & """
DoCmd.OpenForm DocName, acNormal, , strWhere
Go to the top of the page
 
theDBguy
post Jan 13 2020, 03:59 PM
Post#14


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


Hey, by the way, what happened to the SearchForRecord approach?

--------------------
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
 
Jackerman
post Jan 13 2020, 04:11 PM
Post#15



Posts: 13
Joined: 6-January 20



Tried that...still cant get past the first 'Layer'....it goes to the first 'work order' of the first 'Job number'. I had set the criteria in the previous code. I tried the searchfor command with 'strCriteria' and as "[Work Order]= " & """ & Me![Work Order] & """. same result each time.
Go to the top of the page
 
theDBguy
post Jan 13 2020, 04:34 PM
Post#16


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


Well, without actually seeing your db, I am thinking you'll always get this problem because you're only using one level of criteria. Remember when I said to "stack" them up? Did you try that?

--------------------
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
 
Jackerman
post Jan 14 2020, 08:58 AM
Post#17



Posts: 13
Joined: 6-January 20



I did and I got a "Run-Time error 13 - Type mismatch" message.
Go to the top of the page
 
theDBguy
post Jan 14 2020, 10:50 AM
Post#18


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


So, did you try to fix the type mismatch error? Like I said earlier, make sure your field data type and your literal value match in type. In other words, compare text to text and numbers to numbers, etc.

--------------------
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
 
Jackerman
post Jan 15 2020, 02:30 PM
Post#19



Posts: 13
Joined: 6-January 20



I switched gears and tried a Dlookup function instead, however when the code runs it is not going to the record based on the strCriteria. Still struggling with which function is best for going to a record. As a quick review, I want to click on a field in a report and have it take me to the exact field on a subform that is on a subform of a parent form based on the master job number and the specific work order that is clicked on withing that job. I came to the realization that I need to search the first subform for the Item and then have the search proceed to the component and search for the 'Work Order' that needs to be adjusted. One of my issues is figuring out which 'search' function is the correct one - Dlookup, DoCmd Goto Record, DoCmd Find Record, Navigate Record, .FindFirst...I ruled out GoTo record but that about it. My spidey senses tell me that .FindFirst is the way to go but I am nearing the end of my rope trying to figure this out.
Go to the top of the page
 
theDBguy
post Jan 15 2020, 02:40 PM
Post#20


UA Moderator
Posts: 78,167
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not sure how else I could be of help without being able to see what you got. Are you able to share 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
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    6th June 2020 - 06:46 PM