ozydave
05-03-2018, 06:22 AM
Can’t get this to work. AM I going about this the wrong way?
I have spreadsheets sent daily, always with same headings
Date
Quantity
SKU Number in numerical order
Product Name
Order Number
Store Code
Store Name
I rename the spreadsheet adding my BAGcode.
In MS Access I have a form which imports the spreadsheet into a table. This works well.
My table has an additional column ‘BAGCode’ which I need to populate with the code I added when I renamed the spreadsheet.
My code grabs the first 6 characters of the spreadsheet name into a variable. ‘bagCode’
I then refresh the table.
So I only update the records from the latest spreadsheet import, my code gets the ‘Order Number’ from the last record in the table to build a sql update statement.
varsql = "UPDATE tblImportedOrderDetails SET tblImportedOrderDetails.BAGCode = " & bagCode & " WHERE [tblImportedOrderDetails.Order Number]= " & DLookup("[Order Number]", "tblImportedOrderDetails", "ID=" & DMax("ID", "tblImportedOrderDetails", "")) & ""
I then run ‘DoCmd.RunSQL varsql’
Because I can’t seem to get it to work I output via ‘MsgBox the varsql variable. Which look correct.
22157
When the last command is run ‘DoCmd.RunSQL varsql’ I get a popup box
22158
If I enter a value the correct records do get updated to what I just entered. Why does this popup box show?
Would there be a better way to populate the additional column?
First post so go easy
Cheers
I have spreadsheets sent daily, always with same headings
Date
Quantity
SKU Number in numerical order
Product Name
Order Number
Store Code
Store Name
I rename the spreadsheet adding my BAGcode.
In MS Access I have a form which imports the spreadsheet into a table. This works well.
My table has an additional column ‘BAGCode’ which I need to populate with the code I added when I renamed the spreadsheet.
My code grabs the first 6 characters of the spreadsheet name into a variable. ‘bagCode’
I then refresh the table.
So I only update the records from the latest spreadsheet import, my code gets the ‘Order Number’ from the last record in the table to build a sql update statement.
varsql = "UPDATE tblImportedOrderDetails SET tblImportedOrderDetails.BAGCode = " & bagCode & " WHERE [tblImportedOrderDetails.Order Number]= " & DLookup("[Order Number]", "tblImportedOrderDetails", "ID=" & DMax("ID", "tblImportedOrderDetails", "")) & ""
I then run ‘DoCmd.RunSQL varsql’
Because I can’t seem to get it to work I output via ‘MsgBox the varsql variable. Which look correct.
22157
When the last command is run ‘DoCmd.RunSQL varsql’ I get a popup box
22158
If I enter a value the correct records do get updated to what I just entered. Why does this popup box show?
Would there be a better way to populate the additional column?
First post so go easy
Cheers