Solved DAO to update records (1 Viewer)

[Kayleigh]

Hi, Trying to code a function which finds the record selected in listbox and sets fldDefault as True. Also resets existing fldDefault to False (if different from current selection).
Here is my code - not sure how to continue...

Private Sub cmdDefault_Click()
Dim X As Integer
    Dim i As Variant
    Dim DBS As DAO.Database
    Dim rst As DAO.Recordset
    Dim td As DAO.TableDef

        
   Set DBS = CurrentDb
 
    Set td = DBS.TableDefs!jtblStaffRoles
    Set rst = td.OpenRecordset(dbOpenDynaset)
    
    For Each i In Me.lstSelectedPositions.ItemsSelected
    
        rst.FindFirst "fldStaffPositionID = " & Me.lstSelectedPositions.ItemData(i) & " And fldStaffID = " & Me.fldStaffID
        If Not rst.fldDefaultPosition = True Then
            rst!fldDefaultPosition = True
        End If
        
        Debug.Print Me.lstSelectedPositions.ItemData(i)
        On Error Resume Next
        rst.Update
    
    Next i
        
    rst.Close
    Set rst = Nothing
    Set td = Nothing
    Me.Refresh
    
    
    Exit Sub
End Sub

 

[MajP]

Demo

Public Sub SetDefault(staffID As Long, positionID As Long)
  Dim strSql As String
  strSql = "Update jtblStaffRoles set fldDefaultPos = 0 where fldStaffid = " & staffID
  CurrentDb.Execute strSql
  strSql = " Update jtblStaffRoles set fldDefaultPos = -1 where fldStaffID = " & staffID & " AND fldStaffPositionID = " & positionID
  CurrentDb.Execute strSql
  Debug.Print strSql
End Sub

 

[Kayleigh]

Thanks @MajP for that efficient piece of code!
Would you be able to assist me with adding that to the update and remove commands - so it checks if the record selected is default and then either reassigns to other field (if removed) or to selected field (if adding new)?
Many thanks!

Private Sub cmdRemove_Click()
Dim X As Integer
    Dim i As Variant
    Dim DBS As DAO.Database
    Dim rst As DAO.Recordset
    Dim td As DAO.TableDef
    Dim D As Boolean
    
        
   Set DBS = CurrentDb
 
    Set td = DBS.TableDefs!jtblStaffRoles
    Set rst = td.OpenRecordset(dbOpenDynaset)
    
    For Each i In Me.lstSelectedPositions.ItemsSelected
    
    rst.FindFirst "fldStaffPositionID = " & Me.lstSelectedPositions.ItemData(i) & " And fldStaffID = " & Me.fldStaffID
        If Not rst.EOF And Not rst.BOF Then
            rst.Delete
        End If
        
        rst.FindFirst "fldStaffID = " & Me.fldStaffID
            If Not rst.EOF And Not rst.BOF Then
                    rst.Edit
                        rst!fldDefaultPos = True
                    rst.Update
            End If
    
    Next i
    rst.Close
    Set rst = Nothing
    Set td = Nothing
    Me.Refresh
    

    
    Exit Sub


End Sub

Private Sub cmdUpdate_Click()

    Dim X As Integer
    Dim i As Variant
    Dim DBS As DAO.Database
    Dim rst As DAO.Recordset
    Dim td As DAO.TableDef
        
   Set DBS = CurrentDb
 
    Set td = DBS.TableDefs!jtblStaffRoles
    Set rst = td.OpenRecordset


    For Each i In Me.lstPossPositions.ItemsSelected
        rst.AddNew
        rst!fldStaffPositionID = Me.lstPossPositions.ItemData(i)
        rst!fldStaffID = Me.fldStaffID
                
        Debug.Print Me.lstPossPositions.ItemData(i)
        On Error Resume Next
        rst.Update
    
    Next i
    rst.Close
    Set rst = Nothing
    Set td = Nothing
    Me.Refresh
    
    
    Exit Sub

End Sub

 

[MajP]

Public Function HasDefault(fldStaffID) As Boolean
  HasDefault = DCount("*", "jtblStaffRoles", "fldStaffID = " & fldStaffID & " AND fldDefaultPos = -1") > 0
End Function

in both the add and remove events at the end

    Me.Refresh
    If Not HasDefault(fldStaffID) And Me.lstSelectedPositions.ListCount > 0 Then SetDefault fldStaffID, Me.lstSelectedPositions.ItemData(0)
    Me.Refresh

You need the me .refresh as the beginning (or a list requery) to ensure you get the correct list count, you need it at the end to show the default value.
You have to check 2 things. If it has a default and if there are positions selected.

 

[MajP]

Here is a simpler check. You only need to set the default if it has assigned positions and none of those positions are default. If no positions you do not have to do anything. So you can simplify the code

Public Function NeedsDefault(fldStaffID) As Boolean
  'First check if it has and roles
  If DCount("*", "jtblStaffRoles", "fldStaffID = " & fldStaffID) > 0 Then
    'Then check if any roles are default
    NeedsDefault = DCount("*", "jtblStaffRoles", "fldStaffID = " & fldStaffID & " AND fldDefaultPos = -1") < 1
  End If
End Function

Then in the procedures

    If NeedsDefault(fldStaffID) Then SetDefault fldStaffID, Me.lstSelectedPositions.ItemData(0)
    Me.Refresh

FYI,
IF you add multiple records the first record gets the default. If you remove a record that is the default the first record becomes the default. You may want a message in those cases to alert the user.

 

[Kayleigh]

Thanks ever so much!
Clear and easy to implement

 

[The_Doc_Man]

Thanks for stepping in, @MajP - I was getting lost trying to follow the descriptions.