• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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?
0
Megin
Asked:
Megin
1 Solution
 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now