Problems INSERTING data into SQL table

You should be executing the command, not opening a recordset

Set db = CurrentProject.Connection

SQLString1 = "INSERT INTO Audit Defects (ModelCode, Katashiki, VinNumber, Colour, Date, ID,Week, Month,Year, TMUKAudit, TMEAudit, CS100, TMEReturn, CarAuditNumber, Body, Seq, AuditTypecode, Partname, Defect, Defectcode, VISNumber, VISPage, VISSection, RankCode, RankTypeCode, Picture1, Picture2, Standard, Actual, Volume7, Volume8, RespCode, Olook, VIS, VISRespCode, SISProcess, Repeat, Expectation)"
SQLString2 = "values ('" + ModelCode + ", '" + Katashiki + ", '" + VinNumber + ", '" + Colour + ", '" + CStr(Date) + ", '" + ID + ", '" + Week + ", '" + Month + ", '" + Year + ", '" + TMUKAudit + ", '" + TMEAudit + ", '" + CS100 + ", '" + TMEReturn + ", '" + CarAuditNumber + ", '" + Body + ",'" + Seq + ",'" + AuditTypeCode + ",'" + PartName + ",'" + Defect + ",'" + DefectCode + ",'" + VISNumber + ",'" + VISPage + ",'" + VISSection + ",'" + RankCode + ",'" + RankTypeCode + ",'" + Picture1 + ",'" + Picture2 + ",'" + Standard + ",'" + Actual + ",'" + Volume7 + ",'" + Volume8 + ",'" + RespCode + ",'" + Olook + ",'" + VIS + ",'" + VisRespCode + ",'" + SISProcess + ",'" + Repeat + ",'" + Expectation + ""

SQLstring = SQLString1 + SQLString2

db.Execute SQLstring, dbFailonerror

Also - define your variables as boolean if they are set as bit fields in the database

Dim TMUKAudit As Boolean
Dim TMEAudit As Boolean
Dim CS100 As Boolean
Dim TMEReturn As Boolean

Hi Ritesh
just double check all the fields you are using exist on the form
is this a unbounded form ? you may want to try a me.refresh or me.requery after the insert has taken place

try using & instead of + in your string concatenation

check the SQL string produced i.e. debug.print SQLstring and see if you can run that sql in another client, just validates that sql, that is all

hi, try starting the second line with a space

SQLString2 = " values

you need to surround each string with quotes...

e.g.  'FORD'

so  "values ('" + ModelCode + ", '"

should be   "values ('" + ModelCode + "', '"

what error message do you actually get?

cquinn,

I copied the code in you posted and change all my check boxes to boolean. I still get the same error.

The error appears on the following line:
TMEAudit = Me.TMEAudit.Value

This is the first box that is not checked i.e the TMUKAudit = Me.TMUKAudit.Value is checked

Everyone else,

I'm just trying your solutions.

Regards

Ritesh

Lowfatspread,

When I put quotes on I get:

compile error
Expected:expression

Regards

ritesh

Limbeck,

the space didn't do anything.

Rockiroads,

The form is link to a table and all the fileds are from this table.

The me.refresh/requery doesn't do anything either but i think thats because the code doesn't get that far down due to the error, and the same with using & instead of +.

Regards

Ritesh

your tablename has a space, does your code work with that name in square brackets?
Also see my comments within code


Try adding to the table using the recordset add instead of running sql
eg


