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

gracie1972
gracie1972 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

Commented:
Consider making ReasonType a required field in your main table.  That should prevent a new record from being saved if it's not populated...

Author

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 Developer

Commented:
Does TypeID have a default value?

Author

Commented:
No which is strange
I ended up adding code which seems to work:

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial