asked on

Getting date from form to table (VBA)

I have code that works to insert items chosen in a list box added to a table.

Now I want to add to that code instructions to also pull the date from a text box on the form and insert that into the table.

I have the code to do it, but I am not sure where to add it into the existing code.

I have added it before the instructions for the list box, but then it only adds the item from the list box to the table. If I add it after the instructions for the list box, it only adds the date.

I don't know why, but only the second instruction ends up working.

Here is the code for moving the item from the list box:

Private Sub btnAddSelected_Click()

Dim frm As Form
Dim ctl As Control


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Variant
Set frm = Forms("frm_Act_Enter")
Set ctl = frm![lstPrevRpts]

Set db = CurrentDb
Set rst = db.OpenRecordset("Act_SubTo_Date")



    For Each i In ctl.ItemsSelected
        rst.AddNew
          Debug.Print i, ctl.Column(4, i)
            rst("ActID") = ctl.Column(4, i)


        rst.Update

Next i



rst.Close
Set rst = Nothing



End Sub

Open in new window

Here is the piece of code I want to add:

 rst.AddNew
            rst!ActDate.Value = Nz(Me!ActDate.Value, Date)
        rst.Update

Open in new window

Can someone tell me where to put this?

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

Try
...
rst("ActID") = ctl.Column(4, i)
rst("ActDate") = Nz(Me!ActDate.Value, Date)
rst.Update
...

Megin

ASKER

I didn't think I could add it in the command with the list box!

Thank you!

I have a huge smile on my face now because that totally worked!

Helen Feddema

You might need to check for a valid date in the control, like this:

If IsDate(Me![txtStartDate].Value) = False Then
   strTitle = "Invalid date"
   strPrompt = "Please enter a valid start date"
   GoTo ErrorHandlerExit
Else
   dteStart = CDate(Me![txtStartDate].Value)
End If

Open in new window

Then use dteStart to set the value in your recordset.