Problem with recordset loop (1 Viewer)

[hollering]

I know this is long but does anyone see the syntax error in this SQL query.

INSERT INTO tblCharProduction (SPI, OpNum, Number, Characteristic, SpecCharNum, Spec, GageNumber, Samples, Frequency, Control, Reaction, FAB, MOV, STO, INS, ControlPrevention, ControlDetection, PotentialEffects, Severity, PotentialCauses, Occurrence, RPN, RecommendedActions, Responsibilities, TargetDate, ActionsTaken, ATSev, ATOcc, ATDet, ATRPN, Detection) VALUES ('001100', 0, 2, 'Alloy', 0, 'S430', 'Cert/Tag', '1', 'Lot/Tag', 'Inspect', 'Verify & Reject & Quarantine', False, False, False, False, '', '', '', '', '', '', '', '', '', , '', '', '', '', '', '');

 

[ChrisO]

Just a quick eyeball but what value is going into TargetDate?

 

[Travis]

Use "" instead of '' for MS Access

 

[modest]

How about next time you clean it up if you want an answer. If you don't take us serious and be courteous enough for us to look at it, we're not going to attempt to understand you.

INSERT INTO tblCharProduction 
(
    SPI, 
    OpNum, 
    Number, 
    Characteristic, 
    SpecCharNum, 
    Spec, 
    GageNumber, 
    Samples, 
    Frequency, 
    Control, 
    Reaction, 
    FAB, 
    MOV, 
    STO, 
    INS, 
    ControlPrevention, 
    ControlDetection, 
    PotentialEffects, 
    Severity, 
    PotentialCauses, 
    Occurrence, 
    RPN, 
    RecommendedActions, 
    Responsibilities, 
    TargetDate, 
    ActionsTaken, 
    ATSev, 
    ATOcc, 
    ATDet, 
    ATRPN, 
    Detection
) 
VALUES 
(
    '001100', 
    0, 
    2, 
    'Alloy', 
    0, 
    'S430', 
    'Cert/Tag', 
    '1', 
    'Lot/Tag', 
    'Inspect', 
    'Verify & Reject & Quarantine', 
    False, 
    False, 
    False, 
    False, 
    '', 
    '', 
    '', 
    '', 
    '', 
    '', 
    '', 
    '', 
    '', 
    [B],[/B]     <-- Looks like an extra comma
    '', 
    '', 
    '', 
    '', 
    '', 
    ''
);

3 Problems. Your table is too big... It seems as though you need to take a database class and put your table into the correct normal form. I would hate to have to query that sucker. Next problem, as previously stated you're using single quotes, in Query Builder you should use double quotes. Final problem, it seems as though you have an extra comma.

Do you see how much easier it is to read the code that I posted instead of the way you did? That's why how you write your code is important for debugging.

 

[hollering]

Modest,

I'm a beginner. All the points in your post regarding the form of my code are good points and I appreciate the feedback. I also VERY MUCH appreciate your help in debugging. I know this looks like a mess but to tell you the truth I didn't even think about cleaning it up...next time I will. However, it would be good for all of us not to treat each other condescendingly.

As for the table size. I originally had the table broken up with a couple lookup columns pointing to other tables. Then I learned of Access 97's method for locking pages of data, and decided it may be best to keep the data stored the way it is considering that it will force Jet to lock only one record at a time. Still, I assure you the table, while massive, does meet 3NF.

