Create a new record from an existing one

A

achett

Hello:
I have 2 tables that are linked to create a query. I also have a form that
is based on the query. The problem is that I'am not able to make changes to
an existing record and save it as a new record using the form.

Structure is as follows:
1st Table
Name: JobType
Fields: Job Type ID(Primary Key,Autonumber)
Other fields: Job Name, Job Title, Parameter 1...and soo on

2nd Table
Name:Job Event ID
Fields: Job Event ID( Primary Key, Autonumber)
Job Type ID (Number)
Other fields: Printer, lines...so on

In my query, I have the Job Type ID selected from Job Events Table and not
from Job Type Table.

Any help is greatly appreciated.

Thanks
achett
 
D

Dirk Goldgar

achett said:
Hello:
I have 2 tables that are linked to create a query. I also have a form
that is based on the query. The problem is that I'am not able to make
changes to an existing record and save it as a new record using the
form.

Structure is as follows:
1st Table
Name: JobType
Fields: Job Type ID(Primary Key,Autonumber)
Other fields: Job Name, Job Title, Parameter 1...and soo on

2nd Table
Name:Job Event ID
Fields: Job Event ID( Primary Key, Autonumber)
Job Type ID (Number)
Other fields: Printer, lines...so on

In my query, I have the Job Type ID selected from Job Events Table
and not from Job Type Table.

Any help is greatly appreciated.

Thanks
achett
Click to expand...

What's the SQL of your query? Which table do you want to create a new
record in? What exactly is the nature of the problem you're having? Is
it that the existing record is being changed, rather than (or in
addition to) a new record being created? Or is it that you're getting
an error creating the new record? How are you going about it?
 
A

achett

Hello:
Is it possible to make keep the exisitng record and make only few changes
and save it as a new record.

Following is the SQL code of the query.

SELECT [Job Event].[Job Type ID], [Job Event].[JobEvent ID], [Job Type].[Job
Name], [Job Type].[Job Title], [Job Type].Printer, [Job Type].[Special
Printer], [Job Type].Lines, [Job Type].Delivery, [Job Type].[Parameter
Number1], [Job Type].[Parameter Description1], [Job Event].[Parameter
Value1], [Job Type].[Parameter Number2], [Job Type].[Parameter Description2],
[Job Event].[Parameter Value2], [Job Type].[Parameter Number3], [Job
Type].[Parameter Description3], [Job Event].[Parameter Value3], [Job
Type].[Parameter Number4], [Job Type].[Parameter Description4], [Job
Event].[Parameter Value4], [Job Type].[Parameter Number5], [Job
Type].[Parameter Description5], [Job Event].[Parameter Value5], [Job
Type].[Parameter Number6], [Job Type].[Parameter Number6], [Job
Type].[Parameter Description6], [Job Event].[Parameter Value6], [Job
Type].[Parameter Number7], [Job Type].[Parameter Description7], [Job
Event].[Parameter Value7], [Job Type].[Parameter Number8], [Job
Type].[Parameter Description8], [Job Event].[Parameter Value8], [Job
Type].[Parameter Number9], [Job Type].[Parameter Description9], [Job
Event].[Parameter Value9], [Job Type].[Parameter Number10], [Job
Type].[Parameter Description10], [Job Event].[Parameter Value10], [Job
Type].[Default Parameter10], [Job Type].[Parameter Number11], [Job
Type].[Parameter Description11], [Job Event].[Parameter Value11], [Job
Type].[Parameter Number12], [Job Type].[Parameter Description12], [Job
Event].[Parameter Value12], [Job Type].[Parameter Number13], [Job
Type].[Parameter Description13], [Job Event].[Parameter Value13], [Job
Type].[Parameter Number14], [Job Type].[Parameter Description14], [Job
Event].[Parameter Value14], [Job Type].[Parameter Number15], [Job
Type].[Parameter Description15], [Job Event].[Parameter Value15], [Job
Type].[Parameter Number16], [Job Type].[Parameter Description16], [Job
Event].[Parameter Value16], [Job Type].[Parameter Number17], [Job
Type].[Parameter Description17], [Job Event].[Parameter Value17], [Job
Type].[Parameter Number18], [Job Type].[Parameter Description18], [Job
Event].[Parameter Value18], [Job Type].[Parameter Number19], [Job
Type].[Parameter Description19], [Job Event].[Parameter Value19], [Job
Type].[Parameter Number20], [Job Type].[Parameter Description20], [Job
Event].[Parameter Value20], [Job Type].[Parameter Number21], [Job
Type].[Parameter Description21], [Job Event].[Parameter Value 21], [Job
Type].[Parameter Number22], [Job Type].[Parameter Description22], [Job
Event].[Parameter Value22], [Job Type].[Parameter Number23], [Job
Type].[Parameter Description23], [Job Event].[Parameter Value23], [Job
Type].[Parameter Number24], [Job Type].[Parameter Description24], [Job
Event].[Parameter Value24], [Job Type].[Parameter Number25], [Job
Type].[Parameter Description25], [Job Event].[Parameter Value25], [Job
Type].[Parameter Number26], [Job Type].[Parameter Description26], [Job
Event].[Parameter Value26], [Job Type].[Parameter Number27], [Job
Type].[Parameter Description27], [Job Event].[Parameter Value27], [Job
Type].[Parameter Number28], [Job Type].[Parameter Description28], [Job
Event].[Parameter Value28], [Job Type].[Parameter Number29], [Job
Type].[Parameter Description29], [Job Event].[Parameter Value29], [Job
Type].[Parameter Number30], [Job Type].[Parameter Description30], [Job
Event].[Parameter Value30]
FROM [Job Type] INNER JOIN [Job Event] ON [Job Type].[Job Type ID] = [Job
Event].[Job Type ID];

