Lost focus event causing loss of real click event.

[joel009]

Hi all!!!! I have a form that I update a comment field using the Lost Focus event. The problem is the the Lost Focus event causes the program to lose the event that caused the Lost Focus event in the first place. Say the user changes the comments and then hits Close. The comments get updated properly but the form does not close, the user has to click Close a 2nd time to get the form to close.
here is the code I have in the Lost Focus event:
Private Sub txtRC2New_LostFocus()

Dim strcmbRouteValue As String
Dim strCurrentRecord As String

Me.Dirty = False

strcmbRouteValue = Me.cmbRoute.Value
strCurrentRecord = Forms!frmBaseRouteAssignment![subfrmRouteEfficiencyDetail].Form!txtPartNumber.Value

If Me.txtRC2 <> Me.txtRC2New Then
'update tblRouteInfo
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblRouteInfo SET tblRouteInfo.RouteComment2 = [Forms]![frmBaseRouteAssignment]![txtRC2New]" _
& " WHERE (((tblRouteInfo.PlantCode)= " & "'" & Me.txtPlantCode.Value & "'" & ")" _
& " AND ((tblRouteInfo.Route)= " & "'" & Me.cmbRoute.Value & "'" & ")" _
& " AND ((tblRouteInfo.ModelYear)= " & "'" & Me.txtModelYear.Value & "'" & "));"
DoCmd.SetWarnings True
End If

Me.Requery

Me.cmbRoute.Value = strcmbRouteValue
Me.Recordset.FindFirst "Route = " & "'" & strcmbRouteValue & "'" & ""

End Sub

Must be late in the day because I am drawing a complete blank on this. How do I capture the button or field or whatever is being clicked on the form and then execute after the Lost Focus event in the Comment field?


Joel

 

[joel009]

Wow - 24 hrs no response at all? I either stumped the stars or this is really so simple no one can believe I asked?

I tried using the After Update event and the same thing happens, the event is triggered and followed through but the action that causes the Lost Focus or After Update, like clicking on the close button, is ignored.

Any hints at all?

Thanks,
Joel

Joel

 

[dhookom]

Be patient. Why are you requerying the current form?
Is the current form based on tblRouteInfo?

Duane
Hook'D on Access
MS Access MVP

 

[joel009]

Hi dhookom,
Current form based on:
SELECT tblRouteInfo.Route, tblRouteInfo.Department, tblRouteInfo.ModelType, tblRouteInfo.JobType, tblRouteInfo.Validated, tblRouteInfo.NumberofDrivers, tblRouteInfo.AddlMinPerShift, Sum(sel_tblPRA_AllocationsDNR.LFAllocation) AS SumOfLFAllocation, Sum(sel_tblPRA_AllocationsDNR.DockAllocation) AS SumOfDockAllocation, Sum(sel_tblPRA_AllocationsDNR.TotalAllocation) AS SumOfTotalAllocation
FROM tblRouteInfo INNER JOIN sel_tblPRA_AllocationsDNR ON (tblRouteInfo.Route = sel_tblPRA_AllocationsDNR.Route) AND (tblRouteInfo.PlantCode = sel_tblPRA_AllocationsDNR.PlantCode)
WHERE (((tblRouteInfo.PlantCode)="04025") AND ((tblRouteInfo.ModelYear)="2011"))
GROUP BY tblRouteInfo.Route, tblRouteInfo.Department, tblRouteInfo.ModelType, tblRouteInfo.JobType, tblRouteInfo.Validated, tblRouteInfo.NumberofDrivers, tblRouteInfo.AddlMinPerShift;
Where sel_tblPRA_AllocationsDNR =
SELECT tblPartRouteAssignment.PlantCode, tblPartRouteAssignment.Route, tblPartRouteAssignment.Station, tblPartRouteAssignment.PartNumber, tblPartRouteAssignment.ModelYear, tblPartRouteAssignment.LFAllocation, tblPartRouteAssignment.DockAllocation, tblPartRouteAssignment.TotalAllocation
FROM tblPartRouteAssignment
ORDER BY tblPartRouteAssignment.Route;

But I think you may be on to something, why am I requerying the form. Is it really necessary?

Joel

 

[dhookom]

Could you place the aggregated information from sel_tblPRA_AllocationsDNR in a subform so the only table in the form's record source is tblRouteInfo? Then you don't need to run an update query against the table to edit the comments.

Duane
Hook'D on Access
MS Access MVP

 

[joel009]

Duane, Already have a tab control in the detail section with 5 tabs. I have bound txt boxes(invisible) and unbound txt boxes(visible) for a few other elements too. I don't think I need to do the form requery as the unbound text boxes can be left alone, they display the correct info and I only need to write it to the table which happens in the lost focus event. I think I over engineered it and have to back up a bit. I took out the requery step and it is behaving as expected.

Thanks, brain fart on that one.

Joel

 

[TheAceMan1]

joel009's SQL from [blue]12 Aug 11 9:59[/blue] ... formatted for better viewing:

[blue]SELECT RI.Route,
       RI.Department, 
       RI.ModelType, 
       RI.JobType, 
       RI.Validated, 
       RI.NumberofDrivers, 
       RI.AddlMinPerShift, 
       Sum(PAD.LFAllocation) AS SumOfLFAllocation, 
       Sum(PAD.DockAllocation) AS SumOfDockAllocation, 
       Sum(PAD.TotalAllocation) AS SumOfTotalAllocation
FROM tblRouteInfo AS [purple][b]RI[/b][/purple] 

INNER JOIN sel_tblPRA_AllocationsDNR AS [purple][b]PAD[/b][/purple]
ON (RI.Route = PAD.Route) AND 
   (RI.PlantCode = PAD.PlantCode)

WHERE (((RI.PlantCode)="04025") AND 
       ((RI.ModelYear)="2011"))

GROUP BY RI.Route, 
         RI.Department, 
         RI.ModelType, 
         RI.JobType, 
         RI.Validated, 
         RI.NumberofDrivers, 
         RI.AddlMinPerShift;

Where PAD = SELECT PRA.PlantCode, 
                   PRA.Route, 
                   PRA.Station, 
                   PRA.PartNumber, 
                   PRA.ModelYear, 
                   PRA.LFAllocation, 
                   PRA.DockAllocation, 
                   PRA.TotalAllocation
FROM tblPartRouteAssignment AS [purple][b]PRA[/b][/purple]

ORDER BY PRA.Route;[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]