Help with Error in Macro

[Sousuke81]

Long time viewer but first time poster. I'm having a problem with my Database, I have the same embedded macro built into multiple buttons but on this one form it is giving me an error. I've tried deleting the button, remaking it from scratch, copying the button from a working form but no matter what I do with the the form it doesn't work. I've even gone so far as to remake the form completely from the ground up and it is still not working.

The error I'm receiving is "Microsoft Access cannot find the object 'Created.'" If 'Created' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly. After hitting the Okay button, the Macro Single Step window comes up and gives the Error number as 3021, Action Name: RunMenuCommand, Arguments: 97.

I am using Access 2010, the Macro is doing 5 things in it. It is setting a value to three different fields in the corresponding table that the form is adding the record to, then saving the record and finally closing the form. In that specific order.



I've got several buttons throughout the entire database that do this exact function and they work fine, it is just in this one form where I am having this problem. I have looked at the table to verify the table names are correct to what the macro is inputting and they are so I'm at a loss at figuring this out to why it is not working. I have backed up and repaired the database as well and it is still not working. Any assistance would be greatly appreciated.

[June7]

Why are you needing to save these data - is data being duplicated between tables?

Need something to analyse. Provide code or better, the database. Follow instructions at bottom of my post.

I don't use macros, only VBA, but I can usually figure them.

[Sousuke81]

Here is the database. The reason for the saving was because after setting the value I wanted to make sure it saved and then closed. When you open it, just type you initals in and press enter. The problem form is under Robotics Reagent, then 1M DTT (DNA IQ) form (Form name is 1M DTT and the Table associated with it is 1M DTT (Robotics))

Greatly Appreciated, also first time builder of an access database so not very keen on VBA that is why I did the Macro Builder.

[June7]

Advise no spaces and special characters/punctuation (underscore is exception) in naming convention. Using spaces, commas, %, hyphens, parens, etc in naming can cause issues. Use only alpha/numeric and underscore. Better would be: Pct_Chelex, 1M_DTT_Robotics.

Instead of using code to save current date and time, use DefaultValue property. Also advise to have date/time in one field. The CreatedBy field can also be populated with DefaultValue property set to: [Logged In].

[QA/QC Date and Initials] should be two fields.

[Sousuke81]

Advise no spaces and special characters/punctuation (underscore is exception) in naming convention. Using spaces, commas, %, hyphens, parens, etc in naming can cause issues. Use only alpha/numeric and underscore. Better would be: Pct_Chelex, 1M_DTT_Robotics.

Instead of using code to save current date and time, use DefaultValue property. Also advise to have date/time in one field. The CreatedBy field can also be populated with DefaultValue property set to: [Logged In].

[QA/QC Date and Initials] should be two fields.

Most of the database was made by someone else, I just had to make it work so most of the tables and forms were made prior to being given to me, the Queries and Reports were done by me and some additional fields were made after an audit. Now I gave you the database as it being all in one, while in practice here I have the tables on our server as a backend database with the front end containing the Linked tables to the back end with Forms, Queries and Reports on the front end. With that setup, how pain staking will it be having to rename everything on the backend, fields included, then making the front end recognize every change done to it.

The QA/QC field is just a text box and is added in most of the time at the time of making but some are added after creation. Basically that field is a direct conversion of the analog paper logbooks we had where it was a single field that was written in that way.

[June7]

Design changes for renaming stuff can be a nightmare. I use Rick Fisher's Find & Replace for Access. Saved my sanity more than once. Cost about $50.

You will have to manually create fields (splitting the QA/QC field). A change like must be done when no one is working with db. Then everyone must have a revised frontend to work with that change. Sounds like a busy weekend in the office. Just depends how important it is to you to fix. Maybe you want to live with it as is.

[Sousuke81]

It's working fine right now (QA/QC Field). I'm just more worried about that button not working. Everything else within the database works fine and as intended with no problems except for that button.

[June7]

What did you think of my suggestion to not use button and code and instead use DefaultValue property to populate the fields?

[Sousuke81]

It's an idea, but the reason for the implementation of the button to fill in the data was to cause a Paper trail for audits instead of just filling in with default values. Similarly when someone adds in a QA/QC date through the other action on the home page, we needed a way to add that and say when it was added without a default value. Yes when you make the reagent the first time it could easily just be done as a default value for the date/time created and the user creating it but I just don't know how an auditor would see that as saying it was done at that time. Being accredited we have to basically have a trail to follow when it was created, when things were added and when things were corrected so that is the explanation of the reason behind the button for adding the data at the moment when it was pressed.

[June7]

What does date and time created indicate - when the record is created?

Why does the Form have "Created" in its BeforeUpdate event? I removed it then no more error.

[Sousuke81]

Basically yes, but it's also for saying that anything that is changed after that date and time are done by someone else. Only 3 people have access to correct any errors in the database through a different Frontend that they have.

[June7]

Then I don't understand concern with using DefaultValue.

Did you fix the form event?

[Sousuke81]

Okay I did all your suggested changes to just that table and subsequent forms, queries and reports. It is still not fixing the error. I removed all special characters and replaced all " " with _ in the tables and fields in the tables. I'm still getting the error even when I have the button only closing the form, not even saving the record or anything just closing the form.

[June7]

All I did was the correction noted in post 10 and the form worked.

[Sousuke81]

Just noticed what you said about the Created, and what do you know, it works. Works completely from the original version that I sent you without any changes. I have no idea how that Created got into the Before Update field, maybe next time I should read what you typed before saying something. Thank you so much for this, it has helped me so much.