Thanks
achett
 
A

achett

Hello:
Is it possible to save the record to the query? I would like to keep the
exisiting record and make the necessary changes and save it as a new record.
The SQL query is as follows:

SELECT [Job Event].[Job Type ID], [Job Event].[JobEvent ID], [Job Type].[Job
Name], [Job Type].[Job Title], [Job Type].Printer, [Job Type].[Special
Printer], [Job Type].Lines, [Job Type].Delivery, [Job Type].[Parameter
Number1], [Job Type].[Parameter Description1], [Job Event].[Parameter
Value1], [Job Type].[Parameter Number2], [Job Type].[Parameter Description2],
[Job Event].[Parameter Value2], [Job Type].[Parameter Number3], [Job
Type].[Parameter Description3], [Job Event].[Parameter Value3], [Job
Type].[Parameter Number4], [Job Type].[Parameter Description4], [Job
Event].[Parameter Value4], [Job Type].[Parameter Number5], [Job
Type].[Parameter Description5], [Job Event].[Parameter Value5], [Job
Type].[Parameter Number6], [Job Type].[Parameter Number6], [Job
Type].[Parameter Description6], [Job Event].[Parameter Value6], [Job
Type].[Parameter Number7], [Job Type].[Parameter Description7], [Job
Event].[Parameter Value7], [Job Type].[Parameter Number8], [Job
Type].[Parameter Description8], [Job Event].[Parameter Value8], [Job
Type].[Parameter Number9], [Job Type].[Parameter Description9], [Job
Event].[Parameter Value9], [Job Type].[Parameter Number10], [Job
Type].[Parameter Description10], [Job Event].[Parameter Value10], [Job
Type].[Default Parameter10], [Job Type].[Parameter Number11], [Job
Type].[Parameter Description11], [Job Event].[Parameter Value11], [Job
Type].[Parameter Number12], [Job Type].[Parameter Description12], [Job
Event].[Parameter Value12], [Job Type].[Parameter Number13], [Job
Type].[Parameter Description13], [Job Event].[Parameter Value13], [Job
Type].[Parameter Number14], [Job Type].[Parameter Description14], [Job
Event].[Parameter Value14], [Job Type].[Parameter Number15], [Job
Type].[Parameter Description15], [Job Event].[Parameter Value15], [Job
Type].[Parameter Number16], [Job Type].[Parameter Description16], [Job
Event].[Parameter Value16], [Job Type].[Parameter Number17], [Job
Type].[Parameter Description17], [Job Event].[Parameter Value17], [Job
Type].[Parameter Number18], [Job Type].[Parameter Description18], [Job
Event].[Parameter Value18], [Job Type].[Parameter Number19], [Job
Type].[Parameter Description19], [Job Event].[Parameter Value19], [Job
Type].[Parameter Number20], [Job Type].[Parameter Description20], [Job
Event].[Parameter Value20], [Job Type].[Parameter Number21], [Job
Type].[Parameter Description21], [Job Event].[Parameter Value 21], [Job
Type].[Parameter Number22], [Job Type].[Parameter Description22], [Job
Event].[Parameter Value22], [Job Type].[Parameter Number23], [Job
Type].[Parameter Description23], [Job Event].[Parameter Value23], [Job
Type].[Parameter Number24], [Job Type].[Parameter Description24], [Job
Event].[Parameter Value24], [Job Type].[Parameter Number25], [Job
Type].[Parameter Description25], [Job Event].[Parameter Value25], [Job
Type].[Parameter Number26], [Job Type].[Parameter Description26], [Job
Event].[Parameter Value26], [Job Type].[Parameter Number27], [Job
Type].[Parameter Description27], [Job Event].[Parameter Value27], [Job
Type].[Parameter Number28], [Job Type].[Parameter Description28], [Job
Event].[Parameter Value28], [Job Type].[Parameter Number29], [Job
Type].[Parameter Description29], [Job Event].[Parameter Value29], [Job
Type].[Parameter Number30], [Job Type].[Parameter Description30], [Job
Event].[Parameter Value30]
FROM [Job Type] INNER JOIN [Job Event] ON [Job Type].[Job Type ID] = [Job
Event].[Job Type ID];

