run update code and set focus back to main form

  • Thread starter Cyberwolf0000 via AccessMonster.com
  • Start date
C

Cyberwolf0000 via AccessMonster.com

I am having an issue with running a piece of code that updates another table
when I exit the subform. I was using the subform_Exit event along with the
lostfocus event on a control on the subform. I can't get the focus back on
the main form. I get Error 2110 Can't move the focus to control SchedDate on
the lostfocus event. Here are the 2 relevant pieces of code

Private Sub EndView_LostFocus()

' Error Handler
On Error GoTo EndView_LostFocus_Error

If IsNull(Me.EndView) Then
Forms!frm_MfgPerfData!SchedDate.SetFocus
End If

On Error GoTo 0
Exit Sub

EndView_LostFocus_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
EndView_LostFocus of VBA Document Form_frm_Endviews_subform"
End Sub


Private Sub frm_Endviews_subform_Exit(Cancel As Integer)

' Error Handler
On Error GoTo frm_Endviews_subform_Exit_Error
Dim strFlds As String
Dim strVals As String
Dim strSQL As String
Dim strTemp As String
Dim rec As DAO.Recordset
Dim strJobNum As String

Forms!frm_MfgPerfData!cmbJobNumID.SetFocus
strJobNum = Forms!frm_MfgPerfData!cmbJobNumID.Column(1)
'Checks to see if there is currently a record in the EstToAct Table
If DCount("EstToActID", "EstToAct", "[Production__]='" & strJobNum & "'
AND [Operation__]=" & Forms!frm_MfgPerfData!OpCode) = 0 Then
'If there isn't a record then build up 2 strings ciontaining the
information to be added to EstToAct
'Start with the Production # and Operation #
strFlds = "Production__, Operation__"
'Get the values from the current record on the form
strVals = "'" & strJobNum & "', " & Forms!frm_MfgPerfData!cmbOpCode.
Column(0)

'If the Press field is not blank add to the string variables
If Not IsNull(Forms!frm_MfgPerfData!Press) Then
strFlds = strFlds & ", Press"
strVals = strVals & ", " & Forms!frm_MfgPerfData!Press
End If
'If the NumWebs field is not blank then add it to the 2 string variables
If Not IsNull(Forms!frm_MfgPerfData!NumWebs) Then
strFlds = strFlds & ", Webs"
strVals = strVals & ", " & Forms!frm_MfgPerfData!NumWebs
End If

'If the Formats field is not blank the add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!Formats) Then
strFlds = strFlds & ", Format_Changes"
strVals = strVals & ", " & Forms!frm_MfgPerfData!Formats
End If
'If the ChipPan fields is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!ChipPan) Then
strFlds = strFlds & ", ChipPan"
strVals = strVals & ", " & Forms!frm_MfgPerfData!ChipPan
End If
'If the Devices field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!Devices) Then
strFlds = strFlds & ", Devices"
strVals = strVals & ", " & Forms!frm_MfgPerfData!Devices
End If
'If the Heads field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!Heads) Then
strFlds = strFlds & ", Heads"
strVals = strVals & ", " & Forms!frm_MfgPerfData!Heads
End If
'If the TOMR field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!TOMR) Then
strFlds = strFlds & ", MR_Est_Hrs"
strVals = strVals & ", " & Forms!frm_MfgPerfData!TOMR
End If
'If the TJobHrs field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!TJobHrs) Then
strFlds = strFlds & ", Est_Hrs_"
strVals = strVals & ", " & Forms!frm_MfgPerfData!TJobHrs
End If
' If the TRWaste field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!TRWaste) Then
strFlds = strFlds & ", Run_Waste_Estimate"
strVals = strVals & ", " & Forms!frm_MfgPerfData!TRWaste
End If

strSQL = "SELECT EndView, EndviewID " & _
"FROM Endviews " & _
"WHERE MfgPerfDataID = " & Forms!frm_MfgPerfData!MfgPerfDataID &
_
" ORDER BY EndViewID;"
Set rec = CurrentDb().OpenRecordset(strSQL)
If rec.RecordCount > 0 Then
rec.MoveFirst
strFlds = strFlds & ", EV__"
strVals = strVals & ", '" & rec!EndView & "'"
End If
If rec.RecordCount > 1 Then
rec.MoveNext
Do Until rec.EOF
strTemp = strTemp & "'" & rec!EndView & "'" & vbCrLf
rec.MoveNext
Loop
strFlds = strFlds & ", Misc_comments"
strVals = strVals & ", " & strTemp
End If

Call AddNewEntry("EstToAct", strFlds, strVals)

End If

Any help would be greatly appreciated.

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2
 
C

Cyberwolf0000 via AccessMonster.com

Repost.
I am having an issue with running a piece of code that updates another table
when I exit the subform. I was using the subform_Exit event along with the
lostfocus event on a control on the subform. I can't get the focus back on
the main form. I get Error 2110 Can't move the focus to control SchedDate on
the lostfocus event. Here are the 2 relevant pieces of code

Private Sub EndView_LostFocus()

' Error Handler
On Error GoTo EndView_LostFocus_Error

If IsNull(Me.EndView) Then
Forms!frm_MfgPerfData!SchedDate.SetFocus
End If

On Error GoTo 0
Exit Sub

EndView_LostFocus_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
EndView_LostFocus of VBA Document Form_frm_Endviews_subform"
End Sub

Private Sub frm_Endviews_subform_Exit(Cancel As Integer)

' Error Handler
On Error GoTo frm_Endviews_subform_Exit_Error
Dim strFlds As String
Dim strVals As String
Dim strSQL As String
Dim strTemp As String
Dim rec As DAO.Recordset
Dim strJobNum As String

Forms!frm_MfgPerfData!cmbJobNumID.SetFocus
strJobNum = Forms!frm_MfgPerfData!cmbJobNumID.Column(1)
'Checks to see if there is currently a record in the EstToAct Table
If DCount("EstToActID", "EstToAct", "[Production__]='" & strJobNum & "'
AND [Operation__]=" & Forms!frm_MfgPerfData!OpCode) = 0 Then
'If there isn't a record then build up 2 strings ciontaining the
information to be added to EstToAct
'Start with the Production # and Operation #
strFlds = "Production__, Operation__"
'Get the values from the current record on the form
strVals = "'" & strJobNum & "', " & Forms!frm_MfgPerfData!cmbOpCode.
Column(0)

'If the Press field is not blank add to the string variables
If Not IsNull(Forms!frm_MfgPerfData!Press) Then
strFlds = strFlds & ", Press"
strVals = strVals & ", " & Forms!frm_MfgPerfData!Press
End If
'If the NumWebs field is not blank then add it to the 2 string variables
If Not IsNull(Forms!frm_MfgPerfData!NumWebs) Then
strFlds = strFlds & ", Webs"
strVals = strVals & ", " & Forms!frm_MfgPerfData!NumWebs
End If

'If the Formats field is not blank the add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!Formats) Then
strFlds = strFlds & ", Format_Changes"
strVals = strVals & ", " & Forms!frm_MfgPerfData!Formats
End If
'If the ChipPan fields is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!ChipPan) Then
strFlds = strFlds & ", ChipPan"
strVals = strVals & ", " & Forms!frm_MfgPerfData!ChipPan
End If
'If the Devices field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!Devices) Then
strFlds = strFlds & ", Devices"
strVals = strVals & ", " & Forms!frm_MfgPerfData!Devices
End If
'If the Heads field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!Heads) Then
strFlds = strFlds & ", Heads"
strVals = strVals & ", " & Forms!frm_MfgPerfData!Heads
End If
'If the TOMR field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!TOMR) Then
strFlds = strFlds & ", MR_Est_Hrs"
strVals = strVals & ", " & Forms!frm_MfgPerfData!TOMR
End If
'If the TJobHrs field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!TJobHrs) Then
strFlds = strFlds & ", Est_Hrs_"
strVals = strVals & ", " & Forms!frm_MfgPerfData!TJobHrs
End If
' If the TRWaste field is not blank then add it to the 2 variables
If Not IsNull(Forms!frm_MfgPerfData!TRWaste) Then
strFlds = strFlds & ", Run_Waste_Estimate"
strVals = strVals & ", " & Forms!frm_MfgPerfData!TRWaste
End If

strSQL = "SELECT EndView, EndviewID " & _
"FROM Endviews " & _
"WHERE MfgPerfDataID = " & Forms!frm_MfgPerfData!MfgPerfDataID &
_
" ORDER BY EndViewID;"
Set rec = CurrentDb().OpenRecordset(strSQL)
If rec.RecordCount > 0 Then
rec.MoveFirst
strFlds = strFlds & ", EV__"
strVals = strVals & ", '" & rec!EndView & "'"
End If
If rec.RecordCount > 1 Then
rec.MoveNext
Do Until rec.EOF
strTemp = strTemp & "'" & rec!EndView & "'" & vbCrLf
rec.MoveNext
Loop
strFlds = strFlds & ", Misc_comments"
strVals = strVals & ", " & strTemp
End If

Call AddNewEntry("EstToAct", strFlds, strVals)

End If

Any help would be greatly appreciated.
Click to expand...

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

PowerBuilder calling a Word macro 0
Working outlook via vba code with xlsx - error 0
VBA Export to PDF 0
Update a date field in sub-form from dates fields on main form 1
VBA - exporting serial letters to single documents 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Syntax error in Update statement 3
Referencing Forms 5

Top