Sub or Function not defined

[walrus]

Hi All

I've got a weird problem. When I try to compile the code below I get the error message 'Sub or Function not defined'. I'm using Access 2007.

    For x = 0 To 20

        If Nz(rec1(x), "") <> Nz(rec2(x), "") Then
            
            DoCmd.SetWarnings False

            strSQL = "INSERT INTO tblResults ([Version], CUSTOMER_ID, CUSTOMER_NAME, CUST_LONG_NAME, STREET, CITY, POSTAL_CODE, COUNTRY_ID, AREA_MGR_ID, AREA_MGR, KAM_ID, KAM, SALES_REP_1_ID, SALES_REP_1, SALES_REP_2_ID, SALES_REP_2, SALES_REP_3_ID, SALES_REP_3, SALES_REP_4_ID, SALES_REP_4, SALES_REP_5_ID, SALES_REP_5) " & _
            "VALUES ('" & intVersion & "', '" & rec1(0) & "', '" & rec1(1) & "', '" & rec1(2) & "', '" & rec1(3) & "', '" & rec1(4) & "', '" & rec1(5) & "', '" & rec1(6) & "', '" & rec1(7) & "', '" & rec1(8) & "', '" & rec1(9) & "', '" & rec1(10) & "', '" & rec1(11) & "', '" & rec1(12) & "', '" & rec1(13) & "', '" & rec1(14) & "', '" & rec1(15) & "', '" & rec1(16) & "', '" & rec1(17) & "', '" & rec1(18) & "', '" & rec1(19) & "', '" & rec1(20) & "');"
            
            DoCmd.RunSQL strSQL

            strSQL = "INSERT INTO tblResults ([Version], CUSTOMER_ID, CUSTOMER_NAME, CUST_LONG_NAME, STREET, CITY, POSTAL_CODE, COUNTRY_ID, AREA_MGR_ID, AREA_MGR, KAM_ID, KAM, SALES_REP_1_ID, SALES_REP_1, SALES_REP_2_ID, SALES_REP_2, SALES_REP_3_ID, SALES_REP_3, SALES_REP_4_ID, SALES_REP_4, SALES_REP_5_ID, SALES_REP_5, [Modified], [Modified By]) " & _
            "VALUES ('" & intVersion & "', '" & rec2(0) & "', '" & rec2(1) & "', '" & rec2(2) & "', '" & rec2(3) & "', '" & rec2(4) & "', '" & rec2(5) & "', '" & rec2(6) & "', '" & rec2(7) & "', '" & rec2(8) & "', '" & rec2(9) & "', '" & rec2(10) & "', '" & rec2(11) & "', '" & rec2(12) & "', '" & rec2(13) & "', '" & rec2(14) & "', '" & rec2(15) & "', '" & rec2(16) & "', '" & rec2(17) & "', '" & rec2(18) & "', '" & rec2(19) & "', '" & rec2(20) & "', '" & rec(23) & "', '" & rec(24) & "');"
            
            DoCmd.RunSQL strSQL

            x = 0
            DoCmd.SetWarnings True
            GoTo NextRecord

        End If

    Next x

What I'm trying to do is compare two recorsdsets and if it finds a difference, record the result line from both recordsets in a new table. This worked fine until I tried to go beyond more than 21 records from recordset 2 to publish to the Results table.

The problem arises in the 2nd INSERT INTO... statement. I get the error and it highlights the rec2(20) value. I can enter anything after it (it doesn't have to be a recordset value) but the error still occurs and it wont compile. If I close the statement at Rec2(20) it works fine.

I've no idea why. I can work around it but its a faff - is there something obvious I've missed?

 

[pr2-eugin]

walrus, I am sorry to be the bearer of bad news but your Table does not look Normalized.. You need to act on it.. You seem to have stored much information in one single record, which makes it very hard to Query and use the data efficiently..

Then you seem to be storing duplicate values in the same table.

What you need is a Design restructure not a simple fix. Simple fix can patch up the immediate issue but will cause permanent damage in the not very far future.. Good Luck.

 

[jdraw]

Further to Paul's comment, I think it would be beneficial to the reader if you would post the entire vba for a procedure and not just a snippet.
You might also want to review the db.execute command with dbFailOnError parameter instead of the SetWarnings True/False.

I agree that table structure is probably the real issue.

 

[walrus]

This is how the data is stored in the source database tables! I know it's not a very efficient way of doing things but the original developers set the database up this way and this is what I have to work with. When I create local databases I usually do normalise the data.

As it happens I solved my problem - I forgot to add a '2' to rec(23) and rec(24) - works now! Doh.

 

[pr2-eugin]

Again this is just a patch, remember you now have the power to make the change, this is very highly required for you to do now. Else (quoting from the document I gave you),

...who break these rules do so at their own peril.

 

[walrus]

*Sigh*... I wish. The 'source' database was developed for users who cant link tables, who have no idea what 'normalization' is and are too busy to learn how to do it properly - managers and the like. So if I develop some new report and I get to re-create tables locally I sort it out properly. But the source database is used world-wide and is now too big to re-develop. It is due for replacement but that wont be for another five years - with a bit of luck I'll have found another job by then. Or be dead. Win-win either way.

 

[pr2-eugin]

with a bit of luck I'll have found another job by then. Or be dead. Win-win either way.

You and me are in the same boat I guess..