SinghSuperStud

iTrader: (2)

Hi !

is there any way I can use a macro or VB to move a set number of records from one table and append them to another ?

I have table of unallocated records which need to be allocated. I've done a form from which you select a name and check allocate. This should move records from the unallocated table to the allocated table and add the relevant name in one of the fields.

any help appreciated !

thanks, Jai

Fatman

You can run a predefined query from code, or even define, run and delete a query from code. How about...

SELECT your records and copy to a recordset
APPEND them to your target table
DELETE them from your source table

...or is that far too simplistic?

chiark

Fatman's suggestion will work providing you're not in a multi-user situation. If there is the potential for someone to update the table with an unallocated record, you need to delete from the original table where a unique identifier (eg primary key) is not in the allocated table.

Fatman

Doesn't Access have the ability to apply record locking?

chiark

should do, yes, but I'm unsure whether you can lock across queries hence the slight paranoia when it comes to deleting stuff

SinghSuperStud

iTrader: (2)

thanks all !

Fatman - wat I'm after is the necessary code to do the select, append and delete functions as I'm fairly new so SQL and my knowldge of VB is somewhat limited. In particular, I only want to select a predefined number of records (50).

suggestions ?

thanks again

Jai

GammonRoll

Assuming I have grasped your requirement you need to right click the button and select build event, code builder. This should pull up the VB interface and assuming i've grasped it correctly the following should work:

Docmd.runsql("INSERT INTO tblAllocated SELECT * From TblUnallocated WHERE AllocatedFlag = True")
docmd.runSQL("DELETE * FROM tblUnallocated WHERE AllocatedFlag = TRUE")
This takes no account of record locking or hiding of user messages. Is this what you're looking for? If not, them PM me and I'll see if I can help.

Cheers,

Gamm

Jza

You dont need a select and then append query - as the dude above says, you need to do an append. One thing you might want to do is clear the table you append to first - or if you dont have a table to append to - create a make-table query which does it for you. If you want to keep the table you created, just change the query from a make-table to an append (to the new table) query.

Try and get away from macros straight away - they are VERY BAD.... trust me - ive been using Access for 10 years

Create a module and put the code in there - with some sort of error trapping in!

Jza