Not in list Question

[Mclaren]

I have customers table (tbl_Debtors) and I also have "invoices not paid" table (tbl_Invoices_not_Paid). The secondary key on the tbl_Invoices_not_Paid is the customer_ID in the tbl_Debtors table, and as such is set to maintain referential integrity. The data for the tbl_Invoices_not_Paid is obtained from an excel spreadsheet, which is an output from SAP.

When i import the data by use of a datasheet using the simple copy and paste method of selecting all the lines from the excel spreadsheet and pasting into a datasheet form, sometimes there maybe a new customer on the excel spreadsheet that has not been updated to my MS access database.



When i paste the records from the spread sheet into the ms access datasheet, all goes well until a customer that does not exist. I get the not in list error, and the copy and paste action stops.

I would like to be able to code my form to automatically create a new record in the customers table and then finish the paste of records to the invoices not paid table, (including any lines on the excel spreadsheet for the missing customer).

[Estuardo]

G'd Evening Mclaren,
You may approach the problem in several ways, but any of them you will need to avoid at any cost the copy/paste part.
The Firs step would be to import your excel worksheet row by row or import the whole worksheet into a temporal table.
The second. Query the imported data to look for new customers. If exist a new one add to your customers table and "Grab" its id
The Third step would be to add the rows from your temp table to your table "tbl_Invoices_not_Paid"
Finally clean up the temp table.
Depending on the structure of your tables and the worksheet the whole process is not complicated some lines of code and a couple of queries and you're done.
To transfer the whole worksheet you may want to try this
DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel4,"yourTableName"," YourExcelDoc.xlsx","UpToYou"
If you want to do do it with code, you may want to use this objects: Excel.Application, Excel.Worksheet and Excel.Workbook.

G'd luck