Need Help - Insert Into Statement not working but no error codes

[FatMan]

Hi all:
I have the following code in my program that is supposed to insert a record
in my table (tblContent) and replace the fields with the values stored in
varValues(). When I run the code my debug.print statements will show the
expected data yet the Insert Into Statement fails to add the record in table
and offers me no error message/code. I know the program gets by the
statement as I have added a temp msgbox to prompt me to check to see if
anything has happened. The odd thing is I have used the same statement with
a different table and fields and it works. All I did was copy, paste and
change the work code to reflect the change in the table and field names. All
the data being added in this case is "text", except the last two that are
long integers.

Can anyone offer any suggestions as to what might be happening?

Any and all help is greatly appreciated.

Thanks,
FatMan


'=====================
'Start of Code
'=====================

Debug.Print "Content Data"
Debug.Print varValues(0) & " = Variety"
Debug.Print varValues(1) & " = Size"
Debug.Print varValues(2) & " = Quality"
Debug.Print varValues(3) & " = Color"
Debug.Print varValues(4) & " = Blush"
Debug.Print varValues(5) & " = Length"
Debug.Print varValues(6) & " = Remark"
Debug.Print varValues(7) & " = Bar Code"
Debug.Print "intFillID: " & intFillID
Debug.Print "intfileid: " & intFileID