Also, single quotes are handy when you are creating the SQL statement in a string variable...especially one this large. Just take a look at the statement that creates the thing (PLEASE NOTE: I will clean this up for use I'm just putting it here to show how the single quotes help rather than escaping all those double quotes).

                InsertCharSQL = "INSERT INTO tblCharProduction ([SPI], [OpNum], [Number], [Characteristic], [SpecCharNum], " & _
                                "[Spec], [GageNumber], [Samples], [Frequency], [Control], [Reaction], [FAB], [MOV], [STO], [INS], " & _
                                "[ControlPrevention], [ControlDetection], [PotentialEffects], [Severity], [PotentialCauses], " & _
                                "[Occurrence], [RPN], [RecommendedActions], [Responsibilities], [TargetDate], [ActionsTaken], " & _
                                "[ATSev], [ATOcc], [ATDet], [ATRPN], [Detection]) VALUES ('" & NewSPI & "', " & _
                                rsChar!OpNum & ", " & rsChar!Number & ", '" & rsChar!Characteristic & "', " & _
                                rsChar!SpecCharNum & ", '" & rsChar!Spec & "', '" & rsChar!GageNumber & "', '" & _
                                rsChar!Samples & "', '" & rsChar!Frequency & "', '" & rsChar!Control & "', '" & _
                                rsChar!Reaction & "', " & rsChar!FAB & ", " & rsChar!MOV & ", " & rsChar!STO & ", " & _
                                rsChar!INS & ", '" & rsChar!ControlPrevention & "', '" & rsChar!ControlDetection & "', '" & _
                                rsChar!PotentialEffects & "', '" & rsChar!Severity & "', '" & rsChar!potentialcauses & "', '" & _
                                rsChar!Occurrence & "', '" & rsChar!RPN & "', '" & rsChar!RecommendedActions & "', '" & _
                                rsChar!Responsibilities & "', " & rsChar!TargetDate & ", '" & rsChar!ActionsTaken & "', '" & _
                                rsChar!ATSev & "', '" & rsChar!ATOcc & "', '" & rsChar!ATDet & "', '" & rsChar!ATRPN & "', '" & _
                                rsChar!Detection & "');"

Again, Modest, I REALLY appreciate your help and will take your comments into consideration in the future. Actually...scratch that...in the present. I'm going to clean these up in my code right after I post this message.

 

[hollering]

Actually, upon further inspection, that comma is there because the field being passed (TargetDate, which would be a date -- thanks Chris), is null. Would this cause the syntax error message? If so, how can I handle null values effectively? In this database they tend to be the rule more than the exception.

 

[hollering]

Solved the problem by using a variant variable to convert null values to the word NULL or use the rsChar value if it is not null. Just plugged that variable into the SQL statement instead of using the recordset values directly.

Thanks Chris and Modest

 

[hollering]

Solved the problem by using a variant variable to convert null values to the word NULL or use the rsChar value if it is not null. Just plugged that variable into the SQL statement instead of using the recordset values directly.

Thanks Chris and Modest

 

[modest]

I wasn't trying to be mean, I was just saying that it's easier for everyone if the code is easy to read. I use single quotes in VBA as well, but for Query Builder you need to use double quotes. Since you're doing it in VBA, stick with single if you want... we just didn't know where the SQL was going because we didn't see it wrapped in any code.

Second, if you can break it up into more tables, then your table is more than likely not 3NF.. I dont know what the data is or what the table is used for, but just by looking at the field names, it seems you could make more tables. Just remember, the larger your table size, the slower your queries.

Anyhow, I'm not saying you should put it in your code like this, but don't you see how the following is a lot easier to read? Even compared to what you posted. Easier to read means easier to catch mistakes

InsertCharSQL = _
                "INSERT INTO " & _
                    "[tblCharProduction] " & _
                "(" & _
                    "[SPI], " & _
                    "[OpNum], " & _
                    "[Number], " & _
                    "[Characteristic], " & _
                    "[SpecCharNum], " & _
                    "[Spec], " & _
                    "[GageNumber], " & _
                    "[Samples], " & _
                    "[Frequency], " & _
                    "[Control], " & _
                    "[Reaction], " & _
                    "[FAB], " & _
                    "[MOV], " & _
                    "[STO], " & _
                    "[INS], " & _                                
                    "[ControlPrevention], " & _
                    "[ControlDetection], " & _
                    "[PotentialEffects], " & _
                    "[Severity], " & _
                    "[PotentialCauses], " & _                
                    "[Occurrence], " & _
                    "[RPN], " & _
                    "[RecommendedActions], " & _
                    "[Responsibilities], " & _
                    "[TargetDate], " & _
                    "[ActionsTaken], " & _
                    "[ATSev], " & _
                    "[ATOcc], " & _
                    "[ATDet], " & _
                    "[ATRPN], " & _
                    "[Detection]" & _
                ") " & _ 
                "VALUES " & _ 
                "(" & _ 
                    "'" & NewSPI & "', " & _
                        & rsChar!OpNum & ", " & _
                        & rsChar!Number & ", " & _
                    "'" & rsChar!Characteristic & "', " _
                        & rsChar!SpecCharNum & ", " & _ 
                    "'" & rsChar!Spec & "', " & _
                    "'" & rsChar!GageNumber & "', " & _
                    "'" & rsChar!Samples & "', " & _
                    "'" & rsChar!Frequency & "', " & _
                    "'" & rsChar!Control & "', " & _
                    "'" & rsChar!Reaction & "', " _
                        & rsChar!FAB & ", " _
                        & rsChar!MOV & ", " _
                        & rsChar!STO & ", " _
                        & rsChar!INS & ", " & _
                    "'" & rsChar!ControlPrevention & "', " & _
                    "'" & rsChar!ControlDetection & "', " & _
                    "'" & rsChar!PotentialEffects & "', " & _
                    "'" & rsChar!Severity & "', " & _
                    "'" & rsChar!potentialcauses & "', " & _
                    "'" & rsChar!Occurrence & "', " & _
                    "'" & rsChar!RPN & "', " & _
                    "'" & rsChar!RecommendedActions & "', " & _
                    "'" & rsChar!Responsibilities & "', " _
                        & rsChar!TargetDate & ", " & _
                    "'" & rsChar!ActionsTaken & "', " & _
                    "'" & rsChar!ATSev & "', " & _
                    "'" & rsChar!ATOcc & "', " & _
                    "'" & rsChar!ATDet & "', " & _
                    "'" & rsChar!ATRPN & "', " & _
                    "'" & rsChar!Detection & "'" & _
                ");"

If those are all the fields of your recordset, there is a much easier way to do this. As far as syntax goes, I can't find any errors, but I'm not the best syntax catcher. If I have a syntax error, I just go line by line until I see the problem. You more than likely have a misspelled name or an extra comma, or a missing space.. something simple. That's because I dont believe syntax errors are related to mismatched datatypes.
I would like to see the database.

 

[modest]

By the way you need to wrap a date in the hash symbols so:

& rsChar!TargetDate & ", " & _

becomes

"#" & rsChar!TargetDate & "#, " & _


But, glad you solved your problem. If you looped through your fields you could test the datatype and it'd be easier to write the string

With rs
    Do Until .EOF
        For Each fld In .Fields
            Select Case  fld.Type
                Case vbString
                    strVar = "'" & fld.Value & "'"
                Case vbDate
                    strVar = "#" & fld.Value & "#"
                Case Else
                    strVar = fld.Value
            End Select
        Next
    .MoveNext
    Loop
End With

Something similar to that

 

[hollering]

Hmm...the first one looks great, and that's how I tried to do it this morning. However, when I get so many lines down I get an error about "Too many line continuations". I'm using Access 97, so I don't know if you're allowed more lines in 2000 or not, but now that I think back I remember that I had tried to write it like that and ran into that error.

I'm too tired to try to figure out the second method you posted...maybe tomorrow.

 

[ChrisO]

I think the limit is 50 in A97.

You can break the ‘more than 50 lines’ into chunks of less than 50 if you like, it’s just string manipulation.

Regards,
Chris.

 

[ChrisO]

Please let me try to explain about ‘it’s just a string’.

In the following, strSub starts out being a ZLS but gets added to along the way.
If we break up the string build concatenation into logical sections we not only overcome the limits of line continuation but also make it easier to read.

The first line to break the line continuation sequence is: -
vbNewLine & vbTab & "Dim strControlName As String"

But the string build is picked up again with the line: -
strSub = strSub & vbNewLine & _

So on we go and it becomes, well almost, readable: -

    [color=green]'   Now create the new Form's Form_Open Sub.  This is where the real fun starts![/color]
       strSub = vbTab & "Dim lngCol         As Long" & _
    vbNewLine & vbTab & "Dim lngRow         As Long" & _
    vbNewLine & vbTab & "Dim lngTop         As Long" & _
    vbNewLine & vbTab & "Dim lngLeft        As Long" & _
    vbNewLine & vbTab & "Dim lngWidth       As Long" & _
    vbNewLine & vbTab & "Dim lngHeight      As Long" & _
    vbNewLine & vbTab & "Dim lngOffset      As Long" & _
    vbNewLine & vbTab & "Dim strControlName As String"  [color=red]'  Continuation sequence broken[/color]

      strSub = strSub & vbNewLine & _                   [color=red]'  String build picked up again[/color]
    vbNewLine & vbTab & "Me.ScrollBars = False" & _
    vbNewLine & vbTab & "Me.RecordSelectors = False" & _
    vbNewLine & vbTab & "Me.NavigationButtons = False" & _
    vbNewLine & vbTab & "Me.RecordSource = " & Chr$(34) & "tblLocalities" & Chr$(34) 

      strSub = strSub & vbNewLine & _
    vbNewLine & vbTab & "lngTop = 100" & _
    vbNewLine & vbTab & "lngLeft = 100" & _
    vbNewLine & vbTab & "lngWidth = 400" & _
    vbNewLine & vbTab & "lngHeight = lngWidth" & _
    vbNewLine & vbTab & "lngOffset = lngWidth" & _
    vbNewLine & _
    vbNewLine & vbTab & "Me.Detail.Height = 9800"
      
      strSub = strSub & vbNewLine & _
    vbNewLine & vbTab & "For lngRow = 0 To " & CStr(lngNumberOfTextBoxes - 1) & _
    vbNewLine & vbTab & vbTab & "For lngCol = 0 To " & CStr(lngNumberOfTextBoxes - 1) & _
    vbNewLine & vbTab & vbTab & vbTab & "strControlName = " & Chr$(34) & "Text" & Chr$(34) & " " & Chr$(38) & " " & "CStr(lngRow * " & CStr(lngNumberOfTextBoxes) & " + lngCol)" & _
    vbNewLine & vbTab & vbTab & vbTab & "Me(strControlName).Top = lngTop" & _
    vbNewLine & vbTab & vbTab & vbTab & "Me(strControlName).Left = lngLeft" & _
    vbNewLine & vbTab & vbTab & vbTab & "Me(strControlName).Width = lngWidth" & _
    vbNewLine & vbTab & vbTab & vbTab & "Me(strControlName).Height = lngHeight" & _
    vbNewLine & vbTab & vbTab & vbTab & "Me(strControlName).BackStyle = 1" & _
    vbNewLine & vbTab & vbTab & vbTab & "Me(strControlName).ControlSource = " & Chr$(34) & "TestText" & Chr$(34) & _
    vbNewLine & vbTab & vbTab & vbTab & "Me(strControlName) = " & Chr$(34) & "_X_" & Chr$(34) & _
    vbNewLine & vbTab & vbTab & vbTab & "lngLeft = lngLeft + lngOffset" & _
    vbNewLine & vbTab & vbTab & "Next lngCol" & _
    vbNewLine & vbTab & vbTab & "lngLeft = 100" & _
    vbNewLine & vbTab & vbTab & "lngTop = lngTop + lngHeight" & _
    vbNewLine & vbTab & "Next lngRow"
        
      strSub = strSub & vbNewLine & _
    vbNewLine & vbTab & "Me.Text" & CStr(lngI) & ".Top = 8700" & _
    vbNewLine & vbTab & "Me.Text" & CStr(lngI) & ".Left = 1000" & _
    vbNewLine & vbTab & "Me.Text" & CStr(lngI) & ".Height = 300" & _
    vbNewLine & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 1) & ".Top = 8700" & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 1) & ".Left = 3000" & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 1) & ".Width = 2000" & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 1) & ".Height = 300" & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 1) & ".Caption = " & Chr$(34) & "Run Update with Echo" & Chr$(34) & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 1) & ".OnClick = " & Chr$(34) & "=Command" & CStr(lngI + 1) & "_OnClick()" & Chr$(34) & _
    vbNewLine & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 2) & ".Top = 8700" & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 2) & ".Left = 5000" & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 2) & ".Width = 2000" & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 2) & ".Height = 300" & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 2) & ".Caption = " & Chr$(34) & "Run Update no Echo" & Chr$(34) & _
    vbNewLine & vbTab & "Me.Command" & CStr(lngI + 2) & ".OnClick = " & Chr$(34) & "=Command" & CStr(lngI + 2) & "_OnClick()" & Chr$(34)

    [color=green]'   Create the new Form's Form_Open Sub.[/color]
    CreateFunction mdlThisFormsModule, "Form", "Open", "Form_Open(Cancel As Integer)", strSub

So we get to a point that strSub is in fact the entire source code for a Form_Open subroutine.

But it is still a string because we can still read it.
Next thing that happens is that the above string writes another string as source code for the compiler.
Some of the string that was created as source code for the compiler writes another string at compile time.

The compiler parses the resultant string and makes something executable out of it.

So some of the above ‘code’, which it isn’t, is three levels above the compiler making any sense out of it and, at least, four levels above the processor being able to run it.
So, in a sense, it is all ‘just a string’ and we never ‘write’ code…just a string.
(Not even in assembly or directly in machine ‘code’...it’s still ‘just a string’.)

Therefore, the point is to understand and be able to manipulate strings…they are the source code for the end result, whatever level it takes.

Hope that makes some sense.

Regards,
Chris.