Problem with UpdateBatch statement - empty date fields no longer empty

[wouterv81]

Hi,



I've created a form,
and some code when the form is opened, data is pulled from a table into a recordset, and the form fields are populated from the recordset.
I've got a little "next" and "previous" button which, upon being clicked, first update the recordset and the table to reflect any changes made on the form, and then move to the next or previous record.

I'm facing a problem with date/time fields in my table. If the date/time field in the table is blank, and I press the "next" button, it populates the field with a date from the previous record, and pushes it into my table.

I tried to solve it in my UpdateRecords sub by pushing a blank field into the recordset if the form field is blank (as seen in the code below). It did not help.

Here's some (shortened) code samples. I didn't show a sample of the FillData sub because I don't think the problem is there. All data is showing up correctly on my form.

Any ideas?

Thanks,
Walter

Private Sub btnNext_Click()
    UpdateRecordsrl
    rrs.MoveNext
    If rrs.EOF Then
        rrs.MoveLast
    End If
    FillDatarl
End Sub

Private Sub UpdateRecordsrl()
    
   txtrlnumber.Locked = False
    txtrlnumber.SetFocus
    rrs.Fields.Item("rlnumber") = txtrlnumber.Text
    
    dterlstartdate.SetFocus
    If dterlstartdate = "" Then
        rrs.Fields.Item("rlstartdate") = ""
    Else
        rrs.Fields.Item("rlstartdate") = dterlstartdate
    End If

    rrs.UpdateBatch
    txtrlnumber.Locked = True

End Sub

[June7]

Why are you using recordset? Is the form bound to table/query? Are controls bound to fields of form RecordSource? If all yes then data entry/edit is direct to the table fields. Record entry/edit committed to table when move to another record, close form, or run code DoCmd.RunCommand acCmdSaveRecord.

You don't show any code that declares, sets, opens a recordset.

Want to provide project for analysis? Follow instructions at bottom of my post.

[wouterv81]

June, thanks for your reply. I think I've figured it out.
To answer your question, I like using recordsets more than linking a form directly to a table. It gives me more control over the transfer of data and it gives me a greater degree of flexibility.

Just in case there are others interested in this issue and resolution, here's what went wrong:

The FillData procedure which pulls data from the table and pushes into the form, was not doing what it should be doing. The issue was not only with "date/time" fields but also with text fields and others.

Here's a sample of the old code (only showing the FillData procedure with one text field and one data field):

Private Sub FillDatarl()
    txtrlbusinessunit.SetFocus
    If rrs.Fields.Item("rlbusinessunit") <> 0 Then
        txtrlbusinessunit.Text = rrs.Fields.Item("rlbusinessunit")
    End If
            
    dterlstartdate.SetFocus
    If rrs.Fields.Item("rlstartdate") <> 0 Then
        dterlstartdate.Text = rrs.Fields.Item("rlstartdate")
    End If
    
End Sub

It was fixed by changing the code to:

Private Sub FillDatarl()
    txtrlbusinessunit.SetFocus
    If rrs.Fields.Item("rlbusinessunit") <> 0 Then
        txtrlbusinessunit.Text = rrs.Fields.Item("rlbusinessunit")
    Else
        txtrlbusinessunit.Text = ""
    End If
            
    dterlstartdate.SetFocus
    If rrs.Fields.Item("rlstartdate") <> 0 Then
        dterlstartdate.Text = rrs.Fields.Item("rlstartdate")
    Else
        dterlstartdate.Text = ""
    End If
    
End Sub

By not including the "Else" part, I allowed this field to hold onto the value from the previous record and carry it into the next record.
Though you didn't answer my question (which I now realize was not possible to answer based on the information I gave - for which my apologies), you did somehow made me think of it. Thanks for that.