473,320 Members | 1,978 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,320 software developers and data experts.

How can I duplicate a record to a new record without copying all fields

6
I have a button on a form that I want to use to duplicate an existing record. It worked fine until I added a calculated field to the form and a filter combobox. Now I get errors. How do I get around this? Please be patient with me. I only know how to copy, paste and modify VBA. I usually work with macros. Thanks.
Feb 3 '20 #1

✓ answered by NeoPa

SandyB:
I've checked that the table fields and bound control have different names. I ran it in debug (step into) mode. Each of the controls show the correct value. But the form displays #Name? I've compacted and repaired both the backend (tables) and frontend (forms) databases. I've unlinked and relinked them. I've checked that autocorrect was turned off. Any other ideas?
Well, will you look at that!! Reminiscent of the ending scene of Grease. Here's the newbie struggling to cope and being quiet in the background - when suddenly - out she pops in full leathers and the whole playground has eyes out of their stalks.

Really well done. You tried this, that and the other without prompting. All good ideas.

The actual problem is with my code. The .DefaultValue property is a value that has to resolve to a string. That is to say it is resolved by the system and the result used. It may be clearer if I use an example of the word Bob :
Expand|Select|Wrap|Line Numbers
  1. .Family_Title.DefaultValue = """Bob"""
This will put the string "Bob" as the value of .DefaultValue. My code (unfortunately) used the text Bob instead. When it comes to trying to use this the system tries to find what Bob is a reference to and fails.

My earlier code should have read :
Expand|Select|Wrap|Line Numbers
  1.     With Me
  2.         .Family_Title.DefaultValue = """" & DLookup("[Family Title]", "Families", "FamilyID=" & currentID) & """"
  3.         .Family_Last_Name.DefaultValue = """" & DLookup("[Family Last Name]", "Families", "FamilyID=" & currentID) & """"
  4.         .Family_City.DefaultValue = """" & DLookup("[Family City]", "Families", "FamilyID=" & currentID) & """"
  5.         .Family_State.DefaultValue = """" & DLookup("[Family State]", "Families", "FamilyID=" & currentID) & """"
  6.         .Family_Zip.DefaultValue = """" & DLookup("[Family Zip]", "Families", "FamilyID=" & currentID) & """"
  7.  
  8.         Call .Family_Title.SetFocus
  9.     End With
