 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2007Duplicate a record with some changes
I have a database (Access 2000) that allows me to add compliance issues. Up until now it has been 1 issue to 1 Business Unit. I now need to be able to create a new record with most of the data the same, but in the new record I need to select a different Business Unit and a different Business Unit Contact for the new issue. The Business Unit and Business Unit Contacts are each in their own tables named accordingly. The issues are in a table named tbl_Issues. I would like the users to not have to reenter all of the data, so I am looking for a solution. I was thinking of using the Control Wizard to create a "Copy" button, but I need to be able to modify it to blank out the two fields and require the user to select new values from the tables. Does this sound doable or do I need to do this with all VB code moving data field by field? Could someone suggest the best approach and the code to make it work. I am very new to VB. This is the code supplied by the Wizard. Private Sub Add_additional_Business_Unit_Click() On Error GoTo Err_Add_additional_Business_Unit_Click DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append Exit_Add_additional_Business_Unit_Click: Exit Sub Err_Add_additional_Business_Unit_Click: MsgBox Err.Description Resume Exit_Add_additional_Business_Unit_Click Thanks  SignatureMikeA A better solution would be to create the new record in a different way. Set the Bookmark of the form's RecordsetClone to the current record. Dim rs As DAO.Recordset Set rs = Me.RecordsetClone rs.Bookmark = Me.Bookmark Then move to a new record: RunCommand acCmdRecordsGotoNew Then copy the fields you want from the clone set into the new record: Me.[SomeField] = rs![SomeField] Me.[AnotherField] = rs![AnotherField] 'etc The new record is not saved until the user completes the extra fields. (Use Form_BeforeUpdate if you need to check that programmatically.) SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >I have a database (Access 2000) that allows me to add compliance issues. Up > until now it has been 1 issue to 1 Business Unit. I now need to be able to [quoted text clipped - 36 lines] > > Thanks Another solution could be to write the values to the Tag of the designated fields and then when you go to a new record read the Tags from the controls with a loop and fill in the designated fields. That way you can decide which fields should be copied... Maurice > A better solution would be to create the new record in a different way. > [quoted text clipped - 54 lines] > > > > Thanks Thanks Allen Where do I put this code? As a procedure in the button I was playing with? How do i trigger the action? thanks SignatureMikeA > A better solution would be to create the new record in a different way. > [quoted text clipped - 54 lines] > > > > Thanks Allen, I tried using the code in the button and either i am doing it wrong or this is not the place to do it. I am getting a compile error that reads "User-defined type not defined. The Help is talking about Type...End Type statements. Is there something else I need to add here? I am a real new to VB so I am at a loss as to where to go with this. thanks mike SignatureMikeA > Thanks Allen > [quoted text clipped - 61 lines] > > > > > > Thanks Is it complaining about the line of code Dim rs As DAO.Recordset If so, while in the VB Editor, select Tools | References from the menu bar, scroll through the list of available references until you find the entry for Microsoft DAO 3.6 Object Library, select it (by checking the box to the left of it), then click on OK to close the dialog. The problem you're encountering is despite the fact that DAO is the preferred method to communicate with Jet databases (i.e. MDB or MDE files), Microsoft decided to remove the reference as a default in Access 2000 and 2002. (They corrected this oversight in Access 2003) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Allen, > [quoted text clipped - 87 lines] >> > > >> > > Thanks Yes. Set the button's On Click property to: [Event Procedure] Then click the Build button (...) beside the property. Access opens the code window, showing the code you originally posted. Replace these 3 lines with your new code: DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 Then check that Access understands your code by choosing Compile in the Debug menu. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Allen, > [quoted text clipped - 85 lines] >> > > MsgBox Err.Description >> > > Resume Exit_Add_additional_Business_Unit_Click Thank you both. I'm a big step closer, but now I'm getting an error message that I have not been able to figure out. Maybe you can point me in the right direction. The error message is 'Item not found in this collection", but it does not say what item. Below are the fields I am moving. I get a new record and the top 4 fields are populated in the new record, but not the last 4. I have change the order of the last 4 records and run the code with the same results. I have commented them out one at a time to see if i could identify the problem but that didn't make a difference either. Me.[ReviewName] = rs![ReviewName] -- Text field Me.[IssueName] = rs![IssueName] -- Test field Me.[RegulationID] = rs![RegulationID] -- This is a # field that does a lookup to a table named "Regulation". Me.["Opened Date"] = rs!["Opened Date"] -- Date field 'Me.[Comment] = rs![Comment] -- Memo field 'Me.["Repeat Issue"] = rs!["Repeat Issue"] -- Yes/No box 'Me.["Opened By"] = rs!["Opened By"] -- This is a # field that does a lookup to a table names "Contacts". 'Me.["Assigned To"] = rs!["Assigned To"] -- This goes to the same table in the same way as the last field. This is the code for the lookup. SELECT [Contacts].[ID], [Contacts].[First Name] & " " & [Contacts].[Last Name] AS Expr1 FROM Contacts ORDER BY [Contacts].[Last Name]; Any ideas? thanks Mike SignatureMikeA > Yes. Set the button's On Click property to: > [Event Procedure] [quoted text clipped - 100 lines] > >> > > MsgBox Err.Description > >> > > Resume Exit_Add_additional_Business_Unit_Click 1. What's with the quotes, e.g.: Me.["Opened Date"] That doesn't look right. 2. Does the code compile? (Compile in Debug menu.) 3. If it runs, which line gives the error? SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Thank you both. I'm a big step closer, but now I'm getting an error > message [quoted text clipped - 149 lines] >> >> > > MsgBox Err.Description >> >> > > Resume Exit_Add_additional_Business_Unit_Click Allen, It was my understanding (probably incorrectly) that if the field name contained a blank [Opened Date] that you had to group it by using quotes ("") or brackes (). I take it that this is not needed. I do not get a compile error, just a small window that pops up in front of the form with that message. If I click OK, I can manually enter the rest of the data. Mike SignatureMikeA > 1. What's with the quotes, e.g.: > Me.["Opened Date"] [quoted text clipped - 157 lines] > >> >> > > MsgBox Err.Description > >> >> > > Resume Exit_Add_additional_Business_Unit_Click Let us know if removing the quotes solves the problem. (The square brackets do the job.) SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Allen, > [quoted text clipped - 189 lines] >> >> >> > > MsgBox Err.Description >> >> >> > > Resume Exit_Add_additional_Business_Unit_Click That did the trick. Thanks for the help and the education. Mike SignatureMikeA > Let us know if removing the quotes solves the problem. > (The square brackets do the job.) [quoted text clipped - 192 lines] > >> >> >> > > MsgBox Err.Description > >> >> >> > > Resume Exit_Add_additional_Business_Unit_Click That did the trick. Thanks for the help and the education. Mike SignatureMikeA > Let us know if removing the quotes solves the problem. > (The square brackets do the job.) [quoted text clipped - 192 lines] > >> >> >> > > MsgBox Err.Description > >> >> >> > > Resume Exit_Add_additional_Business_Unit_Click I'm hoping you can help me again. After the new record is created, I would like to have the focus on the form be set to the 1st of the 2 fields they need to add for the new record [BusinessUnitID]. This field is on the form, not a subform. It is a # field that does a lookup in a table named Regulation. How is this done? thanks Mike SignatureMikeA > That did the trick. Thanks for the help and the education. > [quoted text clipped - 196 lines] > > >> >> >> > > MsgBox Err.Description > > >> >> >> > > Resume Exit_Add_additional_Business_Unit_Click Best to start a new thread for a new question. You should be able to use SetFocus. Without testing, it would be something like this: Me.Parent.SetFocus Me.Parent!BusinessUnitID.SetFocus SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > I'm hoping you can help me again. > [quoted text clipped - 6 lines] > lookup > in a table named Regulation. Allen, I started to do that, but then though that because the entire history of my problem was here it would put the question in context if I added it here. I added the code you suggested and I am getting an error message. "The expression you entered has an invalid reference to the Parent property." I have also added a message box with an if... then on a yes/No answer. I plased the update code to run on a Yes answer. If works fine, but does it effect the placement of the new code. If I included the code after the update code in the If...then I get the error message. If I move it out of the if...ten the code runs withut error but does not set focus. Msg1 = MsgBox("Do you want to assign this issue to an additional Business Unit?", vbYesNo, "New Issue Record") If Msg1 = vbYes Then thanks SignatureMikeA > Best to start a new thread for a new question. > [quoted text clipped - 14 lines] > > lookup > > in a table named Regulation. If the form you added this code to has no Parent, then it is not a subform. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Allen, > [quoted text clipped - 36 lines] >> > lookup >> > in a table named Regulation. I'm sorry I didn't make it clear on my posting below. I realize that the field is on the form. I have been trying to use - Me.BusinessUnitID.Setfocus but I keep getting an error saying it doesn't like .Setfocus. As I type it, it wants me to use .value. I'm sure my syntax is wrong but done know what to change. I've seen other posting where they appear to be using this format, but I cannot get it to work. thanks Mike SignatureMikeA > If the form you added this code to has no Parent, then it is not a subform. > [quoted text clipped - 38 lines] > >> > lookup > >> > in a table named Regulation. Allen, I figured out my focus problem. It was a Dah! I have to fields with similar names. The one I was trying to use was the one NOT on the form. Works like a charm when you use the correct field. thanks for your help and patience. Mike SignatureMikeA > I'm sorry I didn't make it clear on my posting below. I realize that the > field is on the form. I have been trying to use - Me.BusinessUnitID.Setfocus [quoted text clipped - 49 lines] > > >> > lookup > > >> > in a table named Regulation. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.