Insert into query not working

[davyre]

Hi, I got query that will insert values into tables. It was working just fine this morning, but as I create new form, modified something small, and I flush all the data and reinsert all the data into the database, the query wont work (the table is still empty, but the autonumber still continues if I insert a new record manually). All the coding seems working (no error message showed up or anything like that). Any idea what hinders the query to work? Thanks

 

[dhookom]

Based on what I can see, your SQL statement is wrong.

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Exactly how are you attempting to run the INSERT query? It may be silently failing because you are using code that suppresses the errors.

 

[davyre]

here's the snippet of my insert query

StrSQL1 = "INSERT INTO tblParts ([PartNumber], [PartDescription], [Material], [PartCost], [VendorPart], [Comments], [VendorID])"
            StrSQL1 = StrSQL1 & " VALUES (" & PartNumber_insert & ", " & PartDescription & ", " & Material & ", " & PartCost & ", " & VendorPart & ", " & Comments & "," & VendID & ");"

I did not know what happened, but this morning I tried it again, and its working. Is it because yesterday I use "compact and repair database button" a lot? Does that function may cause the database to do some errors?
any Thanks!

 

[PHV]

I highly doubt that all the inserted are numeric.
You have to surround all text values with single quotes.

Tip: Debug.Print StrSQL1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

hei PHV, can you give me info about what should I use if the data type is numeric, or string, or date?
These things are always confuses me.
if numeric then should i do it like this?

" & Variable & "

if text/string

"'" & Variable & "'"

if date

#" & Variable & "#

CMIIW Thanks

 

[PHV]

" & Variable & "

'" & Replace(Variable, "'", "''") & "'

#" & Format(Variable, "yyyy-mm-dd") & "#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Hi all, I got this new problem with INSERT INTO query.

allBlank = 0
excelLine = 2

Do While allBlank <= 5
    modelNumber = Worksheet.Cells(excelLine, "B")
    If modelNumber = "" Or IsNull(modelNumber) = True Then
        allBlank = allBlank + 1
        excelLine = excelLine + 1
    Else
        allBlank = 0
        itemNumber = Worksheet.Cells(excelLine, "A")
        'itemNumber = "'" & itemNumber & "'"
        
        modelNumber = Worksheet.Cells(excelLine, "B")
        modelNumber = "'" & modelNumber & "'"
        
        modelDescription = Worksheet.Cells(excelLine, "C")
        modelDescription = "'" & modelDescription & "'"
        
        unitNumber = Worksheet.Cells(excelLine, "D")
        unitNumber = "'" & unitNumber & "'"
        
        UnitDescription = Worksheet.Cells(excelLine, "E")
        UnitDescription = "'" & UnitDescription & "'"
        
        UnitDimensions = Worksheet.Cells(excelLine, "F")
        UnitDimensions = "'" & UnitDimensions & "'"
        
        
        
        insertTblModelQuery = "INSERT INTO TblModel ([ModelNum], [ModelDesc]) VALUES (" & modelNumber & ", " & modelDescription & ");"
     
            DoCmd.SetWarnings False
            DoCmd.RunSQL insertTblModelQuery
            DoCmd.SetWarnings True
        
        excelLine = excelLine + 1
    End If
Loop

The problem is this:
In the excel file that I import from, there are some duplicate with model(Excel column A and B). What I want is Access to insert into TblModel only distinct models (no duplicates). I already set the TblModel.ModelNum as Indexed(No Duplicates). This is correct, but the problem is why the TblModel.ModelID not working correctly (i.e skip the odd numbers as the duplicates were discarded)?

the excel file: one model has 2 units


why on the tblModel.ModelID(first field) skips the odd numbers?


Thanks!