Append Single Record From A Form

[daniesmith]

First, if I am not in the correct place, please let me know. I am a novice needing help. I have a single DB (Contractor) with 17 various fields. The purpose is to track the type of license they have, i.e. plumbing, electrical, HVAC, general, etc..... One contractor can have one to many types of licenses. I created a form to add the contractors the first time - no problems. It is the second, third license (type) where I am having a problem. I created another form to lookup the contractor by name (in a combo box) and bring all the fields into the form. I then edit the type (license) field and want to append the data back into the database as a new record. I am using a append query. Following is the syntax. Instead of a new record (same data except a differnet type) the current record is just overwritten? Help..... Is there a easier method?

INSERT INTO Contractor ( Company, Type, OT_Type_Desc, Lic_Date, Contact, Phys_Addr, Phys_City, Phys_St, MA_Addr, MA_City, MA_St, MA_Zip, Phone_1, Phone_2, Fax )
SELECT [Forms]![ContForm]![Combo0] AS Company, [Forms]![ContForm]![Combo57] AS Type, [Forms]![ContForm]![Text81] AS OT_Type_Desc, [Forms]![ContForm]![Text83] AS Lic_Date, [Forms]![ContForm]![Text85] AS Contact, [Forms]![ContForm]![Text87] AS Phys_Addr, [Forms]![ContForm]![Text89] AS Phys_City, [Forms]![ContForm]![Text91] AS Phys_St, [Forms]![ContForm]![Text93] AS MA_Addr, [Forms]![ContForm]![Text95] AS MA_City, [Forms]![ContForm]![Text97] AS MA_St, [Forms]![ContForm]![Text99] AS MA_Zip, [Forms]![ContForm]![Text101] AS Phone_1, [Forms]![ContForm]![Text103] AS Phone_2, [Forms]![ContForm]![Text105] AS Fax;

[debasisdas]

If you want to over write the record you need to use UPDATE not INSERT.

[AndreRet]

This looks like MS Access 2007. The INSERT to SELECT is then 100% for appending a record.

Assuming that the correct record was selected, the code will run fine. There is however no other "shorter/quick"method to update the data, you have to call every field and set its data. You do however do not need to update data that you will not be editing, in other words - if company name etc stays the same, you leave the field out. Only add the new data that was changed.

[daniesmith]

Thanks for the response. You are correct it is Acces 2007 (I should have mentioned that). I did not make myself clear. I don't want to update the current record. I want to edit it and then copy it back as a new record. Sorry for the confusion.

[AndreRet]

Now I'm confused... :)

You want to edit a current record, in other words, change a fields value to something else (say a field called name that has a value "daniesmith" to a new value "AndreRet" whilst keeping the same record id...

Then you want to copy that same record and add it as an entire new record with a new record id but the same values? Not sure I understand exactly what you need... I'll gladly help if you can give me more info.

[daniesmith]

The table is for tracking the different licenses a contractor can have (i.e. plumbing, electrical, HVAC, etc...) so one contractor can have up to 6 different type licenses. Setting up the first license can be accomplished by just adding a record to the table (contractor) via a form (LicForm). It is the 2nd, 3rd, 4th, etc... for the same contractor that I having a problem with. I currently am loading a form (newlic) with a combobox (by looking at the company and license type). I then am wanting to only change 1 field, the type license field to the new type and then save (append) the record (as a new record). Does this help? I really appreciate your help on this. Dan

[AndreRet]

Ok, the way I see it now is ...

The contractor can have multiple licenses, each with its own record, i.e. rec1... daniesmith... plumber AND rec2... daniesmith... electrician etc.

If a contractor exists with 1 or more licenses, you can give him another license providing its not allocated to him as yet.

This means that we will not update the first record where the contractor was selected, but only add a new record under his id with the new license description which will then have a new record number, say 1 was selected, 2 is the new record?

Your statement is then correct ...

So, to answer your original question, there is NO other shorter way of doing it than to specify each fields value to ensure that the data is captured as required.

[daniesmith]

Thanks so much. I really appreciate it. I have been wading through this stuff trying to learn as I go. Again, thanks so much. Dan

[AndreRet]

Only a pleasure Dan. Please mark this as solved, thanx. :)