I've written some VBA code in Access 2003 and have been successfully inserting new Customer records.  However, when I try to insert a second level "job" Customer record where the new record should fall underneath an existing customer, I receive an error "Unspecified error" Error no. -2147467259..

This is the SQL I'm using: INSERT INTO Customer ("Name","IsActive","ParentRefFullName","CompanyName","BillAddressAddr1","BillAddressAddr2","BillAddressAddr3",
"BillAddressAddr4","BillAddressCity","BillAddressState","BillAddressPostalCode","BillAddressCountry",
"CustomerTypeRefFullName","TermsRefFullName") values('2K4053KM',1,'Smith Services, LLC','','Interested Underwriters','','','','','','','UNITED STATES','Assured Client','Net 30')

I've tried using the ParentRefListID in place of the ParentRefFullName but same result.  I've double-checked that the Parent customer is in fact in the table and spelled right, etc. so I'm pretty sure I am down to a syntax or conceptual problem.  I've assumed that QB will add the sublevel value based on the ParentRef since Sublevel is not an insertable field.  Any help or examples would be greatly appreciated.

- Ken

If you need to create a customer, you can use a format similar to this:

For USA Editions of QuickBooks:
INSERT INTO "customer" ("Name","LastName","FirstName","BillAddressAddr1",
"BillAddressAddr2","BillAddressCity","BillAddressState","BillAddressPostalcode")
values('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th
St.','Carlsbad','CA','92009')

For jobs, just add ParentRefListID or ParentRefFullName of the Jobs parent record along with a unique name for the JOB like this:

INSERT INTO "customer" ("Name","ParentRefFullname")
values ('Zuniga Trust', 'Zuniga, Daphne')

Thanks.  I simplified my INSERT statement and was able to get this to work and after adding back fields to my SQL statement I ended up being able to INSERT with what appears to be my original SQL.  Go figure!  Anyway, your response helped to confirm that my approach was correct.  I still don't know what prevented it at first but I've got my code running now and have inserted several hundred records.

I ran into one glitch though.  I have an existing Customer named 'SEA KING'.  When I try to insert a new record: 'Sea King, Inc.', I get error no. 3100 , "The name "'Sea King, Inc." of the list element is already in use."  It is not in use but I think it is comparing the string before the comma "Sea King" and sees that this substring in fact exists as a FullName in the Customer table.  I can successfully add Customers with commas in their name but in this case where the substring ahead of the comma on the INSERT data matches an existing Customer FullName, it appears to choke.  Is this a known issue or is something else going on here?

Thanks again.