Thanks
achett
 
D

Dirk Goldgar

achett said:
Hello:
Is it possible to save the record to the query? I would like to keep
the exisiting record and make the necessary changes and save it as a
new record. The SQL query is as follows:

SELECT [Job Event].[Job Type ID], [Job Event].[JobEvent ID], [Job
Type].[Job Name], [Job Type].[Job Title], [Job Type].Printer, [Job
Type].[Special Printer], [Job Type].Lines, [Job Type].Delivery, [Job
Type].[Parameter Number1], [Job Type].[Parameter Description1], [Job
Event].[Parameter Value1], [Job Type].[Parameter Number2], [Job
Type].[Parameter Description2], [Job Event].[Parameter Value2], [Job
Type].[Parameter Number3], [Job Type].[Parameter Description3], [Job
Event].[Parameter Value3], [Job Type].[Parameter Number4], [Job
Type].[Parameter Description4], [Job Event].[Parameter Value4], [Job
Type].[Parameter Number5], [Job Type].[Parameter Description5], [Job
Event].[Parameter Value5], [Job Type].[Parameter Number6], [Job
Type].[Parameter Number6], [Job Type].[Parameter Description6], [Job
Event].[Parameter Value6], [Job Type].[Parameter Number7], [Job
Type].[Parameter Description7], [Job Event].[Parameter Value7], [Job
Type].[Parameter Number8], [Job Type].[Parameter Description8], [Job
Event].[Parameter Value8], [Job Type].[Parameter Number9], [Job
Type].[Parameter Description9], [Job Event].[Parameter Value9], [Job
Type].[Parameter Number10], [Job Type].[Parameter Description10],
[Job Event].[Parameter Value10], [Job Type].[Default Parameter10],
[Job Type].[Parameter Number11], [Job Type].[Parameter
Description11], [Job Event].[Parameter Value11], [Job
Type].[Parameter Number12], [Job Type].[Parameter Description12],
[Job Event].[Parameter Value12], [Job Type].[Parameter Number13],
[Job Type].[Parameter Description13], [Job Event].[Parameter
Value13], [Job Type].[Parameter Number14], [Job Type].[Parameter
Description14], [Job Event].[Parameter Value14], [Job
Type].[Parameter Number15], [Job Type].[Parameter Description15],
[Job Event].[Parameter Value15], [Job Type].[Parameter Number16],
[Job Type].[Parameter Description16], [Job Event].[Parameter
Value16], [Job Type].[Parameter Number17], [Job Type].[Parameter
Description17], [Job Event].[Parameter Value17], [Job
Type].[Parameter Number18], [Job Type].[Parameter Description18],
[Job Event].[Parameter Value18], [Job Type].[Parameter Number19],
[Job Type].[Parameter Description19], [Job Event].[Parameter
Value19], [Job Type].[Parameter Number20], [Job Type].[Parameter
Description20], [Job Event].[Parameter Value20], [Job
Type].[Parameter Number21], [Job Type].[Parameter Description21],
[Job Event].[Parameter Value 21], [Job Type].[Parameter Number22],
[Job Type].[Parameter Description22], [Job Event].[Parameter
Value22], [Job Type].[Parameter Number23], [Job Type].[Parameter
Description23], [Job Event].[Parameter Value23], [Job
Type].[Parameter Number24], [Job Type].[Parameter Description24],
[Job Event].[Parameter Value24], [Job Type].[Parameter Number25],
[Job Type].[Parameter Description25], [Job Event].[Parameter
Value25], [Job Type].[Parameter Number26], [Job Type].[Parameter
Description26], [Job Event].[Parameter Value26], [Job
Type].[Parameter Number27], [Job Type].[Parameter Description27],
[Job Event].[Parameter Value27], [Job Type].[Parameter Number28],
[Job Type].[Parameter Description28], [Job Event].[Parameter
Value28], [Job Type].[Parameter Number29], [Job Type].[Parameter
Description29], [Job Event].[Parameter Value29], [Job
Type].[Parameter Number30], [Job Type].[Parameter Description30],
[Job Event].[Parameter Value30]
FROM [Job Type] INNER JOIN [Job Event] ON [Job Type].[Job Type ID] =
[Job Event].[Job Type ID];
Click to expand...

