How to copy 9 fields data from table to other table automatically

[Guest]

One table has 9 fields. The other one table also has 9 fields. I would like
to input all 9 fields data in a table and i press input next record. 9 fields
data from this tables will send all these data to the specfiy table. Of
course, this table also have 9 fields. It's meant each record i have created,
it also created a same record to the specify table.

Original table: table1
Specify table: table2

I guess maybe like this:
[table2][name] = [table1][name]
[table2][in_date] = [table1][in_date]
.....

But i don't know how to do it. would you help me. thank you very much.

 

[Guest]

Create an append query that appends from Table1 to Table2. Make sure that
each field in Table1 gets appended to the correct field in Table2 in the
query (they related fields must be of the same data type). In the criteria of
Field1 of the query, specify [Forms]![Table1]![Field1]. This will append all
records from Table1 to Table2 where Field1 is equal to Field1 on your form.
Make sure Field1 is a unique key so that it appends only the record(s) you
want.

Now put a button on your form to copy the data. When it is clicked, run this
code:

DoCmd.OpenQuery "Query1"

where Query1 is the name of the above query.

 

[Mike Mueller]

Brian,
I am looking for a similiar type of solution- in that I need
to populate the 'current' table with all data from an
'archived' table. The archived tables are of varying names
depending on when they were created.

What I am looking to do is to bring up a ui for the user to
select the table and then bring that information in to
replace what is currently in the 'current' table

Do to reverse compatibility issues, this will need to be
compatible with Access 97

Any help you can give me would be great.

Mike


: Create an append query that appends from Table1 to Table2.
Make sure that
: each field in Table1 gets appended to the correct field in
Table2 in the
: query (they related fields must be of the same data type).
In the criteria of
: Field1 of the query, specify [Forms]![Table1]![Field1].
This will append all
: records from Table1 to Table2 where Field1 is equal to
Field1 on your form.
: Make sure Field1 is a unique key so that it appends only
the record(s) you
: want.
:
: Now put a button on your form to copy the data. When it is
clicked, run this
: code:
:
: DoCmd.OpenQuery "Query1"
:
: where Query1 is the name of the above query.
:
: "Alan48" wrote:
:
: > One table has 9 fields. The other one table also has 9
fields. I would like
: > to input all 9 fields data in a table and i press input
next record. 9 fields
: > data from this tables will send all these data to the
specfiy table. Of
: > course, this table also have 9 fields. It's meant each
record i have created,
: > it also created a same record to the specify table.
: >
: > Original table: table1
: > Specify table: table2
: >
: > I guess maybe like this:
: > [table2][name] = [table1][name]
: > [table2][in_date] = [table1][in_date]
: > ....
: >
: > But i don't know how to do it. would you help me. thank
you very much.

 

[Guest]

I am guessing that the data is in the same format for the various tables. In
this case, I would opt to simply add an "Archive" Yes/No field to the table
and either display or hide records depending on whether the user needs to see
the old stuff or not (instead of constantly moving things between tables).
You could also just show the user the info for a selected date range.

An alternative approach (if you really want to maintain archive tables)
would be to create a form that allows the user to pick, for instance, a date
range, then a query that first deletes all records from the "current" table
(if you want ONLY the archived data), then appends all records within the
date range into the "current" table.

I don't know how you would actually bring up a list of table names for the
user to pick from. I am sure there is a way, but it sounds like it might be
overkill. Perhaps some more detail would help.

Brian,
I am looking for a similiar type of solution- in that I need
to populate the 'current' table with all data from an
'archived' table. The archived tables are of varying names
depending on when they were created.

What I am looking to do is to bring up a ui for the user to
select the table and then bring that information in to
replace what is currently in the 'current' table

Do to reverse compatibility issues, this will need to be
compatible with Access 97

Any help you can give me would be great.

Mike


