Add current record to table

[TinaR]

I'm using Access 2007. I'd like to use a command button on a form to
populate the current record's information to another table. I thought using
an append query (which would be activated by the command button) would work
but I'm not getting the results I want.

How do I select the current record on the form so only this record is
appended to the table? Table1 is the table behind my form. And Table2 is the
table I'd like the append query to write to. Both tables have auto number
primary keys.

Thanks for your help.
Tina

 

[TinaR]

The append query works but I'm running into some difficulty. Currently, both
my tables have an autonumber ID as the primary key. They aren't related in
any way. If I leave these ID #'s as primary keys, I get a key violation and
lock violation error message and the append doesn't occur. If I remove the
primary keys, the append works except that a new random id # is assigned when
the record is written to my new table. This new id# may be a duplicate of
another number already in the table.

What I would like to see happen is this:
The user clicks the button to import the current record. The current record
is written to the new table as a new record with the next sequential id
number. I don't want to delete the record from its original table after it's
appended. Is this even possible?

Thanks,
Tina

 

[TinaR]

This is what I'm trying to do. We’re using a form to enter new customers. In
order to be a customer, paperwork needs to be sent out and received back.
We’re tracking the paperwork that has been sent out but if the paperwork is
never received back we don’t want these potential customers added to our
roster. So what I thought I would do is separate the tables and create two
forms.

The Current form (and Table1) would be for all customers that have completed
the paperwork and can now be rostered.

The Pending form (and Table2) would be for all those pending customers. As
these pending customers send in their paperwork, I want to add them to Table1
because they have now completed the paperwork and we can roster them. I
wanted to use a command button to add the current record to Table1 so my
admin doesn’t have to re-type everything. (This is where by clicking the
command button the append query would be activated).

I know it’s not the best practice to have two tables with similar data but I
want table 1 to contain only those customers that are truly customers because
they’ve completed our paperwork process.
I’m open to any suggestions and a better way of doing things.
Thanks,
Tina

It sounds like you may have some normalization issues (the same record in
more than one table). Maybe if you described what these records contain we
can suggest a better solution.

The append query works but I'm running into some difficulty. Currently, both
my tables have an autonumber ID as the primary key. They aren't related in
any way. If I leave these ID #'s as primary keys, I get a key violation and
lock violation error message and the append doesn't occur. If I remove the
primary keys, the append works except that a new random id # is assigned when
the record is written to my new table. This new id# may be a duplicate of
another number already in the table.

What I would like to see happen is this:
The user clicks the button to import the current record. The current record
is written to the new table as a new record with the next sequential id
number. I don't want to delete the record from its original table after it's
appended. Is this even possible?

Thanks,
Tina

Maybe this link will give you some ideas.
http://allenbrowne.com/ser-37.html

[quoted text clipped - 11 lines]

Thanks for your help.
Tina

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.



.

 

[TinaR]

Thanks for the guidance. I added a few additional fields to my original
table and created a new form with only the fields I need. The form opens
based on a query of my original table and filtering out only the criteria I
need for this second form. I think this should work nicely. Again, many
thanks!
Tina

All it takes is another field (YES/NO-TRUE/FALSE) in the first table
indicating if they have completed their paperwork.

This is what I'm trying to do. We’re using a form to enter new customers. In
order to be a customer, paperwork needs to be sent out and received back.
We’re tracking the paperwork that has been sent out but if the paperwork is
never received back we don’t want these potential customers added to our
roster. So what I thought I would do is separate the tables and create two
forms.

The Current form (and Table1) would be for all customers that have completed
the paperwork and can now be rostered.

The Pending form (and Table2) would be for all those pending customers. As
these pending customers send in their paperwork, I want to add them to Table1
because they have now completed the paperwork and we can roster them. I
wanted to use a command button to add the current record to Table1 so my
admin doesn’t have to re-type everything. (This is where by clicking the
command button the append query would be activated).

I know it’s not the best practice to have two tables with similar data but I
want table 1 to contain only those customers that are truly customers because
they’ve completed our paperwork process.
I’m open to any suggestions and a better way of doing things.
Thanks,
Tina

It sounds like you may have some normalization issues (the same record in
more than one table). Maybe if you described what these records contain we

[quoted text clipped - 22 lines]

Thanks for your help.
Tina

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com


.