Yikes! Any time you have field names repeated and numbered like that,
you should take a close look at your table design. It generally means
you are taking what should be a separate table -- in this case,
something that would probably be called "JobTypeParameters", with fields
JobTypeID, ParameterNumber and ParameterDescription -- and expressing it
as fields in one table. It makes for a very cumbersome design.

You didn't really answer my questions, so I'm not sure what is the best
advice to give you. One thing that you probably *don't* want to do, if
you can avoid it, is start changing a record and then deciding that you
want to save it as a new record instead. You could do it, of course --
it would probably involve using the form's RecordsetClone to add a new
record, picking up the necessary field values from the controls on the
form, then calling the form's Undo method to undo all changes to the
form's current record, and finally moving the form to the record you
just added. But that would be rather messy, and leave open the
possibility of the process being interrupted and the original record
being changed when you didn't want it to be.

It would be better to create a duplicate (except for the autonumber key
field) of the unmodified current record, move the form to that record,
and modify the duplicate. That way, there's no chance of the original
record being changed unintentionally. The command button wizard will
build a button with code to do this, if you choose the right category
and action in the wizard's dialogs.

The only thing I'm not sure of now is how that will work with your
query, since you have joined records from two tables. The best way to
find out, I guess, is to try it and see. If the wizard's code won't
work, there are other ways.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding data to MS Access from csv file 0
VBA works in orignal but fails in copy of database 1
New Record from an Existing one 0
DMax on existing record to increment serialized # fails 1
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Subform New Record 2
Please help having ordering issues 0
Unable to add new records to a form 0

Top