: Create an append query that appends from Table1 to Table2.
Make sure that
: each field in Table1 gets appended to the correct field in
Table2 in the
: query (they related fields must be of the same data type).
In the criteria of
: Field1 of the query, specify [Forms]![Table1]![Field1].
This will append all
: records from Table1 to Table2 where Field1 is equal to
Field1 on your form.
: Make sure Field1 is a unique key so that it appends only
the record(s) you
: want.
:
: Now put a button on your form to copy the data. When it is
clicked, run this
: code:
:
: DoCmd.OpenQuery "Query1"
:
: where Query1 is the name of the above query.
:
: "Alan48" wrote:
:
: > One table has 9 fields. The other one table also has 9
fields. I would like
: > to input all 9 fields data in a table and i press input
next record. 9 fields
: > data from this tables will send all these data to the
specfiy table. Of
: > course, this table also have 9 fields. It's meant each
record i have created,
: > it also created a same record to the specify table.
: >
: > Original table: table1
: > Specify table: table2
: >
: > I guess maybe like this:
: > [table2][name] = [table1][name]
: > [table2][in_date] = [table1][in_date]
: > ....
: >
: > But i don't know how to do it. would you help me. thank
you very much.

 

[Mike Mueller]

Quick overview on the process I am using-

I have a table 'current', which is the backbone of my
database for generating documents for the customs service.
The addresses are stored in another table, and linked to
'current'. On occasion, I have 2 sets of shipments going
out, so I created an archive command which copies the
'current' table as another table via a query, and then
another query deletes the records in the 'current' table and
get the second manifest going.

As I am the primary user, it is really no big deal for me to
open the archived table and select all-copy and then open
the 'current' table and select all-paste. I am just trying
to make it a little easier for the occasions that this
occurs and I am gone

I had though of the date-range, but there are several
occassions where I will have 2 or 3 shipments go on the same
day. It is hard enough to keep the 4 packages going to the
same address apart from a single shipment, adding in 3 more
would make nearly impossible to keep it straight

I tried the following, but was never able to select the
table which was opened:

Private Sub ImportRecord_Click()
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdSelectAll
DoCmd.RunCommand acCmdCopy
DoCmd.OpenTable "Current", acViewNormal, acEdit
DoCmd.RunCommand acCmdSelectAll
DoCmd.RunCommand acCmdPaste
DoCmd.Close acTable, "Current"
End Sub

Mike


:I am guessing that the data is in the same format for the
various tables. In
: this case, I would opt to simply add an "Archive" Yes/No
field to the table
: and either display or hide records depending on whether
the user needs to see
: the old stuff or not (instead of constantly moving things
between tables).
: You could also just show the user the info for a selected
date range.
:
: An alternative approach (if you really want to maintain
archive tables)
: would be to create a form that allows the user to pick,
for instance, a date
: range, then a query that first deletes all records from
the "current" table
: (if you want ONLY the archived data), then appends all
records within the
: date range into the "current" table.
:
: I don't know how you would actually bring up a list of
table names for the
: user to pick from. I am sure there is a way, but it sounds
like it might be
: overkill. Perhaps some more detail would help.
:
: "Mike Mueller" wrote:
:
: > Brian,
: > I am looking for a similiar type of solution- in that I
need
: > to populate the 'current' table with all data from an
: > 'archived' table. The archived tables are of varying
names
: > depending on when they were created.
: >
: > What I am looking to do is to bring up a ui for the user
to
: > select the table and then bring that information in to
: > replace what is currently in the 'current' table
: >
: > Do to reverse compatibility issues, this will need to be
: > compatible with Access 97
: >
: > Any help you can give me would be great.
: >
: > Mike
: >
: >
message
: >
: > : Create an append query that appends from Table1 to
Table2.
: > Make sure that
: > : each field in Table1 gets appended to the correct
field in
: > Table2 in the
: > : query (they related fields must be of the same data
type).
: > In the criteria of
: > : Field1 of the query, specify
[Forms]![Table1]![Field1].
: > This will append all
: > : records from Table1 to Table2 where Field1 is equal to
: > Field1 on your form.
: > : Make sure Field1 is a unique key so that it appends
only
: > the record(s) you
: > : want.
: > :
: > : Now put a button on your form to copy the data. When
it is
: > clicked, run this
: > : code:
: > :
: > : DoCmd.OpenQuery "Query1"
: > :
: > : where Query1 is the name of the above query.
: > :
: > : "Alan48" wrote:
: > :
: > : > One table has 9 fields. The other one table also has
9
: > fields. I would like
: > : > to input all 9 fields data in a table and i press
input
: > next record. 9 fields
: > : > data from this tables will send all these data to
the
: > specfiy table. Of
: > : > course, this table also have 9 fields. It's meant
each
: > record i have created,
: > : > it also created a same record to the specify table.
: > : >
: > : > Original table: table1
: > : > Specify table: table2
: > : >
: > : > I guess maybe like this:
: > : > [table2][name] = [table1][name]
: > : > [table2][in_date] = [table1][in_date]
: > : > ....
: > : >
: > : > But i don't know how to do it. would you help me.
thank
: > you very much.
: >
: >
: >

 

[Guest]

Thank you Brian. But i would like to say an apologize to you, i cannot handle
the code what you teach. Now i open a ftp account for you. If you are free,
would you mind spend some time go to "ftp://brian:[email protected]"
to download "Inventory Report.mdb" to do one time your method to me. thank
you so much. If you do not want to do it for me. would you teach more details
of your method. thanks

Create an append query that appends from Table1 to Table2. Make sure that
each field in Table1 gets appended to the correct field in Table2 in the
query (they related fields must be of the same data type). In the criteria of
Field1 of the query, specify [Forms]![Table1]![Field1]. This will append all
records from Table1 to Table2 where Field1 is equal to Field1 on your form.
Make sure Field1 is a unique key so that it appends only the record(s) you
want.

Now put a button on your form to copy the data. When it is clicked, run this
code:

DoCmd.OpenQuery "Query1"

where Query1 is the name of the above query.

One table has 9 fields. The other one table also has 9 fields. I would like
to input all 9 fields data in a table and i press input next record. 9 fields
data from this tables will send all these data to the specfiy table. Of
course, this table also have 9 fields. It's meant each record i have created,
it also created a same record to the specify table.

Original table: table1
Specify table: table2

I guess maybe like this:
[table2][name] = [table1][name]
[table2][in_date] = [table1][in_date]
....

But i don't know how to do it. would you help me. thank you very much.

 

[Guest]

Quick overview on the process I am using-

I have a table 'current', which is the backbone of my
database for generating documents for the customs service.
The addresses are stored in another table, and linked to
'current'. On occasion, I have 2 sets of shipments going
out, so I created an archive command which copies the
'current' table as another table via a query, and then
another query deletes the records in the 'current' table and
get the second manifest going.

As I am the primary user, it is really no big deal for me to
open the archived table and select all-copy and then open
the 'current' table and select all-paste. I am just trying
to make it a little easier for the occasions that this
occurs and I am gone

I had though of the date-range, but there are several
occassions where I will have 2 or 3 shipments go on the same
day. It is hard enough to keep the 4 packages going to the
same address apart from a single shipment, adding in 3 more
would make nearly impossible to keep it straight

I tried the following, but was never able to select the
table which was opened:

Private Sub ImportRecord_Click()
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdSelectAll
DoCmd.RunCommand acCmdCopy
DoCmd.OpenTable "Current", acViewNormal, acEdit
DoCmd.RunCommand acCmdSelectAll
DoCmd.RunCommand acCmdPaste
DoCmd.Close acTable, "Current"
End Sub

Mike

I've been thinking about what you want to do and I think I might have come
up with a way to get you moving forward.

