Append a new table

  • Thread starter Thread starter Chris Wagner
  • Start date Start date
C

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
 
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....
Click to expand...

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.
 
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
 
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
 
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
 
Back
Top