473,324 Members | 2,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Duplicate Record with Associated records

6
MS Acc 2003, XP

Thank you in advance for any help.

I have tables called "Makeup" and "Lines". Each makeup can have multiple lines.

Goal is to create a new "makeup" with identical "lines" except with a new makeup id.

I created a form Makeup Copy with Line Subform. In the main form there is ID (key field), Comments, Customer, Description plus an unbound field "NewMakeup". Each line of the subform has the fields "Makeup (primary key), Line (primary key), Product, mm"

I added a duplicate button with code below, having the intention of duplicating the Makeup record with the NewMakeup replacing ID.

Append query has

Field: Makeup
Append to: Forms!Makeup Copy.Tag

then the line, product, and mm columns as usual

with the final column having
Field: NewMakeup: CLng(Forms![Makeup Copy]!Makeup)
Append to: Line

And here is the VB for the "duplicate" button:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnDuplicate_Click()
  2.  
  3. Dim dbs As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim F As Form
  6.  
  7. 'return database variable pointing to current database
  8.  
  9. Set dbs = CurrentDb
  10. Set rst = Me.RecordsetClone
  11.  
  12. 'tag property to be used later by the append query
  13.  
  14. Me.Tag = Me![NewMakeup]
  15.  
  16. 'add new record to end of Recordset object
  17.  
  18. With rst
  19.     .AddNew
  20.     !ID = Me!NewMakeup
  21.     !Comments = Me!Comments
  22.     !Customer = Me!Customer
  23.     !Description = Me!Description
  24.     .Update                     'save changes
  25.     .Move 0, .LastModified
  26. End With
  27. Me.Bookmark = rst.Bookmark
  28.  
  29. 'duplicate all the associated lines
  30.  
  31. DoCmd.SetWarnings False
  32. DoCmd.OpenQuery "cpyMakeup"
  33. DoCmd.SetWarnings True
  34.  
  35. 'requery the subform to display the newly appended records
  36.  
  37. Me![Lines Subform].Requery
  38.  
  39. End Sub
  40.  

Results of clicking button:

Duplicate Output Destination 'Line'.

debugger then highlights this line:

DoCmd.OpenQuery "cpyMakeup"
Jun 17 '08 #1
1 2181
VinArt
6
I'm pretty sure the problem is with the query. I've made several attempted debugger always flags the line in the VB code for the button that points to the query.

Current status:

Field: Makeup
Append to: Forms!Makeup Copy!Tag

(I changed the Tag to equal the old ID rather than the new one)

and

Field: New: Cling(forms![Makeup Copy]!ID)
Append to: NewMakeup

Data type mismatch in criteria expression.
Jun 18 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Giloosh | last post by:
Hello, i need some help if possible... i have a payments table with over 500 records i want to run a query that searches through the table spotting out any duplicate ID#'s and Dates. So basically...
1
by: Robert | last post by:
How can I query an existing table and update a field in each record in the table with the occurrence count of each record e.g. update field to 1 (= first record occurrence), update field to 2 for...
2
by: Jason | last post by:
I have a table of 650,000 records associated to contracts. Some of the contracts have multiple records but each contract only has one Active Record (there might be several inactive records). ...
8
by: Mark | last post by:
When my form goes to a new record, I have a procedure that copies the last record added to the form's underlying table into the form. The intent is that a series of new records may have the same...
3
by: Nhmiller | last post by:
I searched here for an answer. I am entering records into a database, and it would save a lot of time if I could duplicate a record that is very similar to the new one I am about to enter, then...
2
by: stranger | last post by:
My database is set up so people can input parts orders. Sometimes they order the same parts on a monthly basis. I want to be able to duplicate past parts orders and have it pasted in with a new...
0
by: claus | last post by:
Hi, I am not a programmer but tries anyway to program a feature in a form where I am able to copy entries for the form and a subform. I have tried follow the guide here...
4
by: Thomas Arthur Seidel | last post by:
Hello to all, I have a small or big problem with a customer data base, where during a change of system we might have created duplicate records. This should be easy to find, you might think, but,...
1
by: xraive | last post by:
I have a problem with this. Currently I am trying Allen's code and i am not successful. Current Design Table1 (Main Form) TravelID (PK) ApprovedBY EntreredBy BudgetCode ExpenseCode
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.