Saving search results from a linked Excel file into another table

[themushroomking]

Hi there folks!
I'm pretty dang stuck over here. Hope to get some direction on this matter. All replys are much appriciated.

What i have:
- I have a linked excel file with data (old stock)
- I have a search form to retrieve data from this excel file
- And I have another form that creates new records and stores it into another table (new stock items)



What i want to do:
- I open an excisting record in my form (from new stock items table) (so far so good)
- On the same form page i do a search and retrieve data (from old stock items table) (so far so good)
- I want to save that search results from my search form (old stock) >> into the new stock items table on the current record im in. (completely clueless!)

So: I just want to save my search results from the excel file and store it into the table on the record that im currently working in.

I hope i got it clear for you guys

Could anyone help me out a little? Thanks in advance!

[aytee111]

Why don't you do a one-time copy of all the old stock to the new stock table? It being Excel, I can't think that there will be that many records.

[themushroomking]

Hi there aytee111!

Thanks for the reply. Ofcourse a very valid question.
The problem is: The excel file is being posted weekly with updates by a supplier. I know its very uncomfortable, but its what i have to deal with.

Right now i made a subform (my search form) and put in on top of my form to create new entries (new stock).
All i need to do, is add the search result to the ID of the new stock is some fields.

[aytee111]

An update query is what you need.
- select "new" stock item - how is this done?
- run update query to update the relevant fields from linked Excel file
- filter the form to display the "new" record

You do not need the search function, it should be automatic based on selected stock item. Be careful not to overwrite existing data - is it possible for a user to change something once it has been copied over from Excel?