Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Saving a record with a new primary key reference

Status
Not open for further replies.
bxgti4x4

bxgti4x4

Technical User
Feb 22, 2002
167
GB
I have a table in which the primary key is "Report Number". This is a numeric field which is completed as the original data is entered. At a later stage, the original data is recovered and used in a new record with a new "Report Number" into which additional data is entered. At present, the original record is recovered, printed and then the original data is re-entered in a new record, together with the additional data, and given a new "Report Number".

Is there any way that I can use a "Save As" principle, as in Excel, to copy the record and allocate a new "Report Number". This will save a lot of time and reduce the risk of data entry errors.
 
Sort by date Sort by votes
Hi

You mean, leaving the original record behind, jsut as save as does?

Look at INSERT INTO ... SELECT ... in help, it may be the means to achive what you are trying to do

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Ken,

Thanks for that tip, it looks like what I need. However, a couple of questions:
1 If the record I wish to "save as" is already open, in a form, how do I specify the values from that record in my SQL statement? I have a feeling that "and me" comes into it somewhere.
2 Is there any way that I can get the SQL statement to request the "Report Number" before the record is appended?

Best Regards
John
 
Upvote 0 Downvote
Hi

If the record is open in another form, you can refer to fields on the form using the FORMS!MyForm!MyColumn syntax

You do realise you can build SQL in code on the fly, and include derived values something like

strNewReport = "0002Q"

StrSQL = "INSERT INTO ..etc SELECT '" & strNewReport & "' AS ReportNo, ReportName FROM tblReports...etc"

docmd.runsql strsql

OK?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Ken

Thanks. As far as building SQL on the fly is concerned, I have to make this work from a command Button so that anyone can use it. Hence my requirement for getting the SQL statement to request the "Report Number"

Best Regards
John
 
Upvote 0 Downvote
Ken,

I decided to try and run this duplication from a command button on the opening menu but I seem to have come unstuck with the code. I tried to use the ideas that you gave me but I am missing something somewhere. I would be grateful if you could you take a look through the code and see if you can see where the mistakes are. I suspect some of the errors are to do with ' and " as I am not exactly sure how they work.

I put the WHERE statement at the end in order to select the Report that I need to duplicate.

Once we can get this right, the final stage would be to have the code ask for the new report number - if you see what I mean.

Code: 
Private Sub Duplicate_Record_Click()
On Error GoTo Err_Duplicate_Record_Click

strNewReport = "050110"

StrSQL = "INSERT INTO Aims ( REPORT, VESSEL, CARGO, BOLNSV, BOLGSV, BOLTCV, BOLMT, BOLGRAV, OBQGSV, OBQFW, OBQNLIQ, GSVAFTL, FWAFTL, DATEARR, DATESAIL, VCFTABBOL, VCFTABVL, LOADPORT, TERMINAL, INSPCO, VEF, OUTNSV, OUTGSV, OUTTCV, OUTMT, OUTGRAV, ROBGSV, ROBFW, ROBNLIQ, GSVBDIS, FWBDIS, DATEBER, DATELFT, VCUOUT, VCFVL, DISPNAME, TERMNAME, INSCO, SUPERCARGO, SURVNO, MPLN, MPDN, [BOLMT(AIR)], [OUTMT(AIR)] )"
SELECT Aims.REPORT, Aims.VESSEL, Aims.CARGO, Aims.BOLNSV, Aims.BOLGSV, Aims.BOLTCV, Aims.BOLMT, Aims.BOLGRAV, Aims.OBQGSV, Aims.OBQFW, Aims.OBQNLIQ, Aims.GSVAFTL, Aims.FWAFTL, Aims.DATEARR, Aims.DATESAIL, Aims.VCFTABBOL, Aims.VCFTABVL, Aims.LOADPORT, Aims.TERMINAL, Aims.INSPCO, Aims.VEF, Aims.OUTNSV, Aims.OUTGSV, Aims.OUTTCV, Aims.OUTMT, Aims.OUTGRAV, Aims.ROBGSV, Aims.ROBFW, Aims.ROBNLIQ, Aims.GSVBDIS, Aims.FWBDIS, Aims.DATEBER, Aims.DATELFT, Aims.VCUOUT, Aims.VCFVL, Aims.DISPNAME, Aims.TERMNAME, Aims.INSCO, Aims.SUPERCARGO, Aims.SURVNO, Aims.MPLN, Aims.MPDN, Aims.[BOLMT(AIR)], Aims.[OUTMT(AIR)]
FROM Aims '"
WHERE ((Aims.REPORT) = [enter report])
'"&strNewReport'" & AS Report
DoCmd.RunSQL StrSQL
   

Exit_Duplicate_Record_Click:
    Exit Sub

Err_Duplicate_Record_Click:
    MsgBox Err.Description
    Resume Exit_Duplicate_Record_Click
    
End Sub

Best regards
John
 
Upvote 0 Downvote
Hi

Private Sub Duplicate_Record_Click()
On Error GoTo Err_Duplicate_Record_Click

strNewReport = "050110"

StrSQL = "INSERT INTO Aims ( REPORT, VESSEL, CARGO, BOLNSV, BOLGSV, BOLTCV, BOLMT, BOLGRAV, OBQGSV, OBQFW, OBQNLIQ, GSVAFTL, FWAFTL, DATEARR, DATESAIL, VCFTABBOL, VCFTABVL, LOADPORT, TERMINAL, INSPCO, VEF, OUTNSV, OUTGSV, OUTTCV, OUTMT, OUTGRAV, ROBGSV, ROBFW, ROBNLIQ, GSVBDIS, FWBDIS, DATEBER, DATELFT, VCUOUT, VCFVL, DISPNAME, TERMNAME, INSCO, SUPERCARGO, SURVNO, MPLN, MPDN, [BOLMT(AIR)], [OUTMT(AIR)] )
SELECT '" & strNewReport & "' As REPORT, Aims.VESSEL, Aims.CARGO, Aims.BOLNSV, Aims.BOLGSV, Aims.BOLTCV, Aims.BOLMT, Aims.BOLGRAV, Aims.OBQGSV, Aims.OBQFW, Aims.OBQNLIQ, Aims.GSVAFTL, Aims.FWAFTL, Aims.DATEARR, Aims.DATESAIL, Aims.VCFTABBOL, Aims.VCFTABVL, Aims.LOADPORT, Aims.TERMINAL, Aims.INSPCO, Aims.VEF, Aims.OUTNSV, Aims.OUTGSV, Aims.OUTTCV, Aims.OUTMT, Aims.OUTGRAV, Aims.ROBGSV, Aims.ROBFW, Aims.ROBNLIQ, Aims.GSVBDIS, Aims.FWBDIS, Aims.DATEBER, Aims.DATELFT, Aims.VCUOUT, Aims.VCFVL, Aims.DISPNAME, Aims.TERMNAME, Aims.INSCO, Aims.SUPERCARGO, Aims.SURVNO, Aims.MPLN, Aims.MPDN, Aims.[BOLMT(AIR)], Aims.[OUTMT(AIR)]
FROM Aims WHERE ((Aims.REPORT) = [enter report])"

DoCmd.RunSQL StrSQL


Exit_Duplicate_Record_Click:
Exit Sub

Err_Duplicate_Record_Click:
MsgBox Err.Description
Resume Exit_Duplicate_Record_Click

End Sub




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Ken,

Thanks for revising the code but, unfortunately, it still does not work. When I try to run it, the SELECT and FROM statments are highlighted and a message box appears saying "Compile Error" and "Syntax Error".

I would be grateful if you could check it out for me once more.

Best Regards
John
 
Upvote 0 Downvote
Hi

if you are getting a syntax error you (probably) have ' or " in the wrong place.

" bounds the string(s)

' bounds string values within the SQL

Because the posts here are limited in width it is sometimes difficult to read lines of code, if you cut and paste the code into an email and send it to kenneth.reayATtalk21.com (replace the AT with @) I will try to make time to look at it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

raywood1
  • Locked
  • Question
Reducing a Massive Linked Table
Replies
4
Views
555
dhookom
dhookom
JayRo71
  • Locked
  • Question
Saved Imoorts problem with file name
Replies
10
Views
570
Andrzejek
Andrzejek
ameedoo3000
  • Locked
  • Question
Send a message to an email
Replies
2
Views
3K
ameedoo3000
ameedoo3000
Anthony343242
  • Locked
  • Question
MS ACCESS UNION ALL with Attachment data type 1
2
Replies
29
Views
5K
Anthony343242
Anthony343242
TrekBiker
  • Locked
  • Question
Field size limit Access 365
Replies
5
Views
3K
TrekBiker
TrekBiker

Part and Inventory Search

Sponsor

Back
Top