Access 2010 loop through recordset and change subform field if blank

Question

I have a command button on a mainform that when clicked runs a loop through a field on the displayed records of a subform and changes the value of all the data in that field to match the value of a unbound combobox on the mainform. It is so that users can have the option to update several records on the subform at once. The code works fine but would be more usefull if it could be changed to only update the field if it is blank for each record. In other words I want it to check if each record has a blank for that field and then populate it based on the combo box and skip to the next record if it in not blank or null. This is what the code looks like right now. I'm not very good with access VBA and am not sure if I should use "Case" or "If" or how exactly to use it with the code below.

Private Sub comm_1_Click()
Dim rs As DAO.Recordset

Set rs = Me.Skid1.Form.RecordsetClone
With rs
    .MoveFirst
    Do While Not .EOF
        .Edit
        ![Release Code] = Me.code_updater.Value
         .Update
         .MoveNext
     Loop
 End With
 Set rs = Nothing

End Sub

I have tried this but it seemed to only update some of the blank records (very strange), I'm pretty sure its close but not quite.

Private Sub comm_1_Click()
Dim rs As DAO.Recordset
Dim fld As Field

Set rs = Me.Skid1.Form.RecordsetClone
With rs
    .MoveFirst
    Do While Not .EOF
     For Each fld In .Fields

      If IsNull(fld.Value) Then
        .Edit
        ![Release Code] = Me.code_updater.Value
        .Update
         End If
        .MoveNext
          Next

    Loop
End With
Set rs = Nothing

End Sub

Solution

I suspect you want

Private Sub comm_1_Click()
Dim rs As DAO.Recordset
Dim fld As Field

Set rs = Me.Skid1.Form.RecordsetClone
With rs
    .MoveFirst
    Do While Not .EOF
      ''Anything, space filled, null, ZLS
      If Trim(![Release Code] & "") = "" Then
        .Edit
        ![Release Code] = Me.code_updater.Value
        .Update
      End If
      .MoveNext
    Loop
End With
Set rs = Nothing

End Sub

I suggest you edit your table and make sure it will not accept zero-length strings by setting the Allow Zero Length property to No for text data types.

Comments

[Nick.McDermaid]

Really your best bet is to step through the code. Click in the margin against on of the code lines and you'll get a yellow line. Now run your code and it will stop at the yellow line. You can hover over variables and see what their values are. Press F8 to run the next line. In this way you can step through your code and see what it is doing, and you will get some more certainty about what is happening. In other words "seems to only update blank records" is not enough for us to go on - you need to do a little more investigation.

[Scott Whittaker]

Seems to be doing the same thing. Although "" or blank is probably what I am actually looking for not null. I also tried using only the fld.Value = "" and getting rid of the IsNull(fld.Value) Or but the code stopped there.

[Scott Whittaker]

it gives me a runtime error code <no currect record> at If fld.Value = "" then

[Fionnuala]

@ScottWhittaker Do you want to update all blank fields in the recordset or just Release Code?

[Scott Whittaker]

I've never used the Trim Function before. Just looked it up, i'll have to remeober that one. Thanks again.