Question Update select records using record ID number

[BRABUS]

Hi folks,

sorry I wasn't sure which section to post this under so admins please feel free to move it to the correct section.

I have a problem that I am sure you will all find very simple to fix.

I have a table of peoples times recorded that every so often I need to add a date to one of the fields based on a record number from another table.

Please let me try and explain. The teams times are input to the table called

tbl_TimeSheetData_ALLDATA
​

I then run a report on all the recorded times the team have submitted that ave not been invoiced yet based on the value of field

Invoice Date
​

being NULL and then export it to a spreadsheet (another person then plays with that data). Once they have done what they need to then then give me a date. I then need to put that date against those record numbers that I sent in the export.

I have a table called

TABLE - Change Invoicing Statement
​

that contains the following fields, taken from the spreadsheet I exported, they are

RECORDNUMBER
DATEINVOICED
​

How would I update the field: Invoice Date in the table:

tbl_TimeSheetData_ALLDATA
​

I haven't been lazy and have been googling like crazy but cant find a way to do this. I am sure this has to be easy to do in Access but the solution is still eluding me.

Please can someone help.

 

[HiTechCoach]

I assume you get a spreadsheet back that has the fields:

RECORDNUMBER
DATEINVOICED

If that is true then you should be able to to run an update query to set Invoice Date (in table) = DATEINVOICED (from spreadsheet data.

When I so imports like this I prefer to make a separate front end just for these tasks.

I like to import eh data from the external into a temp/work tables. This give me a change to validate the data and do any cleaned if needed. I also generally show the data to the user so the can verify this is what they want to import.

 

[arnelgp]

to update your "tbl_TimeSheetData_ALLDATA directly from spreadsheet, you should replaced the red-lettered names with the correct path, workbook and worksheet name of your excel file.

DoCmd.RunSQL "Update [tbl_TimeSheetData_ALLDATA] As A Inner Join [Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database=E:\Book1.xlsx].[Sheet1$] As B " & _
"On A.RecordNumber = B.RecordNumber Set A.[Invoice Date] = B.DateInvoiced;"

 

[BRABUS]

Hi Thanks everyone for the replies, I am sorry I haven't been back before to say thanks but I have been quite ill

Anyway I am going to have a go at this today and see how I get on. I like arnelgp's idea but I had already started with the importing of the spreadsheet into a table in the database called

TABLE - Change Invoicing Statement​

so I am curious how that would also work. HiTechCoach can you help out a little more with your version as well please now that you have the temporary table name.

many thanks again both of you

 

[BRABUS]

arnelgp - thanks your code words brilliantly.

 

[HiTechCoach]

Thanks for the update. Glad to hear that the code worked for you.