I am trying to log my users action into a back-end table if a employee master data is change. Below is my code but i am getting an error with inserting a date. - Dim strSQL As String
-
-
strSQL = "INSERT INTO TblMasterBackEnd " & _
-
"( HRAPSNO, AlphaCode, FirstName, LastName, " & _
-
"Branch, Dept, Loc, CostCtr, Netuser, " & _
-
"DateModified, DateStarted, PayRate, " & _
-
"AnnualSalary, BirthDate, Sex, MaritalStatus, " & _
-
"Dep, Position, DateCreated) " & _
-
"VALUES ('" & Me.txtHRAPSNo & "', '" & _
-
Me.txtAlphaCode & "','" & Me.txtFirstName & "','" & _
-
Me.txtLastName & "','" & Me.txtBranch & "','" & _
-
Me.txtDept & "', '" & Me.txtLoc & "', '" & _
-
Me.txtCostCtr & "', '" & Me.txtNetUser & "', #" & _
-
Me.txtDateModified & "#, #" & Me.txtDateStarted & "#, " & _
-
Me.txtPayRate & ", " & Me.txtAnnualSalary & ", #" & _
-
Me.txtBirthDate & "# ,'" & Me.cboSex & "', '" & _
-
Me.cboMaritalStatus & "'," & Me.txtDep & ",'" & _
-
Me.txtPosition & "', #" & Me.txtDatecreated & "# );"
-
-
DoCmd.RunSQL (strSQL)
-
DoCmd.Save
-
Me.Requery
3 1644 twinnyfo 3,653
Recognized Expert Moderator Specialist
Adict,
First, please use the code tags when posting code, which is a rquirement of this forum. I have edited your post to reflect this. Also, concerning this, it is also wise to add line continuation marks on the forum so that users can view your code more easily, without scrolling all the way to the right.
Second, is this a direct cut and paste from your VBA editor? I have made some minor adjustments to the spacing of your code, but since I am not sure this was a direct cut and paste, I cannot say that this would solve anything. On the surface, I don't see anything that pos out concerning your dates.
Third, when you have trouble with SQL statements, it is often useful to have teh code print it to your Immediate Window so you can see exactly what your SQL statement contains before you execute it. Just add the statement Debug.Print strSQL to the line just prior to running your SQL statement (in this case, line 19).
Fourth, if you are inserting all these fields into a table (and it appears these values are currently on a form, is there a particular reason this form is not bound to TblMasterBackEn d ? There may be a good reason for this, but it would be good to know.
Fifth, an alternate method (and one that I prefer) is to use a Recordset where you are directly updating the table--this is all preference, but for me, it allows me to more directly see what I am updating.
Sixth, you say you are getting an error with inserting a date. How do you know? What error are you getting and how do you know it is related to the date? This information will help us understand the nature ofyour problem.
Seventh, on the surface, it appears that you are creating redundant data in your Database, which most of us try to avoid unless it is absolutelye necessary. Based on the data listed, I don't see any fields that would much change over time, other than pay rate. Why not just add the index for the employee into the table and only the data that needs to be saved? Again, there could be a good reason for you saving so much information redundantly, but it would be good to know the reasons.
Thanks!
NeoPa 32,566
Recognized Expert Moderator MVP
Dates won't work when added that way into a SQL string unless you happen to live and work in North America (at least have your PC settings set that way).
See Literal DateTimes and Their Delimiters (#) for more on that, and How to Debug SQL String for general help when trying to work directly in SQL from VBA.
Thanks twinnyfo and Neopa. I have used recordset and achieved the results. The reason why i want a redundant data is to ensure that every data changes users make on the main master file. This is to achieve audit trails for master data.
Thanks much
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Khurram |
last post by:
I have a problem while inserting time value in the datetime Field.
I want to Insert only time value in this format (08:15:39) into the SQL
Date time Field.
I tried to many ways, I can extract the value in timeonly format by using
this command
Format(now,"HH:mm:ss")
But when I insert it into the Sql Server database, it embadded date value
with it.
the output looks like that "01/01/1900 08:59:00" in that case time is
|
by: abcabcabc |
last post by:
I write an application which can let user define own date format to input,
How to convert the date string to date value with end-user defined date
format?
Example, User Defined Date Format as "dd/MM/yyyy"
input as "01082003" convert to date value as, 01 Aug 2003
Example, User Defined Date Format as "yyyy,dd,MM"
|
by: Richiep |
last post by:
I am trying to get a UK format date of dd/mm/yyyy.
Why does the following subroutine not return a valid date in a web form?
The date returned is #12:00:00 AM# but the date I entered into the text box
was 24/06/2006.
The other solution I have tried is given by the following two lines that do
not compile because a value of date type cannot be converted to datetime:
|
by: gazelle04 |
last post by:
I'm trying to enter the current date into the table through code but there's an error in inserting the date.
Whats wrong with this code:
strSQL = "INSERT INTO University (, , )"
strSQL = strSQL & "VALUES ( " & intUnivId & " , " & strBranchID & ", " & Date() & " );"
I don't know how to make the Date a string in order to run in the SQL statement.
Or should I leave it as it is without quotation marks.
|
by: Paul |
last post by:
I'm sure this isn't a difficult question, but I've been struggling trying to
insert the current date into a sql server field. I've tried numerous
suggestions that I've seen in newsgroups and everything either gives an
error or puts 1/1/1900 in the field. The code I'm trying is:
Dim sqlCMD As New SqlClient.SqlCommand("Insert Into tblNotificationStatus
(UserName, ExpirationDate) values ('" & strUser & "','" &...
| |
by: markjerz |
last post by:
Hi,
I basically have two tables with the same structure. One is an archive
of the other (backup). I want to essentially insert the data in to the
other.
I use:
INSERT INTO table ( column, column .... )
SELECT * FROM table2
|
by: Nathan Sokalski |
last post by:
When determining whether a String can be converted to a DateTime, you can
use the IsDate() method. However, I would also like to know whether the
string is a date, a time, or both a date and a time. Is there any simple way
to do this without using manual pattern matching? Thanks.
--
Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/
|
by: vp.softverm |
last post by:
hi all .
Am facing problem with the date picker. when i click on date picker in
a popup window. the date table is opened in the middle of the pop up
window.
and it is unable to scroll with the pop up
window .
|
by: haridharmajan |
last post by:
I Need to add multiple date into a field using calender each date is to be separated by comma ie when I click on the show month it must display calender and when I click on a date it must inser it to that field also I need to insert many date in to single field separated by comma
help me please
|
by: mmclancy |
last post by:
I'm using Oracle's SQL Developer to do the following:
CREATE TABLE Account (
AccountID INT NOT NULL,
CreditID INT NOT NULL,
CardType VARCHAR(15) NOT NULL,
ExpiryDate DATE,
PRIMARY KEY (AccountID),
CONSTRAINT fk_credit_account FOREIGN KEY (CreditID) REFERENCES CreditCard
)
-- Table created
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |