Hello All,
I am trying to code a command button on a form to create a duplicate record and renumber it. The code I have in the command button currently is; DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
NumberNewRecord
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
The NumberNewRecord sub is as follows: [Prospect Number] = DMax("[prospect number]", "PR Only Info", "[list name] = [Forms]![PR Only Info]![cbListFilter]") + 1
If IsNull([Prospect Number]) Then [Prospect Number] = 1[List Name] = cbListFilter.Va lue
What currently happens with this code is that it adds a new record on form and appears to show the duplicate data in the controls. However, the numbering does not appear to be working. Also, when I move off the new record and then move back, the record still exists, but is empty. Any ideas?
Thanks,
Josh
6 1651 NeoPa 32,568
Recognized Expert Moderator MVP
Not many experts use macros :(.
We all use VBA code as it's better in almost every way.
It's particularly more readable (although code tags help too).
If you were to redo using VBA I'm sure you'd get more help.
If not, just leave it here and hope that someone can help. Good luck whichever.
This code is in VBA. The first part of the code was generated by the command button wizard. If there is a better way to code it without using the docmd method, please advise.
NeoPa 32,568
Recognized Expert Moderator MVP
Yes, you're right.
I was confusing calling menu items with macros. :embarrassed:
This is, nevertheless very difficult to read and understand without trawling through the documentation (typical wizard created code :().
Unfortunately I can't advise on alternative code as I have no idea what it's trying to do.
For that same reason I'm sorry I can't help you with this question.
I tried using the Wizard generated code to copy a record, like yourself, found it to be too full of problems, and finally "rolled" my own. Basically, I copy the record's values I want carried forward to the new record into variables, goto a new record, then assign these variables to the appropriate controls on the new record. For controls that you want to change, you can leave blank, fill with a message telling the user to fill them in, or, as in your case, fill in a new value. Not sure exactly what you mean by "re-numbering" the record, but if it involves incrementing a custom ID number number, yo could use something like DMAX + 1. - 'Copy fields from original record to variables
-
NewField1 = Me.YourField1
-
NewField2 = Me.YourField2
-
NewField3 = Me.YourField3
-
-
'Go to a new record
-
DoCmd.GoToRecord , , acNewRec
-
-
'Plug in old values into new record
-
Me.YourField1.Value = NewField1
-
Me.YourField2.Value = NewField2
-
Me.YourField3.Value = NewField3
-
-
'Set the fields your user wants to change to blanks (not really necessary)
-
Me.YourField4.Value = ""
-
Me.YourField5.Value = ""
-
Me.YourField6.Value = ""
-
-
If any of these fields to be changed is marked as Required in their table's Design Grid, leaving them blank will throw an error, so you'll need to change these to something like this:
-
-
Me.YourField4.Value = "Enter New Value for YourField4 "
-
Me.YourField5.Value = "Enter New Value for YourField5 "
-
Me.YourField6.Value = "Enter New Value for YourField6 "
-
As my signature insinuates, this is only one of several ways to do the job! Good luck!
This solution had occured to me, however this form is huge (over 100 controls), so writing the code to copy each control's value would be tedius and inefficient. I was hoping that there was a way to copy the whole record and then just change the two fields that I needed to. Is there a way maybe with DAO? Sorry, I am new to database programming.
This is the solution I came up with - seems to be working alright. Basically, it gets the primary key of the active record and opens a DAO recordset with only this record and the values of all of the fields I want to keep are copied into an array. A new record is then added to this record set. The fields are then populated with the data in the array. The fields I want to change[List Name] is then entered. The field [Prospect Number] is generated by my ReNumber sub and the form is requeried. Does anyone see any potential issues here?
strList = InputBox("Enter List Name")
Dim db As DAO.Database
Dim IQrst As DAO.Recordset
Dim IQstrSQL As String
Dim i As Integer
Dim lngMaster
lngMaster = [ID]
Dim NumFields As Long
IQstrSQL = "Select * FROM [PR Only Info] WHERE [ID] = " & CStr(lngMaster)
Set db = CurrentDb
Set IQrst = db.OpenRecordse t(IQstrSQL, dbOpenDynaset)
NumFields = IQrst.Fields.Co unt - 1
Dim TempArray()
ReDim TempArray(3 To NumFields)
For i = 3 To NumFields
TempArray(i) = IQrst.Fields(i) .Value
Next i
With IQrst
.AddNew
.Update
.MoveFirst
Do
If IQrst![ID] <> lngMaster Then
.Edit
For i = 3 To NumFields
.Fields(i).Valu e = TempArray(i)
Next i
IQrst![List Name] = strList
.Update
End If
.MoveNext
Loop Until .EOF
.Close
End With
Set IQrst = Nothing
Set db = Nothing
ReNumber strList
Requery
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: pwys |
last post by:
Hello to everyone
WOndering if there anyone could help me with this.
I have a Primary & a secondary table with the unique key (InvoiceNo)
Waht i want is to make duplicate copy of a selected invoice no (both Primary
& secondary) into a new invoice no.
can some one help me in this?
|
by: Paolo |
last post by:
I know I should not be doing this, but I find it very useful. I have a
database in Access which stores data for a small company. Sometimes we
need to add similar information to different tables. Currently I am
already doing something similar by copying certain records into the
same table. The only thing that changes is one field.
Please look at the code:
Dim MyDb As DAO.Database, MyRs As DAO.Recordset
Dim strCode As String
|
by: Robin S. |
last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope
I've done a better job of explaining myself below.
I am trying to produce a form to add products to a table (new products).
Tables:
tblCategoryDetails
CategoryID
SpecID
|
by: Josetta |
last post by:
I have found a wealth of information here on how to duplicate records
in forms with subforms.
I have adapted code found here to work with my forms. It works
beautifully the first time I hit the "duplicate" button. It copies the
main form data, the subform data and moves to the new record. Great!
Here's the problem...if I move to another record (or stay on the newly
created record, for that matter), and hit the Duplicate button...
|
by: mavmavv |
last post by:
I have a Form where I have created a duplicate record button, no
problem...
The subform is where my problem lies. The subform displays data
matching the mainform's ID, these two values are linked. The subform
has no primary key, since there are multiple matching entries.
Basically the mainform displays Customer info + order totals, and the
subform displays the products.
When I goto duplicate the record, the ID from the duplicated...
| |
by: samdev |
last post by:
I have used the wizard to create a Duplicate Record command button on a
form. It works fine for a single duplicate but if I want to duplicate
an additional record I receive an error:
"The command or action 'Paste Append' is not available right now."
I have to close out of the form and reopen to get the button to work
again. A bit of a nuisance.
Any suggestions.
|
by: Kanashii |
last post by:
First off - I want to thank all who respond / try to help out and even those who took a moment to read over my question.
Please be patient as I am -very- new to VB in general and unfortunately I've only had the chance to pick up pieces here-and-there. So keep that in mind when reading my inquiry as I am liable to misuse terminology.
My question, although lengthy, is likely quite basic but I can't seem to find a solution and yes I have...
|
by: kkiger |
last post by:
My problem is this, I can set up a command button to duplicate a record everytime I click on it. What happens is that I get a record but it will be at the end of the table. For example, if I am on record 5 of 10 records and I click on my button I get a new record as number 11, I want the record to be number 6, inserted after the record I click on. I need to have the records in order because the next step is to generate a new number for the...
|
by: Barno77 |
last post by:
Everytime I edit a record, and hit update I get duplicating lines in my Details View. Here's my If statement:
If (Len(e.OldValues("AutoNumber")) > 0) Then
sql = "UPDATE SHIP_LOG SET FileNumber='" & e.NewValues("FileNumber") & "',BOL='" & e.NewValues("BOL") & "',Container='" & e.NewValues("Container") & "',DESTN='" & e.NewValues("DESTN") & "', Comments='" & e.NewValues("Comments") & "' WHERE POD_COMINV='" & e.Keys("POD_COMINV") &...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |