473,231 Members | 1,750 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,231 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 2171
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: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.