[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

Problems INSERTING data into SQL table

Dear experts,

I have moved an access table into SQL and am trying to update it from a form using the code below. I seem to be getting a problem when trying to update the checkboxes which are not ticked.

I get a "compile error: Method or data member not found

The checkbox fields are TMUKAudit, TMEAudit, CS100 and TMEReturn.

Here's the code:

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

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

Dim error As Boolean
error = False
Dim errorMessage As String

If Me.Date.Value = "" Then
error = True
errorMessage = "Please enter a date"
ElseIf Me.Month.Value = "" Then
error = True
errorMessage = "Please enter a month"
End If

If error = True Then
MsgBox errorMessage
Else

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

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 SQLstring, db, adOpenKeyset, adLockOptimistic
   
   
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

Tnbkas for your help.

Regards

Ritesh
0
Ritesh_Mistry
Asked:
Ritesh_Mistry
  • 10
  • 8
  • 5
  • +2
2 Solutions
 
cquinnCommented:
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
0
 
cquinnCommented:
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
0
 
rockiroadsCommented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LimbeckCommented:
hi, try starting the second line with a space

SQLString2 = " values
0
 
LowfatspreadCommented:
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?
0
 
Ritesh_MistryAuthor Commented:
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
0
 
Ritesh_MistryAuthor Commented:
Lowfatspread,

When I put quotes on I get:

compile error
Expected:expression

Regards

ritesh
0
 
Ritesh_MistryAuthor Commented:
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
0
 
rockiroadsCommented:
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

0
 
LowfatspreadCommented:
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...

0
 
Ritesh_MistryAuthor Commented:
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


0
 
LimbeckCommented:
hm you did check to see if you didnt misspell TMEAudit? Check if the name is correct on your form?
0
 
Ritesh_MistryAuthor Commented:
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
0
 
LimbeckCommented:
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
0
 
Ritesh_MistryAuthor Commented:
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
0
 
LimbeckCommented:
what is the value of me.id.value?
0
 
Ritesh_MistryAuthor Commented:
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}
0
 
LimbeckCommented:
731117581781 being the value of id?
0
 
LimbeckCommented:
if so..

Dim id As Double
0
 
Ritesh_MistryAuthor Commented:
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
0
 
LimbeckCommented:
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 & ","

0
 
Ritesh_MistryAuthor Commented:
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.
0
 
LimbeckCommented:
hm ok, i dont think this wil solve all your problems, but using

Dim id as String    

might work for this one.
0
 
rockiroadsCommented:
Hi Ritesh, was away hence missed these last lot of posts

Try defining your variable ID as a String, failing that, last resort is variant

Looking at your data, it has hypens therefore defining as a number will not work


0
 
rockiroadsCommented:
in your code, remember to close the recordset and clear them i.e set them to nothing
0
 
Ritesh_MistryAuthor Commented:
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
0
 
rockiroadsCommented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 8
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now