Private Sub Command54_Click()

    'DoCmd.GoToRecord , , acNewRec
    'On Error GoTo Err_No_Defect_Click

    Dim ModelCode As String
    Dim Katashiki As String
    Dim VinNumber As String
    Dim Colour As String
    Dim Datestring As Date
    Dim ID As Integer
    Dim Week As Integer
    Dim Month As String
    Dim Year As Integer
    Dim TMUKAudit As String
    Dim TMEAudit As String
    Dim CS100 As String
    Dim TMEReturn As String
    Dim CarAuditNumber As String
    Dim Body As String
    Dim Seq As String
    Dim AuditypeCode As Integer
    Dim PartName As String
    Dim Defect As String
    Dim DefectCode As Integer
    Dim VISNumber As String
    Dim VISPage As String
    Dim VISSection As String
    Dim RankCode As Integer
    Dim RankTypeCode As Integer
    Dim Picture1 As Image
    Dim Picture2 As Image
    Dim Standard As String
    Dim Actual As String
    Dim Volume7 As String
    Dim Volume8 As String
    Dim RespCode As Integer
    Dim Olook As String
    Dim VIS As String
    Dim VisRespCode As Integer
    Dim SISProcess As String
    Dim Repeat As String
    Dim Expectation As String
    Dim error As Boolean
    Dim errorMessage As String
    Dim SQLstring As String
    Dim SQLString1 As String
    Dim SQLString2 As String
    'Dim SQLString3 As String
   
    Dim db As New ADODB.Connection
    Dim recordsetgroup As New ADODB.Recordset
    Dim recodsettype As New ADODB.Recordset
    Dim recoedsetinsert As New ADODB.Recordset
   
    MsgBox "Setting Variables"
    ModelCode = Me.ModelCode.Value
    Katashiki = Me.Katashiki.Value
    VinNumber = Me.VinNumber.Value
    coulour = Me.Colour.Value
    datesting = Me.Date.Value
    ID = Me.ID.Value
    Week = Me.Week.Value
    Month = Me.Month.Value
    Year = Me.Year.Value
    TMUKAudit = Me.TMUKAudit.Value
    TMEAudit = Me.TMEAudit.Value
    CS100 = Me.CS100.Value
    TMEReturn = Me.TMEReturn.Value
    CarAuditNumer = Me.CarAuditNumber.Value
    Body = Me.Body.Value
    Seq = Me.Seq.Value
    AuditypeCode = Me.AuditTypeCode.Value
    PartName = Me.PartName.Value
    Defect = Me.Defect.Value
    DefectCode = Me.DefectCode.Value
    VISNumber = Me.VISNumber.Value
    VISPage = Me.VISPage.Value
    VISSection = Me.VISSection.Value
    RankCode = Me.RankCode.Value
    RankTypeCode = Me.RankTypeCode.Value
    Picture1 = Me.Picture1.Value
    Picture2 = Me.Picture2.Value
    Standard = Me.Standard.Value
    Actual = Me.Actual.Value
    Volume7 = Me.Volume7.Value
    Volume8 = Me.Volume8.Value
    RespCode = Me.RespCode.Value
    Olook = Me.Olook.Value
    VIS = Me.VIS.Value
    VisRespCode = Me.VisRespCode.Value
    SISProcess = Me.SISProcess.Value
    Repeat = Me.Repeat.Value
    Expectation = Me.Expectation.Value
   
    error = False
   
    MsgBox "Validating"
   
'NOTE - DATE IS A RESERVED WORD, TRY CHANGING THIS TO ANOTHER NAME
    If IsNull(Me.Date) = True Then
        error = True
        errorMessage = "Please enter a date"

'NOTE - MONTH IS A RESERVED WORD, TRY CHANGING THIS TO ANOTHER NAME
    ElseIf IsNull(Me.Month) Then
        error = True
        errorMessage = "Please enter a month"
    End If
   
    If error = True Then
        MsgBox errorMessage
    Else
   
        MsgBox "Saving"
        Set db = CurrentProject.Connection
       
        'SQLString1 = "INSERT INTO Audit Defects (ModelCode, Katashiki, VinNumber, Colour, Date, ID,Week, Month,Year, TMUKAudit, TMEAudit, CS100, TMEReturn, CarAuditNumber, Body, Seq, AuditTypecode, Partname, Defect, Defectcode, VISNumber, VISPage, VISSection, RankCode, RankTypeCode, Picture1, Picture2, Standard, Actual, Volume7, Volume8, RespCode, Olook, VIS, VISRespCode, SISProcess, Repeat, Expectation)"
        SQLString2 = "values ('" + ModelCode + ", '" + Katashiki + ", '" + VinNumber + ", '" + Colour + ", '" + CStr(Date) + ", '" + ID + ", '" + Week + ", '" + Month + ", '" + Year + ", '" + TMUKAudit + ", '" + TMEAudit + ", '" + CS100 + ", '" + TMEReturn + ", '" + CarAuditNumber + ", '" + Body + ",'" + Seq + ",'" + AuditTypeCode + ",'" + PartName + ",'" + Defect + ",'" + DefectCode + ",'" + VISNumber + ",'" + VISPage + ",'" + VISSection + ",'" + RankCode + ",'" + RankTypeCode + ",'" + Picture1 + ",'" + Picture2 + ",'" + Standard + ",'" + Actual + ",'" + Volume7 + ",'" + Volume8 + ",'" + RespCode + ",'" + Olook + ",'" + VIS + ",'" + VisRespCode + ",'" + SISProcess + ",'" + Repeat + ",'" + Expectation + ""
       
        'SQLstring = SQLString1 + SQLString2
       
        recordsetgroup.Open "SELECT * FROM [Audit Defects]", db, adOpenKeyset, adLockOptimistic
         

