Capturing New Record Number

[Jeff Garrison]

All -

I have a situation where a user clicks on a button to copy the current
record to a new one. My question is...is there a way to get the new record
number and display it with a message box (i.e. "The new record number is X")
while still being on the record that was copied?

Thanks.

JeffG

 

[Dirk Goldgar]

All -

I have a situation where a user clicks on a button to copy the current
record to a new one. My question is...is there a way to get the new
record number and display it with a message box (i.e. "The new record
number is X") while still being on the record that was copied?

What code is being used to copy the record? The way you'd capture the new
"record number" depends on the way you create the record. I'm assuming, by
the way, that by "record number" you mean the value of an autonumber field.
If that's not the case, please explain exactly what you mean.

 

[Jeff Garrison]

It's being done through a macro containing a query.

You are correct, the number I want to capture is the new record using the
Auto Number field.

 

[Dirk Goldgar]

It's being done through a macro containing a query.

You are correct, the number I want to capture is the new record using the
Auto Number field.

If it's a macro, please list the actions that make up the macro; if it's
VBA code (which some people think of as a macro), please post the code. If
it's a true macro, you'll probably have to replace the macro with VBA code,
but that code is not likely to be complicated.

What's the SQL of the query that gets executed?

 

[Jeff Garrison]

Here's the SQL of the query that gets executed. This is query #3 of a 3
part process...queries 1 and 2 delete and append to a temp table to add some
additional data needed by the main table...

INSERT INTO tblProjectMain ( prjProjectName, prjDynamicsID,
prjDynamicsDescription, prjStatus, prjResponseType, prjAddDate,
prjCloseDate, prjCustomer, prjDynamicsSiteName, prjAddress1, prjAddress2,
prjCity, prjState, prjZipCode, prjLocation, prjNumberSites, prjDescription,
prjProjectManager, prjNotesInternal, prjNotesExternal, prjEndUser,
prjEndUserEMail, prjEndUserPhone, prjEndUserPhoneExt, prjPMDell,
prjSalesDell, prjBillType, prjBeginDate, prjBeginTime, prjEndDate,
prjNumSubContractors, prjSpecialRequests, prjSubContractorRate,
prjProjectEscalationDate, prjSegment, prjVertical, prjContractAmount,
prjEstimatedRevenue, prjEstimatedCost, prjEstimatedProfit, prjPostedCosts,
prjActualRevenue, prjBilledAmount, prjQuantity, prjComplete, prjDivision,
prjSub5000, prjTaxSchedule, prjDynamicsEntered, prjDynamicsEnteredDate,
prjProjectClosed, prjSentToRecruiting, prjRunRate, prjProjectDistribution,
prjExpenseLabor, prjExpenseTruckRental, prjExpenseMisc,
prjExpenseSubcontractors, prjExpenseMeals, prjExpenseAirTravel,
prjExpenseRentalMileage, prjExpenseLodging, prjExpenseFuel,
prjDMVCheckNeeded, prjLeadTechNeeded, prjCrossoverNeeded,
prjIntelligentClassroom, prjLinkToProjectMaster, prjMaster )
SELECT tmptblProjectCopy.prjProjectName, tmptblProjectCopy.prjDynamicsID,
tmptblProjectCopy.prjDynamicsDescription, tmptblProjectCopy.prjStatus,
tmptblProjectCopy.prjResponseType, tmptblProjectCopy.prjAddDate,
tmptblProjectCopy.prjCloseDate, tmptblProjectCopy.prjCustomer,
tmptblProjectCopy.prjDynamicsSiteName, tmptblProjectCopy.prjAddress1,
tmptblProjectCopy.prjAddress2, tmptblProjectCopy.prjCity,
tmptblProjectCopy.prjState, tmptblProjectCopy.prjZipCode,
tmptblProjectCopy.prjLocation, tmptblProjectCopy.prjNumberSites,
tmptblProjectCopy.prjDescription, tmptblProjectCopy.prjProjectManager,
tmptblProjectCopy.prjNotesInternal, tmptblProjectCopy.prjNotesExternal,
tmptblProjectCopy.prjEndUser, tmptblProjectCopy.prjEndUserEMail,
tmptblProjectCopy.prjEndUserPhone, tmptblProjectCopy.prjEndUserPhoneExt,
tmptblProjectCopy.prjPMDell, tmptblProjectCopy.prjSalesDell,
tmptblProjectCopy.prjBillType, tmptblProjectCopy.prjBeginDate,
tmptblProjectCopy.prjBeginTime, tmptblProjectCopy.prjEndDate,
tmptblProjectCopy.prjNumSubContractors,
tmptblProjectCopy.prjSpecialRequests,
tmptblProjectCopy.prjSubContractorRate,
tmptblProjectCopy.prjProjectEscalationDate, tmptblProjectCopy.prjSegment,
tmptblProjectCopy.prjVertical, tmptblProjectCopy.prjContractAmount,
tmptblProjectCopy.prjEstimatedRevenue, tmptblProjectCopy.prjEstimatedCost,
tmptblProjectCopy.prjEstimatedProfit, tmptblProjectCopy.prjPostedCosts,
tmptblProjectCopy.prjActualRevenue, tmptblProjectCopy.prjBilledAmount,
tmptblProjectCopy.prjQuantity, tmptblProjectCopy.prjComplete,
tmptblProjectCopy.prjDivision, tmptblProjectCopy.prjSub5000,
tmptblProjectCopy.prjTaxSchedule, tmptblProjectCopy.prjDynamicsEntered,
tmptblProjectCopy.prjDynamicsEnteredDate,
tmptblProjectCopy.prjProjectClosed, tmptblProjectCopy.prjSentToRecruiting,
tmptblProjectCopy.prjRunRate, tmptblProjectCopy.prjProjectDistribution,
tmptblProjectCopy.prjExpenseLabor, tmptblProjectCopy.prjExpenseTruckRental,
tmptblProjectCopy.prjExpenseMisc,
tmptblProjectCopy.prjExpenseSubcontractors,
tmptblProjectCopy.prjExpenseMeals, tmptblProjectCopy.prjExpenseAirTravel,
tmptblProjectCopy.prjExpenseRentalMileage,
tmptblProjectCopy.prjExpenseLodging, tmptblProjectCopy.prjExpenseFuel,
tmptblProjectCopy.prjDMVCheckNeeded, tmptblProjectCopy.prjLeadTechNeeded,
tmptblProjectCopy.prjCrossoverNeeded,
tmptblProjectCopy.prjIntelligentClassroom,
tmptblProjectCopy.prjLinkToProjectMaster, tmptblProjectCopy.prjMaster
FROM tmptblProjectCopy;

At this point, the record number gets assigned (it's in a SQL table vs an
Access table).