Upissue Transfer of data

[Ali29J]

Hi All

I have a database which manages all the quotes we issue to our customers.

Scenario - customer requests a requote

Requirement - Use code to transfer data from an old revision to the new revision

Setup - would like to to select a check box, with command button action to transfer all records, as new records in the same table

could anyone offer any starters...

Thanks

Ali

 

[Remou]

How about an append query?

[tt]strSQL="INSERT INTO tblTable ( Field1 ) SELECT tblTable.Field1
FROM tblTable Where ID =" & Me.ID
DoCmd.RunSQL strSQL[/tt]

 

[Ali29J]

Hi Remou

THanks for getting back to me.

so where you call fields, do you list every field which is require to be transfered?

Could you explain the ID section, as dont understand its functon??

THanks

ALi

 

[Remou]

The ID is whatever you use to uniquely identify the quote (in my database it is the RFQNo). The way I visualised this happening was that the user would look up the old quote on a form and decide to transfer it, so the ID would be on the screen, hence Me.ID, above.
You would list all fields that are to be transferred. Do not include autonumbers. You may wish to set date fields to different dates and so on. The easiest way to get the SQL for the query is to build it in the Query Design screen, when you have everything working the way you want, cut and paste the SQL into your code and make the small changes necessary for running SQL in code.
It is also possible to run such a query from code as a query (DoCmd.OpenQuery), but I think this way is best.

 

[Ali29J]

ok i understand, thankyou for that i will have a crack at it andsee how it goes!

THanks again

Ali

 

[Ali29J]

Hi Remou

COuld you help, how do i deal with multiples

strSQL="INSERT INTO tblTable ( Field1 ) SELECT tblTable.Field1, tblTable.Field2, tblTable.Field3

as below??

INSERT INTO tblTable ( Field1, Field2, Field3 ) ????

or

INSERT INTO tblTable ( Field1), tblTable ( Field2), tblTable ( Field3)

????

THanks

Ali

 

[Ali29J]

also

FROM tblTable Where ID =" & Me.ID keeps returning expected enof statement error....

 

[Ali29J]

OK SO i ve got the following working or sorts...

strSQL = "INSERT INTO RFQDetail (RFQ)SELECT RFQDetail.RFQ FROM RFQDetail WHERE RFQID =" & Me.RFQID
DoCmd.RunSQL strSQL

Have 3 issues:
1. it will not append the data due to key violations, how do i ensure that "ItemID" which is autonumber primary key will get updated as a record is added

2. i need to transfer multiple items

3. i would like it transfer only where RFQID applies but also if check box requote = true

would appreciate any help...

ALi

 

[Ali29J]

Hi Remou

I have got it working as a transfer using the below

strSQL = "INSERT INTO RFQDetail (RFQ, RFQID, Customer, [PN#], Description, [Qty Required], Requote, QtyDetails, Notes) SELECT (RFQDetail.RFQ = forms!frmRequoteStep!rfq), (RFQDetail.RFQID = forms!frmRequoteStep!rfq), RFQDetail.Customer, RFQDetail.[PN#], RFQDetail.Description, RFQDetail.[Qty Required], RFQDetail.Requote, RFQDetail.QtyDetails, RFQDetail.Notes FROM RFQDetail where (((RFQDetail.RFQID)=[forms]![frmRequoteStep]![COmbo5]) AND ((RFQDetail.Requote)=True))"
DoCmd.RunSQL strSQL

I need one final modification, COmbo5 represents the old quote number, which also filters a subform to show the components of the quote, the user then selects check box against the items to be transfered. all works great except it transfers it into the same RFQID

So on the INSERT INTO command, i actually need the RFQID to be inserted against the master form RFQID, which is the new quote ID

Can anyone offer how i can adjust code so the INSERT TO RFQ, RFQID = the new RFQ and RFQID as opposed to that filtered in the subform...

Its really baffling me....

Any help greatly appreciated

ALi

 

[Remou]

I guess you have already created the main record, which has the RFQID? Look up this number and use it in your query, for example:

[tt]intRFQID = DMax("RFQID", "tblQuotes")

strSQL = "INSERT INTO RFQDetail " _
& "(RFQ, RFQID, Customer, [PN#], " _
& "Description, [Qty Required], Requote, " _
& "QtyDetails, Notes) SELECT (" _
& forms!frmRequoteStep!rfq _
& ", " & intRFQID & ", RFQDetail.Customer, " _
& "RFQDetail.[PN#], RFQDetail.Description, " _
& "RFQDetail.[Qty Required], " _
& "RFQDetail.Requote, RFQDetail.QtyDetails, RFQDetail.Notes " _
& "FROM RFQDetail where (((RFQDetail.RFQID)= " _
& [forms]![frmRequoteStep]![COmbo5]) _
& " AND ((RFQDetail.Requote)=True))"
DoCmd.RunSQL strSQL[/tt]

However, the above is very rough indeed as I do not know what your tables look like. it is only intended as an idea of what you can do.