'HERE YOU SET FIELDS LIKE BELOW  
        rs!ModelCode = ModelCode
        rs!Katashiki = Katashiki
        'etc - set all fields in recordset to your local field
       
        'end with this
        rs.Update
       
        Me.ModelCode.SetFocus
        Me.Box50.Visible = True
        Me.Command51.Visible = False
        Me.Label53.Visible = False
        Me.No_Defect.Visible = False
        Me.Label52.Visible = False
        Me.Command54.Visible = False
        Me.Label55.Visible = False
   
    End If

End Sub

Which database system are you trying to update?
 (MS SQL Server, ORACLE, ACCESS, DB2 ...?)

What error messages do you get  ?

before attempt to execute the sql statemet print it out (for debug)

and post it to us so we can understand what is being generated...

Rockiroads,

The error still highlights this bit of code

.TMEAudit. from the line TMEAudit = Me.TMEAudit.Value

I don't think the square brackets did anything.

LowFatSpread,

I'm tring to update a table I can get to via SQL enterprise manager, I think the system is Oracle.

The error message is:

"compile error: Method or data member not found"
And the line in the code that becomes highlighted is  TMEAudit = Me.TMEAudit.Value

Regards

Ritesh

Limbeck/rockiroads,

Apologies, you were right, I did spell TMEAudit incorrectly on the form......It had a space in!!

I've now changed that and my latest error is:

"Setting Variables, Type Mismatch" on the line

 ID = Me.ID.Value

Rockiroads I'm using the code you posted above now.


Regards

Ritesh

i am assuming the value of me.id is a number, then is that number is outside the range 32.768 & 32.767  you need to use Long instead of Integer

Dim ID As Long

LimBeck,

I changed  Dim ID As Integer to  Dim ID As Long but still get the same error.

The part of code that becomes highlighted is the ID = Me.ID.Value

what is the value of me.id.value?

In SQL the field type is UniqueIdentifier but when its linke in Access its classed as a number.

An example of a record is {86713514-5121-4113-9743-731117581781}

731117581781 being the value of id?

if so..

Dim id As Double

Now i get a slightly different error:

setting Variable, Run time error 13, type 13.

the same line in the code is highlighted:

ID = Me.ID.Value

can you give me the type of number in msaccess of the fieldname id and the exact value?

is the value:
{86713514-5121-4113-9743-731117581781}

or is the value:
731117581781

hm you dont even need to sue the var's; you can use the fieldnames in your sql.

 ", " + ID + ", "  could be just as easily
 ", " & me.id.value & ","

In MSAccess the data type is just number but the field size is a Replication ID.

I'm not sure what makes up the contents of the record. The field size in SQL is set to 16 but the number is so much longer.

hm ok, i dont think this wil solve all your problems, but using

Dim id as String    

might work for this one.

in your code, remember to close the recordset and clear them i.e set them to nothing

Rockiroads,

How do i clear the recordset at the end.

Limbeck/Rockiroads,

Using string solved the problem for the ID but now theres another problem. I'm going to use the thinking you provided above to have a go myself so I will leave this questyion open for now but will be back to it ASAP.

Regards

Ritesh

mistake on my part - I am using "rs" to reference fields, it should be "recordsetgroup"  because thats the variable you used to open the recordset

this to close

recordsetgroup.close
set recordsetgroup = nothing