Getting date from form to table (VBA)

Posted on 2014-07-16
Last Modified: 2014-07-18
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
          Debug.Print i, ctl.Column(4, i)
            rst("ActID") = ctl.Column(4, i)


Next i

Set rst = Nothing

End Sub

Open in new window

Here is the piece of code I want to add:

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

Open in new window

Can someone tell me where to put this?
Question by:Megin
    LVL 84

    Accepted Solution

    Add it just below line 22:

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

       'etc etc
    LVL 30

    Expert Comment

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

    Author Comment

    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!
    LVL 31

    Expert Comment

    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
       dteStart = CDate(Me![txtStartDate].Value)
    End If

    Open in new window

    Then use dteStart to set the value in your recordset.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now