Dlookup List for copying

[asmith]

Hey everyone,

I'm trying to copy records from one table to another when a person selects "approve" on a form. I have the function built so that when they select "approve" some VBA will run, but what i'm having trouble with is selecting the records to copy.

What I think I want to do is to create a list from a dlookup to find each record in the "pending table" where a certain criteria is met, and then to create a loop that makes a new record in the "history table" for each item in the list. I can't do a direct copy of the entire record as there is information in the first table that is not required in the second. My main problem is i think i know what i want to do but i have no idea how to do it



Help!

Thanks
Andrew

[TheShabz]

Hey!

how does the user select the data? I think that would be your biggest hurdle. if it's a query that runs with form options to use as WHERE filters, then you can simply embed that query into a SELECT INTO query to copy into the other table, modifying the query to only select the values needed in your copy-to table.

[asmith]

I think the best way for me to try and explain how they access the data is to attach a copy of the database. The attached has one main ticket "Citi 163695" and several sub tickets identified by AN####* ####.

If you open up "Input Form" which is the main form, and go to Citi 163695, you will see the combo box for Status which currently has "Pending Approval". What i want to have happen is copy the info in the subform on the right to the history table. As you will be able to see there are several ID's for this one main ticket that are also represented in the history subform on the bottom left of the form.

To kind of answer your question, yes there is a query running already that identifies the SICS numbers for the ticket, and it is the information found on the SICS ID Detail table that corresponds to those SICS numbers that i want to copy to the history table.

Confusing enough?

Thanks!
Andrew

[TheShabz]

Hey, I dont have Access at home and wont be back in the office until Tuesday. Hopefully someone else will be able to help you out until then.

[asmith]

No worries, Thanks for the heads up

[slave138]

I would personally use an SQL string (UPDATE ... SET ... WHERE) to update the historical table. If you are more comfortable with it, you can use the Query Builder but I like the extra flexibility calling it directly from VBA.

All that said, my main suggestion is to start by going through the DB and renaming all of the controls, fields, tables, queries, etc... which use special characters (& and / mostly) as they cause tons of trouble when it comes to writing the code.