CurrentDb.Execute "INSERT INTO tblContent " & _
"(ConVariety, ConSize, ConQuality, ConColor, ConBlush,
ConLength,ConRemark, ConBarCode, ConFillingID, ConFileNameID) " & _
"VALUES (""" & Trim(varValues(0)) & """, """ & Trim(varValues(1)) & """,
""" & _
Trim(varValues(2)) & """, """ & Trim(varValues(3)) &
""", """ & varValues(4) & """, """ & _
Trim(varValues(5)) & """, """ & Trim(varValues(6)) &
""", """ & varValues(7) & """, " & _
intFillID & ", " & intFileID & " )"

MsgBox "Check it out."

 

[John Spencer]

First, I would build the SQL string by assigning it to a variable.
Second, add some error handling to detect if there is a problem.

StrSQL = "INSERT INTO tblContent " & _
"(ConVariety, ConSize, ConQuality, ConColor, ConBlush,
ConLength,ConRemark, ConBarCode, ConFillingID, ConFileNameID) " & _
"VALUES (""" & Trim(varValues(0)) & """, """ & Trim(varValues(1)) & """,
""" & _
Trim(varValues(2)) & """, """ & Trim(varValues(3)) &
""", """ & varValues(4) & """, """ & _
Trim(varValues(5)) & """, """ & Trim(varValues(6)) &
""", """ & varValues(7) & """, " & _
intFillID & ", " & intFileID & " )"

Debug.Print StrSQL 'You can check if the SQL string is valid.

With CurrentDb()
.Execute StrSQL, dbFailOnError
Msgbox .RecordsAffected & " records added"
End With



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[FatMan]

John:
Thank you soooo much for your help. I did exactly as you said and it worked
but here is the kicker. I don't think the error was in my code as I copied
in your code (and commented mine out) tested it and found zero records
affected. Then I thought/noticed that one of the fields (conLength) being
updated by varValues(5) was a null value. Once I edited the text file and
put a value in for this field/variable the code yours as well as my original
worked fine.

Would a Null value in a text file being imported using this method actually
prevent the record from being added but yet report no errors? Some of the
segments in my text file are optional. Do you have any suggestions as to the
best way to handle the "optional" values? My initial thought would be to
check to see if the varValues() is null and if so replace the null value with
a space for text values and a 0 for numeric values.

Any thoughts?

Thanks,
FatMan


P.S. Oh, in the way of background info: What I am doing is taking a text
file that has been created by a computer program used to sort/grade fruit.
The ext file is one large text file that stores the results for all the fruit
that has been sorted/graded for the day. My program reads the text file
line-by-line and based on the line of text adds it to one of three tables.
Some of the values written to the results text file are optional data.

 

[John Spencer]

use an expression like the following

....
"VALUES (" & _
IIF(Len(Trim(VarValues(0) & ""))=0,"Null",Chr(34) & Trim(VarValues(0)) &
Chr(34)) & _
", " IIF(Len(Trim(VarValues(1) & ""))=0,"Null",Chr(34) & Trim(VarValues(1)) &
Chr(34)) & _
....

Or if VarValues(n) can contain nulls then

IIF(IsNull(VarValue(0),"Null",Chr(34) & Trim(VarValues(0)) & Chr(34))

For numbers
IIF(IsNull(VarValue(1),"Null",VarValues(1))

Then check the SQL to see if looks correct.

As far as not getting an error, do you have any error code in the routine?
Do you have Break on unhandled errors turned on as an option?
Did you use On Error Resume Next somewhere in your code?


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[FatMan]

John:
Thank you for all your help.

The error code that I have in my code is something like this...

Private Sub ImportData_Click()
On Error GoTo Err_ImportData

My code to process the text file and store the data in tables

Exit_Err_ImportData:
Exit Sub

Err_ImportData:
MsgBox Err.Description
Resume Exit_Err_ImportData

End Sub


Where do I find the place to turn the option on/off for Break on unhandled
errors? What does this do exactly?
What more do you think I should have in the way for error code/error trapping?

Thanks,
FatMan

 

[John Spencer]

Open up a VBA module
Select Tools: Options from the menu (Pre Access 2007)
Select the General Tab
Select one of the three Error Trapping options

The only thing I might add to your error trapping would be the err.number and
a title for the message box so I would know where the error was reported from.

MsgBox err.number & vbcrlf & Err.Description,,"ImportData_Click"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[FatMan]

John:
Thanks for all your help. I checked the Tools: Options and it was already
set for "Break on Unhandled Errors". I assume this is good enough. I have
also added the error number and a title to may error message as you have
suggested. Thanks I am sure that will help.

Can I please ask for your help once again on this damn insert into
statement? I cannot find my mistake and thought it was working but now have
determined that one of the values is not getting added to the table.

Here is the code......

Debug.Print varValues(0) & " - Variety"
Debug.Print varValues(1) & " - Size"
Debug.Print varValues(2) & " - Quality"
Debug.Print varValues(3) & " - Color"
Debug.Print varValues(4) & " - Blush"
Debug.Print varValues(5) & " - Length"
Debug.Print varValues(6) & " - Remark"
Debug.Print varValues(7) & " - Barcode"

' Append content data to tblContent and get ConID
strSql = "INSERT INTO tblContent " & _
"(ConVariety, ConSize, ConQuality, ConColor,
ConBlush,ConLength,ConRemark, ConBarCode, ConFillingID, ConFileNameID) " & _
"VALUES (""" & Trim(varValues(0)) & """, """ &
Trim(varValues(1)) & """,""" & _
Trim(varValues(2)) & """, """ &
Trim(varValues(3)) & """, """ & varValues(4) & """, """ & _
Trim(varValues(5)) & """, """ &
Trim(varValues(6)) & """, """ & varValues(7) & """, " & _
intFillID & ", " & intFileID & " )"
Debug.Print strSql

With CurrentDb()
.Execute strSql, dbFailOnError
MsgBox .RecordsAffected & " records added"
End With

Here is the results of the debug.print statements......

COX - Variety
65-70 - Size
Class I - Quality
Red - Color
40-100% - Blush
null - Length
WCOXCAPBGP65E - Remark
15 - Barcode
INSERT INTO tblContent (ConVariety, ConSize, ConQuality, ConColor,
ConBlush,ConLength,ConRemark, ConBarCode, ConFillingID, ConFileNameID) VALUES
("COX", "65-70","Class I", "Red", "40-100%", "null", "WCOXCAPBGP65E", "15",
97, 48 )

What I don't understand is why the varValues(0) (which is the variety and
equals COX) is not being added to my table (tblContent) in the field
ConVariety. The debug.print strSql looks right to me but is there a problem
with it? All other fields are being populated with the correct data. This
is being done in Access 2000.

The only thing that I have added prior to this statement recently is a few
If Then statements to handle null values and it appears that they are working
as "null" has been inserted where a null value was in the text file.
Commenting out the if statements that handle the null values has no affect on
the insert into statement.

Can you please help me out.

Thanks,
FatMan

 

[John Spencer]

I can see no reason for the SQL statement to fail to populate the ConVariety
field.

(Grasping at straws question follows)Is it possible that you have a field with
a similar name and you are populating one field and looking at the other?

INSERT INTO tblContent (ConVariety, ConSize, ConQuality, ConColor,
ConBlush,ConLength,ConRemark, ConBarCode, ConFillingID, ConFileNameID) VALUES
("COX", "65-70","Class I", "Red", "40-100%", "null", "WCOXCAPBGP65E", "15",
97, 48 )

I would try copying the statement as generated and pasting it into a blank
query. Then try running the query and see what results you get.

You do realize that you are inserting the text string "null" into your field
Length. You are not setting the field to a null value, but to the word "null"

You have never posted the full code, but I might look at populating the
VarValues array with the delimiters and commas. Instead of doing it while
building the SQL string.

If Len(Trim(me.something & ""))= 0 then
VarValues(0) = "Null, "
Else
VarValues(0) = Chr(34) & Trim(Me.Something) & Chr(34) & ","
End If

Then your strSQL would be easier to build
strSQL = "INSERT INTO tblContent Values(" & _
" list of fields )" & _
"Values(" & VarValues(0) & VarValues(1) & ... & VarValues(7) & _
intFillID & "," & intFileID & ")"


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County