There is a further possible problem with such code as it's possible to have text values that include quotes in them (See Quotes (') and Double-Quotes (") - Where and When to use them). To avoid this causing you any problems you should really use SQL Injection Attack - SafeSQL or a version of it that works for the quote type you use.
Expand|Select|Wrap|Line Numbers
  1. .Family_Title.DefaultValue = """" & SafeSQL(Nz(DLookup("[Family Title]", "Families", "FamilyID=" & currentID),"")) & """"
Notice I added in the Nz() to ensure it doesn't crash when no record is found to match.

15 2081
twinnyfo
3,653 Expert Mod 2GB
Hi Sandy!

Welcome to Bytes!

We will try to be as patient as possible. Most, if not all of us, have been at a beginner's level at one time or another, so we will try to guide you as best as we can.

First, it would be helpful if you posted the code that you are using that is giving you problems. Also, identify which line of code is causing the specific problem and indicate the complete error that is being thrown.

That will have to be our starting point, as without it, we can't begin to either troubleshoot or ask more particular questions.

Hope this hepps!
Feb 3 '20 #2
SandyB
6
Thanks twinnyfo for your reply. Here's the code I'm using. I copied this from someone online. My field names have spaces. This one doesn't throw an error. It just takes me to a new record with blank fields. It worked beautifully for me in another form (after modifying fields and table names), so I assume I've just removed a delimiter or some such mistake.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Duplicate_Click()
  2.  
  3.     Dim currentID As Long
  4.  
  5.     'TO DO: change all instances of 'FamilyID' with the actual name of your table's ID or primary key
  6.  
  7.     If IsNull(FamilyID) Then
  8.         MsgBox prompt:="Please select the record to copy first.", buttons:=vbExclamation
  9.         Exit Sub
  10.     End If
  11.  
  12.     currentID = FamilyID
  13.  
  14.     'Go to a new record
  15.     DoCmd.GoToRecord record:=acNewRec
  16.  
  17.     'TO DO: set the fields to be copied (those that most likely will have the same values)
  18.     'FORMAT: fieldName = Dlookup("fieldname", "tableName", "primaryKeyField=" & currentID)
  19.  
  20.     Family_Title = DLookup("[Family Title]", "Families", "FamilyID=" & currentID)
  21.     Family_Last_Name = DLookup("[Family Last Name]", "Families", "FamilyID=" & currentID)
  22.     Family_City = DLookup("[Family City]", "Families", "FamilyID=" & currentID)
  23.     Family_State = DLookup("[Family State]", "Families", "FamilyID=" & currentID)
  24.     Family_Zip = DLookup("[Family Zip]", "Families", "FamilyID=" & currentID)
  25.  
  26.     [Family Title].SetFocus
  27.  
  28. End Sub
Feb 3 '20 #3
NeoPa
32,556 Expert Mod 16PB
Hi Sandy.

Please review Before Posting (VBA or SQL) Code. It's perfectly understandable you may not have considered any of these points but if there are any not covered then looking for other problems will be extra complicated.

My first thought is that we can't work with the code you posted as it stands because it won't even compile as far as I can see. It may be that you need help to get it to compile, but that's a whole different question (and answer) than looking into the logic.

It can also help, when the code actually compiles, to ensure we know about anything you reference and rely on in your code for the logic to work. For instance, it might be helpful to understand where familyID is defined and set. First though, get the code into a state where at least it compiles.
Feb 4 '20 #4
SandyB
6
As I said, I'm a VBA novice. Please be kind. I'm doing the best I can. If I knew what you were talking about, I would do it. If I knew how to fix it, I would have. FamilyID is the key field for the Families table. If the code is so bad, I don't understand why it worked so well for another form.
Feb 4 '20 #5
NeoPa
32,556 Expert Mod 16PB
Hi Sandy.

I'm not trying to be unkind. I'm trying to give advice that should help you. I do understand that you're not experienced and everything I've written is with the expectation you will understand and be able to follow. If there is anything that doesn't make sense then feed that back and I will try to break it down further. I'm looking to assist you and to guide you in ways that will help you. I may be an expert in Access & VBA but I'm very inexperienced at communicating with SandyB - so far ;-)

I had another look at your code to see if I could make better sense of it this time. It appears there are certain lines where the style of code used is unusual. Looking closer though, it is valid code if it refers to Controls on a Form. It's not good naming style (as you already warned of - well done) and the code style is one I'd steer clear of, but it could compile - contrary to my previous estimation.

After reading it again it turns out that all the items I assumed were undeclared variables are probably Controls on your Form instead. When I look at it in that context it certainly makes more sense. Normally we expect a poster to explain some of this so we have a better understanding of what we're looking at but you also explained earlier that you're inexperienced so it makes sense you wouldn't have known to.
Feb 4 '20 #6
NeoPa
32,556 Expert Mod 16PB
Where does that leave us?

Well, if we assume FamilyID is a Control (By the way a Control is something found on Forms & Reports that can be used to show Field data from a bound record source. Often mis-referred to as Fields themselves they are actually not.) on your Form that's bound to the Field in the record source of the same name, and further that it's numeric, then we can see that the code does make sense at a very basic level. That's fine - we're not looking for rocket science at this stage.

I can confirm that the syntax for your DLookup()s looks fine. At least visually. This is where compiling may throw up something that otherwise would be hard to spot. Let me know if you have any difficulty triggering a specific compile. It's a very handy trick that all the best VBA coders use frequently so well worth becoming familiar with.

Assuming no compile errors then the only problem I can see that may cause the Form to show no data would be if the value of Me.FamilyID doesn't match a valid value in the table. Is that possible with what you know of the design of your Form?
Feb 4 '20 #7
NeoPa
32,556 Expert Mod 16PB
On a separate note, there is an issue with the logic of the code. It's not why you posted but it may help you to understand why it's a problem and (of course) what the solution is.

Let me start by explaining that when you navigate from one record to another (on a CRUD Form - CRUD == Create; Read; Update; Delete) you start with a record that is, as yet, unchanged. In IT parlance it has a clean buffer. Once you dirty the buffer, by making any changes to data anywhere within the record, then those changes need to be either saved or discarded before moving away to another record.

Why is this important in your situation?

Your code, as it stands, dirties the buffer for the new record. IE. It changes the record. At this point, if the operator navigates away from that record for any reason, then that record will be saved as you created it. Typically, it will also prompt them to save or discard changes. For an operator that knows they haven't made any changes to the record manually this can be confusing, and it typically results in your system having a bunch of records where nobody understands how or why they were created.

To avoid this issue you can use the DefaultValue property of the Controls instead of the Value property which is what your code currently refers to by default (IE. Simply using the name of the Control in your code is interpreted by VBA to mean that Control's Value property in cases where that makes sense).

It may be easier to follow if I show you the rewritten code :
Expand|Select|Wrap|Line Numbers
  1.     With Me
  2.         .Family_Title.DefaultValue = DLookup("[Family Title]", "Families", "FamilyID=" & currentID)
  3.         .Family_Last_Name.DefaultValue = DLookup("[Family Last Name]", "Families", "FamilyID=" & currentID)
  4.         .Family_City.DefaultValue = DLookup("[Family City]", "Families", "FamilyID=" & currentID)
  5.         .Family_State.DefaultValue = DLookup("[Family State]", "Families", "FamilyID=" & currentID)
  6.         .Family_Zip.DefaultValue = DLookup("[Family Zip]", "Families", "FamilyID=" & currentID)
  7.  
  8.         Call .Family_Title.SetFocus
  9.     End With
With this approach the values are never put into the record until the operator themself makes a change. When they do all the required Controls are populated with the specified values. The operator still sees the default values even before they make any other changes, it just won't create a new record or update an existing one until they do.
Feb 4 '20 #8
twinnyfo
3,653 Expert Mod 2GB
NeoPa,

As in other times, your solution above is absolutely brilliant! I never thought of approaching copying a record in such a way.

I can use this!

Thanks!
Feb 5 '20 #9
SandyB
6
NeoPa,

Thanks, so much! That helps a lot. I didn't understand the code I copied. I couldn't figure out which was supposed to refer to controls and which were the table and field names. I think I now understand which parts are referring to controls and which to tables. I like your code much better. It's great that the user won't be editing a record, only setting default values. Once I understood what I'm working with, I renamed some of the controls and used (modified) the code you gave me. Now I have a different problem. Every control except zip code shows #Name? error on my new record. Zip code shows properly. I've checked that the table fields and bound control have different names. I ran it in debug (step into) mode. Each of the controls show the correct value. But the form displays #Name? I've compacted and repaired both the backend (tables) and frontend (forms) databases. I've unlinked and relinked them. I've checked that autocorrect was turned off. Any other ideas?
Feb 5 '20 #10
NeoPa
32,556 Expert Mod 16PB
Thanks Twinny.

Always great to be able to help anyone but other experts is better still :-)
Feb 6 '20 #11
NeoPa
32,556 Expert Mod 16PB
SandyB:
I've checked that the table fields and bound control have different names. I ran it in debug (step into) mode. Each of the controls show the correct value. But the form displays #Name? I've compacted and repaired both the backend (tables) and frontend (forms) databases. I've unlinked and relinked them. I've checked that autocorrect was turned off. Any other ideas?
Well, will you look at that!! Reminiscent of the ending scene of Grease. Here's the newbie struggling to cope and being quiet in the background - when suddenly - out she pops in full leathers and the whole playground has eyes out of their stalks.

Really well done. You tried this, that and the other without prompting. All good ideas.

The actual problem is with my code. The .DefaultValue property is a value that has to resolve to a string. That is to say it is resolved by the system and the result used. It may be clearer if I use an example of the word Bob :
Expand|Select|Wrap|Line Numbers
  1. .Family_Title.DefaultValue = """Bob"""
This will put the string "Bob" as the value of .DefaultValue. My code (unfortunately) used the text Bob instead. When it comes to trying to use this the system tries to find what Bob is a reference to and fails.

My earlier code should have read :
Expand|Select|Wrap|Line Numbers
  1.     With Me
  2.         .Family_Title.DefaultValue = """" & DLookup("[Family Title]", "Families", "FamilyID=" & currentID) & """"
  3.         .Family_Last_Name.DefaultValue = """" & DLookup("[Family Last Name]", "Families", "FamilyID=" & currentID) & """"
  4.         .Family_City.DefaultValue = """" & DLookup("[Family City]", "Families", "FamilyID=" & currentID) & """"
  5.         .Family_State.DefaultValue = """" & DLookup("[Family State]", "Families", "FamilyID=" & currentID) & """"
  6.         .Family_Zip.DefaultValue = """" & DLookup("[Family Zip]", "Families", "FamilyID=" & currentID) & """"
  7.  
  8.         Call .Family_Title.SetFocus
  9.     End With
There is a further possible problem with such code as it's possible to have text values that include quotes in them (See Quotes (') and Double-Quotes (") - Where and When to use them). To avoid this causing you any problems you should really use SQL Injection Attack - SafeSQL or a version of it that works for the quote type you use.
Expand|Select|Wrap|Line Numbers
  1. .Family_Title.DefaultValue = """" & SafeSQL(Nz(DLookup("[Family Title]", "Families", "FamilyID=" & currentID),"")) & """"
Notice I added in the Nz() to ensure it doesn't crash when no record is found to match.
Feb 6 '20 #12
SandyB
6
NeoPa,

That's exactly what I'm looking for! It works like a charm! Thank you so much for your help! I will mark that as the best answer.

Would it be possible to answer just one more question? I'm sorry to be a nuisance, but I'd like to be able to remove the default values if the user clicks on a "New" button. I would like for it to just go to a new record without keeping the defaults. (I think I read that DefaultValue doesn't like nulls, so I'm not sure how to accomplish this.)

Again, thanks so much for your help! I can't tell you how pleased I am to get this working.
Feb 6 '20 #13
NeoPa
32,556 Expert Mod 16PB
Hi Sandy.

Yes I can answer that in here. We have rules about keeping on topic within individual threads but I think this is relevant to the original question.

The answer is that you have to leave the property with an expression that resolves to the Null value. That is to say that it should be empty of any characters. As we saw before, in reverse, there is a big difference between an empty value (In this case actually an empty string or ZLS.) and a value that resolves to "" - which in turn resolves to an empty string.

To illustrate :
Expand|Select|Wrap|Line Numbers
  1. X.DefaultValue = ""
will work to clear any default previously set. However :
Expand|Select|Wrap|Line Numbers
  1. X.DefaultValue = """"""
will leave a reference in there to a ZLS which will cause the result to want to be treated as a string rather than Null.

If you look at the design after making these changes above you'd see it as empty for the first one and a pair of double-quotes ("") for the second.

The first is empty whereas the second only resolves to empty (ZLS in this case) when used.
Feb 7 '20 #14
SandyB
6
That worked, thanks so much for your patience!
Feb 7 '20 #15
NeoPa
32,556 Expert Mod 16PB
It's been a pleasure. It's great to see members trying what they can first and it always makes it easier to see what you're dealing with when you do.
Feb 7 '20 #16

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

Similar topics

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...
8
by: Ray | last post by:
I have a data input form and need to automatically duplicate the existing record as a new record by clicking a button. The main purpose to duplicate the record is that the new record is very...
1
by: gazelle04 | last post by:
Lets say I have this table "SchoolMajors", It has fields like SchoolID, DegreeID, and MajorID. How can I check if there's a duplicate of MajorID within SchoolID and DegreeID. How can I do that...
1
by: Invicta | last post by:
There are two fields I need to copy preferably using a command button. If possible I would also like to stipulate how many new records I want. I've made a start using the in built Duplicate Record...
3
by: mlb992000 | last post by:
I have a form that has a duplicate record command button and I need to create an event that will clear two of the fields once the duplicate record is clicked so that the user will know that it has...
1
by: mlb992000 | last post by:
I have a form that has a duplicate record command button that allows the user to duplicate all information on the form and change two fields. I need to create an event that will clear the two fields...
1
by: VinArt | last post by:
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"...
3
by: Shane van Heerden | last post by:
Table Fields: ID, Route, Flight1, NewDate1, Flight2, NewDate2, Surname, TagNumber etc. Form Filter Code: If Not IsNull(Me.txtStartDate) Then strWhere = strWhere & "( >= " &...
16
by: Joe Y | last post by:
In my Access program, I have a main form that holds recipe general information and a sub-form that keeps the actual formula. The main form name is “recipe” and Record Source is a table named...
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
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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

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.