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

How to stop a field from populating when duplicating a record in an Access form?

P: 4
I have an Access form with action buttons one of which is a duplicate record button. When activated, the duplicated record has one of the yes/no fields toggled on. It is not toggled on in the originating record; just the duplicated record. Any idea of why it is happening and how to correct or stop it? Any help will be greatly appreciated. Thank you.
Jun 19 '19 #1

✓ answered by zmbd

as Neopa suggested... my thoughts on places to check.

Access does not "randomly" set values in records - upon creation of the record all field values are null unless and/or until something takes an action such as a default value.

So in order of thought:
Tables at design, the [Yes/No] fields have a default value of "No" (=== false) when initially added to the table. The most common habit is for people to change this value to "Yes" or delete the value... creating a null... which can have some really fun consequences for searches and if one pushes the record to a SQL-Server

Event in the form:
Despite the fact that the control button is running via macro vs vba... either the form's or the control's after_update event will still trigger once the record is pasted. Do we have any VBA or macro code at the form level that could potentially alter the value of the field.

Event at the table level:
I would dare say, that not many people know that this can be done in the newer versions of Access - the equivalent of a trigger for other databases. A macro can be created that either takes direct action itself or can call a VBA function. You would most likely know if you've done this, hence, why it's the last place I would look for such a Gremlin.

---
I've built a test database in an attempt to duplicate your experience; however, I am not able to do so with what I have done.

Please do me a favor,
Open the command button properties and show the macro editor.
select one row of the macro code, doesn't matter which, we just want the focus within the script.
Press <Ctrl><A> to select all
Press <Ctrl><C> to copy all

Come back to this thread
Click in the reply box and <Ctrl><P>
Select the pasted text and click on the [CODE/] format
You should get something like this (of course yours will have other commands ;-) ):
Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="UTF-16" standalone="no"?>
  2. <UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro For="Command2" Event="OnClick"><Statements><Action Name="FindNextRecord"/></Statements></UserInterfaceMacro></UserInterfaceMacros>
don't worry about stepping the code one of us will take care of that when we review it.

What I am looking for is anything extra that the macro may be executing behind the scenes and this is the most sure way to check that there isn't an extra call being made.

---
On a side note:
Something I find a bit interesting is that you are making a duplicate record which tells me that you do not have a primary key set for this table... why are we duplicating record information? This then suggests that the database may not be normalized in that we're repeating the same information... there are certainly very valid reasons, say an event in a calendar or reoccurring sale of an inventory item to a customer (I could think of other ways and means to record this event).

Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,419
What is it that you do when that button is triggered? What code or macros are executed?

It doesn't make a great deal of sense to ask what you're doing wrong without ever telling us what you're doing.
Jun 19 '19 #2

zmbd
Expert Mod 5K+
P: 5,397
Is the control bound to the [Yes/No] data field?
Jun 19 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 3,212
KaeKae,

I'm with NeoPa on this one. Data doesn't just randomly change in MS Access. You have to tell it to do something. Listing the code you are using would be extremely helpful if you want us to hepp.
4 Weeks Ago #4

P: 4
So sorry Neopa. I used the button wizard to create the duplicate record button and it created the following (I added the save record step at the end):

Expand|Select|Wrap|Line Numbers
  1. OnError (Next,)
  2. SelectRecord
  3. If [MacroError]=0 Then
  4.    Copy
  5. End If
  6. If [MacroError]=0 Then
  7.    RecordsGoToNew
  8. End If
  9. If [MacroError]=0 Then
  10.    SelectRecord
  11. End If
  12. If [MacroError]=0 Then
  13.    Paste
  14. End If
  15. If [MacroError]=0 Then
  16.    MessageBox (=[MacroError].[Description], Yes, None, )
  17. End If
  18. SaveRecord
Hope that provides the information you were looking for in order to assist. I understand that Access would not perform a random action but I have no macros that have anything to do with the Cancel field that gets toggled on. I do have it in some queries but none of them are tied to the form; just reports. Thank you.
4 Weeks Ago #5

zmbd
Expert Mod 5K+
P: 5,397
Nothing in that macro should change the underlying record, while woefully convoluted, it's really nothing more than a [Ctrl][C]/[Ctrl][V]

Have you checked if ...
1) Default value of the field is in the true state.
2) Form level event that is running code that toggles the field value
3) Table level event that is running code that toggles the field value
4 Weeks Ago #6

