Inserting date into a date unbound table with date datatype

[Adict]

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

[twinnyfo]

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 TblMasterBackEnd? 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]

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.

[Adict]

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