Moving Records From one Table to Another

[jstringer]

The database I am designing has 2 tables both containing the exact same fields. One table stores current information about guests (room, bed, dates, monies)in our hostel and the other is a historic table which stores data once the guests have checked out.

I need to write code that will search through the current information table looking for a room and bed number, then copy the room and bed number and move the dates and monies fields into the historic table.

Any help on this would be greatly appreciated!

Thanks in advance,

John

 

[ryan1]

You can do a RunSQL Insert statement that will Archive all old data to Historic table where a textbox = " & a certain record & " in your table. Then do a RunSQl Delete statement to delete these from your original table. If you give me some table and field names i will help you build the statement.

 

[jstringer]

Thank you for the quick response. I am relatively new to Access so I'm not to sure exactly what you mean. Both tables (GuestInfo and OldGuestInfo) are setup with the same fields which are as follows:

Passport, Nationality, Name, Date Entered, Date Paid, Monies Paid, Date Paid To, Accumlalate, Deposit, Room, Bed, and Date Checked out.

I would like to move all of the fields except Room and Bed from GuestInfo to OldGuestInfo.

Thanks again for the help!

John

 

[jstringer]

Oh and also, the records that I want to move would be based on a particular room and bed number. I would like the command to be able to read in two text boxes, room and bed number, find that record in GuestInfo and move it to the OldGuestInfo table.

Hope this doesn't complicate things to much

John