Hello everyone,

I have yet another Insert Into statement error which might be
relatively easy to solve. Here's the problem:

I have a working VBA module which includes various SQL Insert Into
statments. Our system has a limit of taking in only 5 PRODUCT codes and
responding quantities in one row. So if the customer ordered 7 items,
the next two will have to go in second row but for the same customer.
It looks something like this:

BILL_NAME    CONTINUED    PARTNO1    QTY1    PARTNO2    QTY2  .....    PARTNO5    QTY5
JIM JONES    N           1111    1    1112    1    1115    1
            Y           1116    1    1117    1

The continued field if marked Y tells the system that the following 5
items will be at going under the same customer. There are other rules
to follow which is why i have to use a statement which inserts Values
into the specific field in the table rather than just doing Select
Bill_Name, Address, PARTNO1, ..., PARTNO5 FROM tblXXXXX. The following
statement does what it needs to flawlessly:

DoCmd.RunSQL "INSERT INTO MOM(ALTNUM, CONTINUED, PRODUCT01,
PRODUCT02,......)  VALUES ('" & strAltNum & "','" & strContinued4 &
"','" & rs("PARTNO6") & "','" & rs("PARTNO7") & "','" ......).

As you can imagine it is imperative that the statement with Y goes in
the table in order rather than ending up in the top or middle of the
list somewhere.

Now to the delimma. For some reason Access freaks out if the flat file
has more than 300 records to process. What it does is for the first 200
or so customers, it inserts the Y statement in order as it is being
inserted but once in a while it will insert the Y statement in the
middle of the list. What happens is when i export this table to a flat
file to feed it to our system, obviously it does not export it
correctly. I hope I'm making some sense here for some of you experts. I
dont know if the insert statement follows some rule of sorting while
inserting as it works fine in the earlier stages of inserting. Please
advice.