It appears that you have a form with a button (ImportRecord) on it. Add a
combo box - I named it "cboListTables" - and set the following properties:

Format tab:
Column count: 2
Column widths: 0;2 (or something wide)

Data tab:
Limit to list: YES
Record source type: Table/Query
Record source:

SELECT msysobjects.Name, [name] & " - " & [dateupdate] AS LastUpdate FROM
msysobjects WHERE (((msysobjects.Name) Not Like 'msys*' And Not
(msysobjects.Name)='current') AND ((msysobjects.Type)=1)) ORDER BY
msysobjects.DateUpdate DESC;


I modified the code for the ImportRecords button:

'*** begin code ***
Private Sub ImportRecord_Click()
Dim strSQL As String
Dim response As Integer
Dim msg As String

' check for no selection
If IsNull(Me.cboListTables) Then
MsgBox "Please select a table"
Me.cboListTables.SetFocus
Me.cboListTables.Dropdown
Exit Sub
End If

msg = "Do you want to copy records from table" & Me.cboListTables & " to
table CURRENT?"
msg = msg & vbCrLf & vbCrLf
msg = msg & "All records in table CURRENT will first be deleted!!!"
response = MsgBox(msg, vbYesNo + vbDefaultButton2 + vbQuestion)
If response = vbYes Then
strSQL = "DELETE * FROM Current;"
CurrentDb.Execute strSQL

strSQL = "INSERT INTO Current SELECT * FROM " & Me.cboListTables & ";"
CurrentDb.Execute strSQL

'you could close the form here after update
'DoCmd.Close acForm, Me.Name

End If

End Sub
'*** end code ***


NOTE: The archive table structure must match the table CURRENT structure or
the code bombs. Do you have a naming convention for the archive tables so you
could limit the tables names returned to *just* the archive tables?


Anyway, HTH

 

[David C. Holley]

If I understand it from reading the various replies, you have two tables
- one 'current' and one 'archive' I'm curious about the comment

I have 2 sets of shipments going
out, so I created an archive command which copies the
'current' table as another table via a query, and then
another query deletes the records in the 'current' table and
get the second manifest going.

What is the big picture? What is the process that you're trying to
automate? Do you need the capability of sending out multiple manifests?
I'm asking because I'm curious as to wether or not there are some
underlying design issues.

David H

Quick overview on the process I am using-

I have a table 'current', which is the backbone of my
database for generating documents for the customs service.
The addresses are stored in another table, and linked to
'current'. On occasion, I have 2 sets of shipments going
out, so I created an archive command which copies the
'current' table as another table via a query, and then
another query deletes the records in the 'current' table and
get the second manifest going.

As I am the primary user, it is really no big deal for me to
open the archived table and select all-copy and then open
the 'current' table and select all-paste. I am just trying
to make it a little easier for the occasions that this
occurs and I am gone

I had though of the date-range, but there are several
occassions where I will have 2 or 3 shipments go on the same
day. It is hard enough to keep the 4 packages going to the
same address apart from a single shipment, adding in 3 more
would make nearly impossible to keep it straight

I tried the following, but was never able to select the
table which was opened:

Private Sub ImportRecord_Click()
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdSelectAll
DoCmd.RunCommand acCmdCopy
DoCmd.OpenTable "Current", acViewNormal, acEdit
DoCmd.RunCommand acCmdSelectAll
DoCmd.RunCommand acCmdPaste
DoCmd.Close acTable, "Current"
End Sub

Mike


