We help IT Professionals succeed at work.

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

Daisy80
Daisy80 asked
on
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

Make sure that the second line of the module containing this code  is
Option Explicit

Now use Debug>Compile.
This will take you to any unrecognisable names in your code.
I'm fairly sure there is at least one.

Author

Commented:
Thanks for the tip peter.  I'm still a newbie & don't know if this matters, but the main code in question is listed in the code for a pop-up form.  This pop-up form is where the user enters the new contact information (if the worker ID doesn't already exist) or it pops up w/ the existing contact information for the worker selected.  When you click the save button, this code runs for that form to update the main table with either the new or existing information entered.  

Anways, I did what you suggested & entered Option Explicit into the 2nd line on top where this code is entered & then hit Debug --> Compile Base.  It then took me to a different module & highlighted a function that works properly.  I couldn't get it to find anything in the code above.  Any ideas?
If Access stores a '0' (zero) in a date field, then it displays as 12/30/1899.

So here's how to go about fixing it.  I noticed that there's a variable strSQL in Save_Button_Click()
 that's not been used.  I'd suggest you use it.  Have the dynamic SQL be passed to the variable, then pass the variable into the RunSQL procedure.

This would allow you to set a breakpoint as I've indicated.  When the code stops there, press CTRL+G to open the immediate window, and type "?strSQL" into there (not including the quotes) and press enter.

Then you can copy the line of SQL that gets printed out the to immediate window.

Now, go back to Access (not the VBA window), and open the query builder.  Dismiss the dialog that asks for what tables/views you want to use.  Then click on the view button (far left on the command bar at the top) and choose "SQL View".  Paste in the SQL that got printed to the immediate window.

As it's INSERT SQL make sure you don't click "Run" (the toolbar button with the exclamation mark.  Instead use the same view button as before and choose "Datasheet view" or "Design view".  If there's something wrong with your code, Access will tell you right away.

There are two other observations:
1. Get rid of the "?" in "Updated?".  "?" is a symbol with specific meanings in SQL.  It's bad practice to use symbols other than "_" (underscore) in object names.
2. You might try then changing the line to read "[Updated] = "#" & Format(Date(),"mmm-dd-yyyy") & "#".  The hash symbols tells Access that the string between them is a date.  Formatting the date explicitly with a medium month means that you can't fall foul of localization issues (In UK, Australia, etc, Jan 11th 2010 would be 11/01/2010.  In the US that would be Nov 1st!)

Good luck.
Private Sub Save_Button_Click()

Dim lngWorkerID As Long
Dim strSQL As String

    If lngWorkerID <> 0 Then
        strSQL = "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
        strSQL = "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
    
    RunSQL strSQL  'SET THE BREAKPOINT HERE (USE F9)

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

End Sub

Open in new window

Author

Commented:
Many thanks Andrew for those suggestions- and you're probably right.  I'm going to try as you pointed out w/ the breakpoint.  I saw that you noticed my other open question- These 2 issues are actually in the same db, but seeing as though they're not directly related, I thought it'd be best to open a separate ticket for this.

Although, to further on the above, I do want to stress that the date gets accurately added in the first part of the code where it says "[Updated?] = Date() " & _   And it's probably also important to note that this is where the existing records get updated- and that's where the Updated column gets properly updated w/ today's date.  The problem arrises in the second part where a new record gets added if it's not already present- that's when the date updates w/ the 12/30/1899.  And that makes sense how somehow a zero got in that date field.  I'm just puzzled how that zero got in there instead of the current date Date() as instructed?

Either way, I'm going to try what you suggested & hopefully we can find the culprit.  Thanks again for helping me- much appreciated!!
The other thing you can do is change the design of tblCallTree and set the default value for [Updated] to "Date()" then just remove [Updated] from the dynamic SQL here.

This assumes that this would be consistent with any business rules around the records stored in tblCallTree of course.

Author

Commented:
OMG Andrew- you are GENIUS!!!  I did just what you suggested & took out the updated piece from the code & set the default value to Date() & it worked like a charm!!  Thank you soooo much for your help!!
Genius eh?  It's a burden really...
;-p

Glad I could help.