By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,276 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,276 IT Pros & Developers. It's quick & easy.

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

P: 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.
3 Weeks Ago #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.

Share this Question
Share on Google+
15 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,364
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!
3 Weeks Ago #2

P: 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
3 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,660
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.
3 Weeks Ago #4

P: 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.
3 Weeks Ago #5

NeoPa
Expert Mod 15k+
P: 31,660
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.
3 Weeks Ago #6

NeoPa
Expert Mod 15k+
P: 31,660
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?
3 Weeks Ago #7

NeoPa
Expert Mod 15k+
P: 31,660
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.
3 Weeks Ago #8

twinnyfo
Expert Mod 2.5K+
P: 3,364
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!
2 Weeks Ago #9

P: 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?
2 Weeks Ago #10

NeoPa
Expert Mod 15k+
P: 31,660
Thanks Twinny.

Always great to be able to help anyone but other experts is better still :-)
2 Weeks Ago #11

NeoPa
Expert Mod 15k+
P: 31,660
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.
2 Weeks Ago #12

P: 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.
2 Weeks Ago #13

NeoPa
Expert Mod 15k+
P: 31,660
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.
2 Weeks Ago #14

P: 6
That worked, thanks so much for your patience!
2 Weeks Ago #15

NeoPa
Expert Mod 15k+
P: 31,660
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.
2 Weeks Ago #16

Post your reply

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