:I am guessing that the data is in the same format for the
various tables. In
: this case, I would opt to simply add an "Archive" Yes/No
field to the table
: and either display or hide records depending on whether
the user needs to see
: the old stuff or not (instead of constantly moving things
between tables).
: You could also just show the user the info for a selected
date range.
:
: An alternative approach (if you really want to maintain
archive tables)
: would be to create a form that allows the user to pick,
for instance, a date
: range, then a query that first deletes all records from
the "current" table
: (if you want ONLY the archived data), then appends all
records within the
: date range into the "current" table.
:
: I don't know how you would actually bring up a list of
table names for the
: user to pick from. I am sure there is a way, but it sounds
like it might be
: overkill. Perhaps some more detail would help.
:
: "Mike Mueller" wrote:
:
: > Brian,
: > I am looking for a similiar type of solution- in that I
need
: > to populate the 'current' table with all data from an
: > 'archived' table. The archived tables are of varying
names
: > depending on when they were created.
: >
: > What I am looking to do is to bring up a ui for the user
to
: > select the table and then bring that information in to
: > replace what is currently in the 'current' table
: >
: > Do to reverse compatibility issues, this will need to be
: > compatible with Access 97
: >
: > Any help you can give me would be great.
: >
: > Mike
: >
: >
message
: >
: > : Create an append query that appends from Table1 to
Table2.
: > Make sure that
: > : each field in Table1 gets appended to the correct
field in
: > Table2 in the
: > : query (they related fields must be of the same data
type).
: > In the criteria of
: > : Field1 of the query, specify
[Forms]![Table1]![Field1].
: > This will append all
: > : records from Table1 to Table2 where Field1 is equal to
: > Field1 on your form.
: > : Make sure Field1 is a unique key so that it appends
only
: > the record(s) you
: > : want.
: > :
: > : Now put a button on your form to copy the data. When
it is
: > clicked, run this
: > : code:
: > :
: > : DoCmd.OpenQuery "Query1"
: > :
: > : where Query1 is the name of the above query.
: > :
: > : "Alan48" wrote:
: > :
: > : > One table has 9 fields. The other one table also has
9
: > fields. I would like
: > : > to input all 9 fields data in a table and i press
input
: > next record. 9 fields
: > : > data from this tables will send all these data to
the
: > specfiy table. Of
: > : > course, this table also have 9 fields. It's meant
each
: > record i have created,
: > : > it also created a same record to the specify table.
: > : >
: > : > Original table: table1
: > : > Specify table: table2
: > : >
: > : > I guess maybe like this:
: > : > [table2][name] = [table1][name]
: > : > [table2][in_date] = [table1][in_date]
: > : > ....
: > : >
: > : > But i don't know how to do it. would you help me.
thank
: > you very much.
: >
: >
: >

 

[Guest]

Sorry about the delay - I was out of town for a couple of days. I tried
connecting to your FTP address, but got server resets - do you need to
re-enable the FTP for me?

Thank you Brian. But i would like to say an apologize to you, i cannot handle
the code what you teach. Now i open a ftp account for you. If you are free,
would you mind spend some time go to "ftp://brian:[email protected]"
to download "Inventory Report.mdb" to do one time your method to me. thank
you so much. If you do not want to do it for me. would you teach more details
of your method. thanks

Create an append query that appends from Table1 to Table2. Make sure that
each field in Table1 gets appended to the correct field in Table2 in the
query (they related fields must be of the same data type). In the criteria of
Field1 of the query, specify [Forms]![Table1]![Field1]. This will append all
records from Table1 to Table2 where Field1 is equal to Field1 on your form.
Make sure Field1 is a unique key so that it appends only the record(s) you
want.

Now put a button on your form to copy the data. When it is clicked, run this
code:

DoCmd.OpenQuery "Query1"

where Query1 is the name of the above query.

One table has 9 fields. The other one table also has 9 fields. I would like
to input all 9 fields data in a table and i press input next record. 9 fields
data from this tables will send all these data to the specfiy table. Of
course, this table also have 9 fields. It's meant each record i have created,
it also created a same record to the specify table.

Original table: table1
Specify table: table2

I guess maybe like this:
[table2][name] = [table1][name]
[table2][in_date] = [table1][in_date]
....

But i don't know how to do it. would you help me. thank you very much.