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

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

Open in new window

gracie1972Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
Consider making ReasonType a required field in your main table.  That should prevent a new record from being saved if it's not populated...
gracie1972Author Commented:
That did not work if anything it just populated the Type ID with the first item in the list.  Any other ideas?
Paul Cook-GilesSenior Application DeveloperCommented:
Does TypeID have a default value?
gracie1972Author Commented:
No which is strange
gracie1972Author Commented:
I ended up adding code which seems to work:

CurrentDb.Execute ("DELETE FROM [DSBP Member Case Information] WHERE NZ(Reason_ID)= 0")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.