Avatar of gracie1972
Flag for United States of America asked on

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!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
Next varItem

For Each varItm In Me.lstReason.ItemsSelected
Me.lstReason.Selected(varItm) = False

Me.lstReason.Selected(0) = True

Set rs = Nothing
Set db = Nothing

Exit Sub

End Sub

Open in new window

Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon
Paul Cook-Giles

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

That did not work if anything it just populated the Type ID with the first item in the list.  Any other ideas?
Paul Cook-Giles

Does TypeID have a default value?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

No which is strange

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question