We help IT Professionals succeed at work.
Get Started

Adding New Records In Table via VBA Shows 12/30/1899 as a Date Value

Daisy80
Daisy80 asked
on
685 Views
Last Modified: 2013-11-28
Hi all-

I'm having such a hard time w/ this db seeing as though I'm trying to modify something I didn't create.  I've done tons of google searches to no avail so I'm praying someone here can help before I loose all my hair over here!!  I have the first code below in a separate module.

And then I have the 2nd code below that either inserts a new record into a table or updates the existing record using the data entered into a form by the user.  The Worker ID uniquely identifies each worker & determines whether the record already exists or not.

The "Updated?" field is supposed to update the table with the date it was updated using the Date() function.  I should note that the format for this field in the table it's based on is correctly shown as Short Date.  The first part of the code works perfectly by updating existing records w/ today's date under the Updated? column.  The problem alies in the second part.  For some reason, it's entering 12/30/1899 as the value for any new records & I can't see where I'm going wrong.  Can somebody please help me fix this so that the [  Date & ")"  ] part of the code inserts today's date & not 12/30/1899?  I have a feeling it's some sort of formatting issue & the date's not converting properly, but I can't understand why the first part works fine & the second doesn't.  Thanks as always for your help!


Sub RunSQL(strSQL As String)
    'turn off the warnings
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL strSQL
    'DoCmd.Requery
    
    'turn warnings on
    DoCmd.SetWarnings True
End Sub

Open in new window

Private Sub Save_Button_Click()

Dim lngWorkerID As Long
Dim strSQL As String

    If lngWorkerID <> 0 Then
        RunSQL "UPDATE tblCallTree SET " & _
        "FirstName = """ & Me.FirstName & """," & _
        "LastName = """ & Me.LastName & """," & _
        "HomePhone = """ & Me.HomePhone & """," & _
        "CellPhone = """ & Me.CellPhone & """," & _
        "Pager = """ & Me.Pager & """," & _
        "Extension = """ & Me.Extension & """," & _
        "Email = """ & Me.Email & """," & _
        "Fax = """ & Me.Fax & """," & _
        "ManagerID = " & Me.Manager & "," & _
        "IsManager = " & Me.IsManager & "," & _
        "[Updated?] = Date() " & _
        "WHERE WorkerID = " & lngWorkerID
    Else
        RunSQL "INSERT INTO tblCallTree (FirstName, LastName, HomePhone, CellPhone, Pager, Extension, Email, Fax, ManagerID, IsManager, CampaignID, [Updated?]) VALUES ( " & _
        """" & Me.FirstName & """," & _
        """" & Me.LastName & """," & _
        """" & Me.HomePhone & """," & _
        """" & Me.CellPhone & """," & _
        """" & Me.Pager & """," & _
        """" & Me.Extension & """," & _
        """" & Me.Email & """," & _
        """" & Me.Fax & """," & _
        Me.Manager & "," & _
        Me.IsManager & "," & _
        Me.CampaignID & "," & _
        Date & ")"

    End If
    
DoCmd.Close acForm, "frmEditContact_LiveCallTree", acSaveYes
[Forms]![frmLiveCallTree]![Contacts_List].Requery

End Sub

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE