troubleshooting Question

After I Update record in subform, an extra record is added in the data table in Access

Avatar of gracie1972
gracie1972Flag for United States of America asked on
Microsoft Access
5 Comments1 Solution58 ViewsLast Modified:
I have a subform linked to a main form in access that updates a main table based on the H_ID.  

See Code Below:
Everything works flawless EXCEPT, when I am done updating and click my button the code is behind (cmdAddReason_Click) it works.  

The issue is before the code starts and I click from my main form to my subform a new record a blank line is added and assigned a record ID (Example we will say I clicked new member and record ID 123 is created).
The code as it functions below updates each record id as a selection is made, however, the original record ID (Record ID 123) is blank.  

Any suggestions on how to fix this?

My table will look like this:
Date         Record ID   H ID          Reason Type       Reason
1/11/18   123                M12        
1/11/18   124                M12        Other                    Other
1/11/18   125                M12        Consult                Med Review
1/11/18   126               M12         Follow-up             Reschedule

Private Sub cmdAddReason_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
  
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("DSBP Member Case Information", dbOpenDynaset, dbAppendOnly)

'Make sure a selection has been made
  If Me.lstReason.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Reason"
    Exit Sub
  End If
  
'Add selected value(s) to table
  Set ctl = Me.lstReason
  For Each varItem In ctl.ItemsSelected

    rs.AddNew
    rs!Reason_ID = ctl.ItemData(varItem)
    rs!Type_ID = Me.cboTypeID
    rs!DSBP_Date = Me.DSBP_Date
    rs!H_ID = Me.H_ID
    rs!DSBP_ID = Me.DSBP_ID
    rs!Pharmacist_ID = Me.Pharmacist_ID
    rs!Care_Manager_ID = Me.Care_Manager_ID
    rs!Reason = ctl.Column(0)
    If ctl.ItemData(varItem) = 8 Then
        rs!Other = Me.txtOther
        End If
    ctl.Selected(varItem) = False
    rs.Update
   
Next varItem

'Housekeeping
ExitHandler:
For Each varItm In Me.lstReason.ItemsSelected
Me.lstReason.Selected(varItm) = False

Next
Me.lstReason.Selected(0) = True

Set rs = Nothing
Set db = Nothing

Exit Sub

End Sub
ASKER CERTIFIED SOLUTION
gracie1972

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros