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?
MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Add it just below line 22:

For Each i In ctl.ItemsSelected
  rst.AddNew
  Debug.Print i, ctl.Column(4, i)
  rst("ActID") = ctl.Column(4, i)
  rst("ActDate") = Nz(Me!ActDate.Value, Date)  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

   rst.Update
   'etc etc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
Try
...
     rst("ActID") = ctl.Column(4, i)
     rst("ActDate") = Nz(Me!ActDate.Value, Date)
     rst.Update
...
0
MeginAuthor Commented:
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!
0
Helen FeddemaCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.