0

I have a main report (Projects Overview) which I am trying to create an OnClick event which will take me from the report to the field where that piece of information is entered on a form (LiveJobs).

My problem is that there is a subform (Estimate Items Subform) where the order items are entered. Then there is a subform on that (Production Subform) which is where the components that make up the 'Item' are entered. So a 'Desk' is ordered under 'Items' and then the components of the desk - drawer boxes, top, privacy panel are all entered on the Production Subform so they can all be tracked and monitored for production. As they are being produced there is time scheduled for each of these items in a time slot corresponding to a particular week.

In the report I want to be able to click on the time scheduled for any component and link back to the form and the corresponding week where it is scheduled and move hours around within the order form. My code will currently take me to the correct job but it wont get me to the correct 'layer' of the first subform and then to the correct layer of the component. Lets say for example the 3rd item in an order and then the 2 component of that Item.

Below is my code as it sits currently which only goes as far as trying to get to the correct item on the first subform. I figured if I could figure that out I could use the same logic to get to the correct component. This code results in a "Runtime Error '13' Type Mismatch"...I have been going round and round with this for days... Thanks in advance for any and all 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

    'find the Item in the item subform

    Dim dbs As DAO.Database
    Dim RstItem As DAO.Recordset
    Dim strItemCriteria As Integer

    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
            Debug.Print (strItemCriteria)
            If .NoMatch Then
            MsgBox "No Match Found"
            End If
           End With

   Set rs = Nothing

End Sub
David Buck
  • 3,752
  • 35
  • 31
  • 35
Jason Ackerman
  • 1

1 Answers1

0

I figured out the code. Here it is for reference.

Private Sub Estimated_hours_for_current_week_Click()
Dim frm1 As Form
Dim frm2 As Form
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
DoCmd.OpenForm "LiveJobs", _
WhereCondition:="[Job Number]=" & "'" & Me![Job Number] & "'"
Set frm1 = Forms!LiveJobs.Estimate_Items_Subform.Form
Set frm2 = Forms!LiveJobs.Estimate_Items_Subform!ProductionComponentSubform.Form
Set rst1 = frm1.Recordset.Clone
Set rst2 = frm2.Recordset.Clone
With rst1
.FindFirst "[Estimate Item subform table ID] =" & Me.Estimate_Item_subform_table_ID
If .NoMatch Then
MsgBox "Item not found"
Else
frm1.Bookmark = rst1.Bookmark
End If
End With
With rst2
.FindFirst "[Estimate details ID]=" & Me.Estimate_details_ID
If .NoMatch Then
MsgBox "project not found"
Else
frm2.Bookmark = rst2.Bookmark
Forms![LiveJobs].SetFocus
Forms![LiveJobs]![Estimate Items Subform].SetFocus
Forms![LiveJobs]![Estimate Items Subform]![ProductionComponentSubform].Form![Estimated 
hours for current week].SetFocus
End If
End With
End Sub
Jason Ackerman
  • 1