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

 

[theDBguy]

Hi. Are you dealing with a multi-select Listbox?

 

[Kayleigh]

It could be possible but ideally not.

 

[Cronk]

If the listbox is not multiselect, then the value of the listbox is the same as the first column of the item selected.

   Set rst = currentdb.OpenRecordset("select * from jtblStaffRoles where fldStaffPositionID = " & Me.lstSelectedPositions)
   rst.edit   
     rst!fldDefaultPosition = True
   rst.Update

 

[theDBguy]

It could be possible but ideally not.

If you're not dealing with a multi-select Listbox, then you're code could become a little simpler.

 

[Kayleigh]

Well in this situation a multi-select would not be useful as I would only like one record to be set as default for each staff member. So I guess it would listbox is not multi-select...

 

[theDBguy]

Well in this situation a multi-select would not be useful as I would only like one record to be set as default for each staff member. So I guess it would listbox is not multi-select...

In that case, as @Cronk said, the value of the Listbox is the same as the selected item.

 

[Kayleigh]

So I have adjusted it according to @Cronk 's suggestion however when run a syntax error is flagging the query...
Also how do I include in code that original record marked fldDefault = True is changed to false?

My code:

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)
    
   Set rst = CurrentDb.OpenRecordset("select * from jtblStaffRoles where fldStaffPositionID = " & Me.lstSelectedPositions)
   rst.Edit
     rst!fldDefaultPosition = True
   rst.Update
        
    rst.Close
    Set rst = Nothing
    Set td = Nothing
    Me.Refresh
    
    
    Exit Sub
End Sub

 

[Cronk]

The following lines in your code are superfluous.

   Set DBS = CurrentDb
   Set td = DBS.TableDefs!jtblStaffRoles
   Set rst = td.OpenRecordset(dbOpenDynaset)

On what line does the error occur? (Put a break point at the start of your code and step through it using the F8 key.) Perhaps the field fldStaffPositionID is a string or the first column of your list box is a string.

 

[wvmitchell]

Just like to add a few comments... both versions of this should test for a current record e.g. in the first version, after the FindFirst we should use something like

If Not rst.NoMatch Then
...
End If

and in the second version, after opening the recordset we should use something like

If Not (rst.BOF And rst.EOF) Then
...
End If

I understand that in this particular situation, we might never have a condition where there is no current record, but this is a good coding practice.

 

[Kayleigh]

Thanks for all your help. Works now and I've learnt something on the way!
As usual it was mainly an issue with a misspelled field name

 

[Kayleigh]

My next question is how to implement a check for fldDefaultPos into the cmdUpdate which adds a new record to table. So if a record is present with fldDefaultPos = True, new record does not set a value to field but if no record exists with fldDefaultPos = True it should set new record accordingly.
Here is my code:

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

 

[The_Doc_Man]

it was mainly an issue with a misspelled field name

Yeah, darn don't you hate that....

But on to the next question. I understand WHAT you asked but the answer depends on in which record you find that fldDefaultPos value to be tested. You would get different instructions depending on where that field is found.

 

[Kayleigh]

fldDefaultPos is simply another field in the jtblStaffRoles.

 

[The_Doc_Man]

From what you have shown, there is no correlation to your recordset rst that points to that table because if you are adding a record, ALL fields will start life at their default values - which, if not specified, will be null.

The ONLY thing that could even remotely be a source for this would have to be the contents of the listbox you are scanning. Otherwise, the question seems to make no sense (because of its apparent lack of a correlated data source.) What is in the listbox?

 

[Kayleigh]

Listbox contains fldStaffPositionID.

 

[The_Doc_Man]

If that is ALL it contains, you have no valid source for the fldDefaultPos that can correlate to your selection. Or at least, from the way you asked the question, I can see no such source. It might actually be there, but I don't see it.

 

[Kayleigh]

So how would you suggest that I can do this action?

 

[The_Doc_Man]

At this point, since I see no clear-cut connection between the parts you describe, I cannot understand the action you propose. If I can't understand it then I can't help you.

You seem to be trying skip an action for which there is no clear path between this code and the data you require for the decision to be made. Focus on describing this relationship between your intended action and the required decision data. If you can define the relationship more clearly, I might be able to help you. As it is right now, I don't see it.

For what it is worth, I know your problem (from the outside). You can see intuitively that there is some decision you need to make and you know where that decision needs to be made. But I will submit for your consideration that if you can't tell me how you intended to GET the specific information you need in order to MAKE that decision, you don't understand your own problem well enough to be trying to code it yet. I'm trying to be gentle about this, but until you can fill in the missing pieces, I have no clue as to how to help you. Nobody can, until you help yourself by doing a bit more problem analysis in this part of your app.

Let me summarize what I have seen; it might clarify why I'm having such trouble. You are creating new records based on selections made from a list. You want to be able to skip some action for a new record under certain circumstances based on a table where a particular field is kept. The field in question specifies some kind of default option. But you are creating a new record based on a list that only contains one thing, from the looks of it, and that list doesn't appear to be directly related to the table where you are creating the new record. The new record, because it is new, CANNOT have the particular field filled in. More specifically, if there is a default value of TRUE (or FALSE) for the record, then this field in the new record will be invariant and thus there is no decision to be made. IF there is a relationship through the list selection, I can't see is as being unique, so a lookup does not seem to be correct. Unless there is another relationship between records that you haven't disclosed.

This problem could be either one of description (i.e. there IS a path from the list to the table, record, and field in question, you just haven't explained it well) or one of design omission (i.e. you really don't know how to identify the information you wanted to use.)

 

[Kayleigh]

@The_Doc_Man - I appreciate your time to explain my misunderstanding. It is possible that there is a mistake with my design.
I have reproduced the element of the DB which I am currently working on.
The code which I am struggling with is located on the DEfrmAddRoleStaff pop-out form. (Can be accessed by double-clicking on a name in frmRolesManager) Please see the Add Selected Positions command - currently adds selected position to current staff member, but would like to add a check for fldDefaultPos so if false (ie no roles are currently default) it would assign true to field in this record. (Should also have parallel function for the remove selected positions command)
Hope this is clearer to understand with the sample DB.