Copy a partial record.

[mugman17]

Hi there:

I would like to duplicate a record. However, I dont want everything. There are a few fields that I would like left blank in the new record. So is there a way to copy certain fields of a record? Obviously, the wizard just allows me to copy over the whole record. Thanks.

 

[HiTechCoach]

I would use an Append query that only copies the fields that you want.

 

[missinglinq]

Or, if you're not comfortable working with queries, you could just add a line or lines to the Wizard generated code to reset the values of the fields you don't want copied:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Me.Textbox1 = Null
Me.Textbox2 = Null
Me.Textbox3 = Null

People say the Wizard generated code is going to disappear, but they've been saying that for years, and, in pint of fact, Microsoft couldn't do that, for backward compatibility reasons.

BTW, personally I'd go with the Coach's suggestion, but that's just me!

 

[james_IT]

Or, if you're not comfortable working with queries, you could just add a line or lines to the Wizard generated code to reset the values of the fields you don't want copied:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Me.Textbox1 = Null
Me.Textbox2 = Null
Me.Textbox3 = Null

People say the Wizard generated code is going to disappear, but they've been saying that for years, and, in pint of fact, Microsoft couldn't do that, for backward compatibility reasons.

BTW, personally I'd go with the Coach's suggestion, but that's just me!

Hi

I have a similar requirement - I have a stock item that I want to duplicate (all but a few fields) because i want to enter a new item of stock which shares a few similar attributes without entering everything again.

I have tried the above code but it says I am trying to enter a record with duplicate values in the index even though ive written

Me.StockNumber = Null

Any ideas?

 

[missinglinq]

Where does you StockNumber come from? Is it input by the user? Does it come from code? Is it an AutoNumber?

 

[pbaldy]

Duplicate post:

http://www.access-programmers.co.uk/forums/showthread.php?t=185144

 

[james_IT]

Where does you StockNumber come from? Is it input by the user? Does it come from code? Is it an AutoNumber?

Its input by the user (not an autonumber). Typical value might be N11611/4

 

[missinglinq]

Then my guess is that you might be trying to do just that, which is to say you may already have a record where the StockNumber field is Null! I'd go to the table and quickly scan down to see if that field is empty in one of your records.

 

[james_IT]

Then my guess is that you might be trying to do just that, which is to say you may already have a record where the StockNumber field is Null! I'd go to the table and quickly scan down to see if that field is empty in one of your records.

Ye, i assumed that also - checked the table but all have values in!

Really dont understand why it wont work.

Any more ideas?

 

[HiTechCoach]

Ye, i assumed that also - checked the table but all have values in!

Really dont understand why it wont work.

Any more ideas?

I suggest my original recommendation.

I Normally handle this with an Append query. It really is very simple and just a few lines of VBA code!

I would first ask for the StockNumber. The use an append query to copy the record and replace the StockNumber with the one that the user input.

Here is a simple example using the Customer table in Northwind

strNewCustNum  = InputBox("Enter New Customer Number")


StrSQL = "INSERT INTO Customers ( CustomerID, CompanyName, ContactName, ContactTitle )"

strSQL = strSQL &  " SELECT """ & strNewCustNum & """ AS CustomerID, Customers.CompanyName, Customers.ContactName, Customers.ContactTitle "

strSQL = strSQL &  "FROM Customers WHERE (((Customers.CustomerID)=""" & Me.CustomerID  &  """));"

CurrentDB.Execute strSQL