Simple VBA macro to copy records from one table to another (1 Viewer)

[dim4x4]

Hi!

I am a newbie here. I have some experience with Excel VBA, but Access is totally different. Could you please help me with the following?

I have two tables in my Access database, their fields are exactly the same (for now). One table is called Uncheched. The other one is called Checked.

So what is need is a macro that takes selected records in table Unchecked and copies them to the end of table Checked. Actually CUTS from Unchecked and PASTES to the end of Checked table.

Thank you!

PS. This is what I'm trying to do, but without much success so far

Sub MoveRecords()

DoCmd.RunCommand acCmdCut
DoCmd.OpenTable "tblChecked", acNormal, acEdit
DoCmd.GoToRecord , , acLast
DoCmd.RunCommand acCmdPaste

End Sub

 

[Uncle Gizmo]

Why not keep all the records in one single table and provide the option to add a checkbox against a particular record?

 

[namliam]

well in database land we dont do things that way....

We would have only one table and have a Checked field that would contain a Yes or a No.

You should then have a query for checked and unchecked to access your table/data.

 

[dim4x4]

Well, many people will have access to the Unchecked table. They will be entering records. There will be mistakes, some records would not be approved at all, etc. Only one person (me) will have access to the Checked table. I do not want other people who enter data into Unchecked table to be able to see or even worse - edit any records after they have been checked by me and copied to the Checked table. I'm even thinking of having two separate database files - Unchecked and Checked. And password protect Checked file to be viewed and/or edited only by myself.

So Unchecked table/file most of the time should be empty or have just a few pending-to-be-checked records.

Thank you!

 

[Uncle Gizmo]

Why are you using tables for your users to enter data? You should be using a form..

 

[dim4x4]

How using a form would help me do what I am trying to do? BTW, I'm only a few days on Access, it was all Excel before and it was much easier.

 

[Uncle Gizmo]

A form will allow you to control the data that is entered and make your job easier in the long run.

 

[dim4x4]

Thank you! Will try the forms. But cutting/pasting from one table to another - is it doable in principle in Access?

 

[Uncle Gizmo]

Thank you! Will try the forms. But cutting/pasting from one table to another - is it doable in principle in Access?

You would probably be better off sticking with Excel.

 

[dim4x4]

Ok, thank you!

 

[namliam]

Cutting/pasting is doable, but ill-adviced....

When making the step from Excel to Access it is best to forget all you think you know about data in Excel and learn everything a new....
Checked/unchecked as said belong in a single table.... added field(s), checked yes/no and/or checked date

As gizmo put forward, data entry in access should be controled in a form, where you can validate a lot of stuff before the (faulty) data goes into your table.
I.e. a column that can / should contain Yes, No, Maybe and only those three values can be controled so that a user cannot put in "possibly" ... etc....

Also you can control the data users see by using the afore mentioned queries, they will only see the unchecked, you on the other hand using the table, a second query or a second form can see all....

That is how is should be done "access style", though depending on your situation more work is probably involved.

 

[dim4x4]

namliam, thank you! I agree with all of you that what you suggest is the right way to do it. But that ivolves some learning curve which under my current time constraints is not feasible. So I wanted a quick fix for my situation. In the future when the workload is not as intense, it all will be done the right way. Again, thank you for your help and suggestions!

 

[namliam]

Copy/pasting is a worse learning curve as it needs some doing as well PLUS you are going in the wrong direction

 

[The_Doc_Man]

The whole point of Access to do what you are doing is that if you control what your users can see from the beginning (using a form), you can use code behind the form to protect you from user depredation. However, your point about time investment is not an insubstantial consideration.

Here is what you might consider as a faster way to do this. You have a way to select candidates for movement to the "Checked" table. (You said so in the #1 frame of this thread.) So... write two queries.

INSERT INTO Checked (field1, field2, etc, etc.) SELECT field1, field2, etc. etc. FROM Unchecked WHERE selector-field = TRUE ;

DELETE * FROM Unchecked WHERE selector-field = TRUE;

The above represents the raw SQL. You would of course put the correct table names, field names, and selection condition into a pair of strings and then do something like

DoCmd.RunSQL InsertQuery
DoCmd.RunSQL DeleteQuery

This will be FAR faster than using recordsets.

Having told you how to do this quickly up front, remember that I am recognizing your time constraints. Long-term, this should be done by discarding the Excel-think solution and learning Access enough to keep your users from being able to interfere with your actions. For example, using data entry from one form that your users can see but doing the check-mark operations on a second form that has more features, or on the same form that is sensitive to your role within the company.