Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Reports _ Link From Report To Subform

Posted by: Jackerman Jan 6 2020, 02:22 PM

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

Posted by: theDBguy Jan 6 2020, 02:26 PM

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.

Posted by: Jackerman Jan 7 2020, 08:48 AM

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.

 

Posted by: theDBguy Jan 13 2020, 11:57 AM

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.

Posted by: Jackerman Jan 13 2020, 01:35 PM

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.

Posted by: theDBguy Jan 13 2020, 01:44 PM

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

Posted by: Jackerman Jan 13 2020, 02:41 PM

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.

Posted by: theDBguy Jan 13 2020, 02:49 PM

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.

Posted by: Jackerman Jan 13 2020, 02:57 PM

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.

Posted by: theDBguy Jan 13 2020, 03:00 PM

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...

Posted by: Jackerman Jan 13 2020, 03:07 PM

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

Posted by: theDBguy Jan 13 2020, 03:32 PM

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.

Posted by: Jackerman Jan 13 2020, 03:54 PM

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

Posted by: theDBguy Jan 13 2020, 03:59 PM

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

Posted by: Jackerman Jan 13 2020, 04:11 PM

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.

Posted by: theDBguy Jan 13 2020, 04:34 PM

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?

Posted by: Jackerman Jan 14 2020, 08:58 AM

I did and I got a "Run-Time error 13 - Type mismatch" message.

Posted by: theDBguy Jan 14 2020, 10:50 AM

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.

Posted by: Jackerman Jan 15 2020, 02:30 PM

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.

Posted by: theDBguy Jan 15 2020, 02:40 PM

Hi. Not sure how else I could be of help without being able to see what you got. Are you able to share it?

Posted by: Jackerman Jan 16 2020, 03:36 PM

In my .FindFirst statement the strCriteria needs to be defined as an integer since it is based on the subform ID. I cant get the strCriteria to Dim As Integer. I keep falling into a loop of data and type mismatches...any words of wisdom?

Posted by: Jackerman Jan 16 2020, 03:41 PM

Here is the code as it sits currently.

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

'find the Item in the item subform

Dim db As DAO.Database
Dim RstItem As DAO.Recordset
Dim strItemCriteria As Integer
strItemCriteria = [Estimate Item subform table ID] = "Me![Estimate Item subform table ID]"
Set db = CurrentDb
Set RstItem = db.OpenRecordset("Estimate Items Subform table", dbOpenDynaset, dbSeeChanges)
With RstItem
RstItem.FindFirst strItemCriteria
End With



Posted by: theDBguy Jan 16 2020, 03:55 PM

Hi. It might be more helpful if you could post a sample db for testing, so we can see what's actually happening. Is that possible?

Posted by: Jackerman Jan 17 2020, 02:26 PM

I am a bit reluctant to post the database which I know poses an issue. I think I have the code written correctly but it will not go to the record as I want it to. When I am in debug and hover over the strCriteria and other code it all seems to be finding and doing what it is supposed to but wont go to the record based on the criteria. It will find the 'Job Number' and go to the correct Job but will not find the correct 'Estimate Item Subform table ID' and go to that. Here is the code:

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
'find the Item in the item subform
Dim dbs As DAO.Database
Dim RstItem As DAO.Recordset
Dim strItemCriteria As String
Set dbs = CurrentDb
Set RstItem = dbs.OpenRecordset("Estimate Items Subform table", dbOpenDynaset)
strItemCriteria = ([Estimate Item subform table ID] = Me.Estimate_Item_subform_table_ID)
With RstItem
RstItem.MoveLast
DoEvents
RstItem.FindFirst (strItemCriteria)
If .NoMatch Then
MsgBox "No Match Found"
End If
End With
Set rs = Nothing
End Sub

Posted by: theDBguy Jan 17 2020, 04:25 PM

Hi. Other options to share your db are to email me a copy and/or we can do a web meeting with screen sharing.