Append a new table

[Chris Wagner]

I am working with two tables
Table A has 50,000 records
Table B has 0 records

I have set up a Query that does a search on table A which works quite
well. It comes up in a Design Sheet Format showing a list of possible
matches. What I want to do is have a check box in the first column when
Table A is showing. And when checked, and the window Closed (Ctrl W)
it will create a new record or records in Table B with the ID's of the
records checked in Table A.

So If I was able to find and check 340 records in Table A...when done
Table B would have 340 records.

Where might be the best place to start looking , or what might be the
'keywords' in help, or if someone has an answer....

thanks
Chris Wagner

 

[John Vinson]

I am working with two tables
Table A has 50,000 records
Table B has 0 records

I have set up a Query that does a search on table A which works quite
well. It comes up in a Design Sheet Format showing a list of possible
matches. What I want to do is have a check box in the first column when
Table A is showing. And when checked, and the window Closed (Ctrl W)
it will create a new record or records in Table B with the ID's of the
records checked in Table A.

So If I was able to find and check 340 records in Table A...when done
Table B would have 340 records.

Where might be the best place to start looking , or what might be the
'keywords' in help, or if someone has an answer....

Simplest would be to change the design of TableA to add a YesNo field,
"CopyMe" or whatever. Put this first in the list of fields in your
query.

Then run an Append query, using a criterion of True on CopyMe and
appending the records to TableB.

You can't just add a checkbox to a query since a query has no
existance independent of the table or tables which make it up. As an
alternative, you can create a table with two fields - its primary key
a foreign key to TableA (creating a one to one relationship) and the
other this yes/no field; include this table in your query and base the
append on the two tables joined.

 

[Arvin Meyer]

This can be done, but not quite the way you want. There are no events on
tables, nor should you be working on them anyway. If you use a datasheet
view of a form, you can run code with the click event of a checkbox do send
the data over to Table B, or you could wait until you are done and send them
all at once.

I'd almost prefer them one at a time, since you could always delete them
that way too (aircode):

Sub chkWhatever(Cancel As Integer)

If Me.chkWhatever = True Then
CurrentDB.Execute "INSERT INTO TableB( ID, ..., ..., ... ) SELECT
ID, ..., ..., ... FROM TableA WHERE [ID] = " & Me.txtID
Else
CurrentDB.Execute "Delete * From TableB WHERE [ID] = " & Me.txtID

End Sub

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Chris Wagner]

Thank You for your help I see I missed a 0 in the records in table A
(500,000) I had thought of importing the 500,000 ID's into a new table and
creating a link. On Arvins idea I'll need to do some more reading to
completely understand where the code goes. I can see the events in a
form.... just need some more work at it.

Thanks Again
Chris Wagner

 

[Chris Wagner]

Added a checkbox field to Table A
Index on the Checkbox (Yes Duplicates)
Did the search of records manually and checked the boxes
Did a 'Make Table' Query with checkbox = Yes
Created new table of those checked

Thanks again
Chris Wagner