Link to home
Start Free TrialLog in
Avatar of AIdoHSG
AIdoHSGFlag for United States of America

asked on

Set focus on the updated record


I have a form which allows the user to see a couple of fields and update a record if the information does not match what we have in our Master DB. I'm a having an issue where when I update a record... The information is updated but it always returns the first record info.
Also i get the following message:
ODBC --- Call Fail.
[Microsoft][ODBC SQL Server Driver][SQL Server] cannot insert duplicate key row in object 'dbo.tblPhysRecruitingTargets' with unique index 'IX_tblPhysRecruitingTargets'.(#2601) [Microsoft][ODBC SQL Server Driver][SQL Driver] The statement has been terminated (#3621)

i dont' understand... I'm not inserting a duplicate record... I'm updating the record based on what the ID is and I'm setting the focus back on that ID... I've tried different variations, but nothing seems to work... If I do a Me.Setfocus everything goes smooth but I forced to scroll to the record I updated or redoing a filter... My code is below, can anyone help me out???
Private Sub cmdUpdateSurveyAddr_Click()
On Error GoTo Err_cmdUpdateSurveyAddr_Click
    ' Define SQL variables and form
   Dim strSQL_UpdateSurveyAddr As String
   Dim strFormID As String
   Dim lintPID As Long
   lintPID = Me.PID.Value
   strFormID = "frmUpdateHon"
    ' Update Alternate Phone field first
   strSQL_UpdateSurveyAddr = "UPDATE tblHonUpdate INNER JOIN qryPhysRecruitingTargets ON tblHonUpdate.PID = qryPhysRecruitingTargets.PID SET qryPhysRecruitingTargets.Honoraria_Name = [tblHonUpdate].[HonName], qryPhysRecruitingTargets.Hon_AddressLine1_ThisProject = [tblHonUpdate].[HonAddressLine1], qryPhysRecruitingTargets.Hon_AddressCity_ThisProject = [tblHonUpdate].[HonAddressCity], qryPhysRecruitingTargets.Hon_AddressState_ThisProject = [tblHonUpdate].[HonAddressState], qryPhysRecruitingTargets.Hon_AddressZip_ThisProject = [tblHonUpdate].[HonAddressZip] WHERE tblHonUpdate.PID = " & lintPID & " ;"
                Debug.Print strSQL_UpdateSurveyAddr
                DoCmd.RunSQL strSQL_UpdateSurveyAddr
                MsgBox "field has been updated with Data from Survey for PID " & lintPID & "", vbOKOnly, "Complete"
   ' Refresh form if open
  If Forms(strFormID).CurrentView <> 0 Then
        PID = lintPID
'       'Me.SetFocus
   End If
       Exit Sub
       MsgBox Err.Number & ": " & Err.Description
       Resume Exit_cmdUpdateSurveyAddr_Click
End Sub

Open in new window

Avatar of Jim P.
Jim P.
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AIdoHSG


Thank you for your help...
Glad to be of assistance. May all your days get brighter and brighter.