Link to home
Start Free TrialLog in
Avatar of gracie1972
gracie1972Flag 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.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

Avatar of Paul Cook-Giles
Paul Cook-Giles
Flag of United States of America image

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

ASKER

That did not work if anything it just populated the Type ID with the first item in the list.  Any other ideas?
Does TypeID have a default value?
No which is strange
ASKER CERTIFIED SOLUTION
Avatar of gracie1972
gracie1972
Flag of United States of America image

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