New Macro User

[FCVIII]

OK Here goes. I am trying to use macros such that when the user clicks an entry from a list box, the macro willl search for a prexisting record copy the entire record and copy it to another table. Essentialy to select records from one table and enter them in another. Any help woulf be greatly appreciated.

FCVIII

 

[David R]

Just to clarify..

Why do you want to copy the entire record? Is there a reason that copying the Primary Key will not suffice?

 

[FCVIII]

macro

Well the first table is a large it has like 1000 records so far. I will not need to use all of them so I didn’t assign primary keys.

I wanted to create a new table where I could pick which records are important and then assign a primary key. There is no primary key in the first table. I did not want to retain a non-sequential series when I created the second table.

So I have several questions right now
First.
If Table 2 references the primary key for table 1 will that create a table 2 containing the actual records that I want or will that just bring the primary key to the field, which may create a relationship. Table two will be my primary table for the database everything else I do comes from table two.
And how do I do that?
Right now I have a list box but the macro I have just opens table one and I’m trying to get the macro to record the focus/control (I don’t know what its called) and search for the record with a matching first field, then copy and paste that record two table two.

Second
Is there a Access/Visual Basic for access book that you recommend. I am such a beginner! I have limited programming knowledge in C, matlab and Fortran 90 (don’t laugh) but right now I am working without a reference.


Thank you very much for your response.

 

[David R]

So your Table One is only being used to select which records you actually want in your Table Two, and then will be discarded?

If so, here's how I would do it. Add a checkbox field (Yes/No) to your Table One. Call it [Transfer] or something similar. Now if you can go through your table and find all the records that you want to keep, check this box to Yes. Make a backup!

Now create an Append Query that only selects those records where [Transfer] is True (-1). Run your Append Query to your new Table Two (you don't have to copy over the checkbox field, obviously, but it needs to be in the query so you can apply criteria). Make sure you really did get all the records you want, then you can get rid of Table One.

If you need Table One still at some point in the future then I don't understand your question yet. Also, you made a comment "I did not want to retain a non-sequential series when I created the second table." I hope you don't expect your Autonumber PK in Table two to be unfailingly sequential either for all points in the future. As an example, watch what happens if you start your Append query but then cancel it when it asks for confirmation, then run it again and accept. Your Autonumbers will start at whatever the last number would have been from the first run, plus 1. Deleting records at any time will render the series imperfect as well. Autonumber fields are simply not designed to be perfectly sequential; they're designed to be unique.

As far as books go, there's a lot of good ones out there. Access for Dummies is not bad for beginners. Intermediately, I use QUE's Special Edition Microsoft Access <version>. Advanced users here in the forum often mention The Access Bible.