Append to a specific record???

[gmatriix]

Hello All,

Don't know if this is possible or not but I have a append query that I would like to append to a specific record.

Is this possible?

something like ...append to record where the ID matches "whatever"

Any Ideas?

 

[pbaldy]

Well, an append query adds a new record. If you want to modify a existing record, you want an update query.

 

[gmatriix]

Thanks,

What Im trying to do is duplicate record on my main form and subform and paste them to a new record. The subform is join by the [product id] field.

So I am creating a new product id by "Me.[Product ID] = Nz(DMax("[product ID]", "product"), 1000) + 1"

And I just want to copy the current record and subform data to the new record with the new id. I am trying to figure this out. I thought by using append queries I could accomplish this with:

DoCmd.OpenQuery "CopyProd"
DoCmd.OpenQuery "CopyRoute"
DoCmd.RefreshRecord
DoCmd.GoToRecord acDataForm, Me.Name, acLast
Me.[Product ID] = Nz(DMax("[product ID]", "product"), 1000) + 1
DoCmd.RefreshRecord

It works but the "CopyRoute" is not going to the right record because I need something that will tell it to append to new created id.

Please I am new at this ....so what Im doing may not make sense to you but....can you help??

Thanks

 

[pbaldy]

Ah, you mean you need the key value of the new "one" record to use in the "many" table? Is it an autonumber? If you're creating the number with that code, you would just need to use it. Maybe put the value in a form and have the queries get it there. Personally I don't typically try to manipulate a form around adding the records. I add the records to the table and then requery the form to display them.

 

[gmatriix]

No, Product Id is not a autonumber. It still has own unique id. I just used the code to create a product id each time a new record is added. I just need to be able to copy those records and add them to the same new record....

I guess I know what i want to do but ensure on how to set it up to work.

Any ideas?

 

[pbaldy]

You mean another idea besides this one?

Maybe put the value in a form and have the queries get it there.

 

[gmatriix]

OK,

What I am not sure of is .....if I am able to use the number. how do I tell the query to append to that specific record with that product id?

So if my new product ID is 70244

I'm not sure on how to have the queries to append to the record with the product id of 70244...

thanks:banghead:

 

[pbaldy]

If you do what I described:

INSERT INTO ...
SELECT [Forms]![FormName]![TextboxName] ...

 

[gmatriix]

Thanks for your help

I did not end up doing that. Could not get it to work. However, I figured it out using append queries.

I used Dmax on the product Id for both queries to make the product id's match and Refreshed and it worked!

thanks.....