P: 4
Yes, zmbd, it is a true copy/paste function but it the database is for an inexperienced user so the button is a helpful tool. Again, it is why I am confused why Access is choosing a random field to change to the true state. I guess I'm too much of an amateur but I don't understand your 1, 2, 3. Is it your solution to correct the problem?
4 Weeks Ago #7

NeoPa
Expert Mod 15k+
P: 31,419
I suspect that Zmbd is suggesting that you look in those three places to find where a DefaultValue for that Control on your form is being set.

It seems likely that one of those has a default set such that any new records that are added, without that value being explicitly set, will be added with the default.

BTW Thank you for your earlier response. It's so much easier when people respond to your suggestions simply and easily.
4 Weeks Ago #8

zmbd
Expert Mod 5K+
P: 5,397
as Neopa suggested... my thoughts on places to check.

Access does not "randomly" set values in records - upon creation of the record all field values are null unless and/or until something takes an action such as a default value.

So in order of thought:
Tables at design, the [Yes/No] fields have a default value of "No" (=== false) when initially added to the table. The most common habit is for people to change this value to "Yes" or delete the value... creating a null... which can have some really fun consequences for searches and if one pushes the record to a SQL-Server

Event in the form:
Despite the fact that the control button is running via macro vs vba... either the form's or the control's after_update event will still trigger once the record is pasted. Do we have any VBA or macro code at the form level that could potentially alter the value of the field.

Event at the table level:
I would dare say, that not many people know that this can be done in the newer versions of Access - the equivalent of a trigger for other databases. A macro can be created that either takes direct action itself or can call a VBA function. You would most likely know if you've done this, hence, why it's the last place I would look for such a Gremlin.

---
I've built a test database in an attempt to duplicate your experience; however, I am not able to do so with what I have done.

Please do me a favor,
Open the command button properties and show the macro editor.
select one row of the macro code, doesn't matter which, we just want the focus within the script.
Press <Ctrl><A> to select all
Press <Ctrl><C> to copy all

Come back to this thread
Click in the reply box and <Ctrl><P>
Select the pasted text and click on the [CODE/] format
You should get something like this (of course yours will have other commands ;-) ):
Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="UTF-16" standalone="no"?>
  2. <UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro For="Command2" Event="OnClick"><Statements><Action Name="FindNextRecord"/></Statements></UserInterfaceMacro></UserInterfaceMacros>
don't worry about stepping the code one of us will take care of that when we review it.

What I am looking for is anything extra that the macro may be executing behind the scenes and this is the most sure way to check that there isn't an extra call being made.

---
On a side note:
Something I find a bit interesting is that you are making a duplicate record which tells me that you do not have a primary key set for this table... why are we duplicating record information? This then suggests that the database may not be normalized in that we're repeating the same information... there are certainly very valid reasons, say an event in a calendar or reoccurring sale of an inventory item to a customer (I could think of other ways and means to record this event).
4 Weeks Ago #9

P: 4
zmbd you are a genius and I am an idiot! I have and after_update on a related field in the form (Cancel_Date) to make true if a data is entered in it. Would you recommend On_Change as a better option?


And you are right; the database is not normal. Based on the data source, the user's level of comfort and other factors, it is not designed by normal standards. Not something I would prefer but based on the circumstances of the client, it meets the needs and expectations and they are happy with it. I am just making some requested changes and I haven't been in it for over a year. A bit rusty apparently.
4 Weeks Ago #10

zmbd
Expert Mod 5K+
P: 5,397
::blush::
Anyone that steps on their toes enough learns not to step on their toes - Lord knows, I've stepped on my toes a few times - I advise steel toes! :)

The On_Change event might work; however, we don't have a good understanding your your design. I say give it a try - make a copy of your database, change the code to the On_Change event. Worse case is that it doesn't work...

If it doesn't work then we should understand what the macro in the related field is doing and why.

My other thought is to use the Me.NewRecord property of the form; however, using macros, I don't think there is a direct way to check if you are on a new record. You might be able to hybridize the Macro to execute vba to check; however, IMHO, you should just move to pure vba at that point.

> Design, sometimes the client doesn't really understand what they want... if you show them a better way they may take to it like a duck to water.
4 Weeks Ago #11

Post your reply

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