- Disaster Recovery
- Query Syntax
- Databases
- Microsoft Access
- Microsoft SQL Server
Adding New Records In Table via VBA Shows 12/30/1899 as a Date Value
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!
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
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
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.
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.
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?
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
Good luck.
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
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
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!!
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.
This assumes that this would be consistent with any business rules around the records stored in tblCallTree of course.
-
![]()
received a Solution
Urgent! Cannot insert new records in SQL table. -
![]()
wrote an Article
MS Access VBA error 3146 when using a string longer than 128 characters in a WHERE clause with SQL Server -
![]()
created a Video
![]()
Synchronize List Box in Access using SQL and VBA
-
Explore Cloud Class® ![]()
Programming C# 6: Fundamentals
Premium Content
You need an Expert Office subscription to comment.Start Free Trial