Split database: The data macro 'Comments.AddComment' cannot be found.

[Modify_inc]

Using Access 2010, I split my database and it appears to respond much quicker on my network.



One issue I just found though, after testing it further, is now the 'comment field' gives an error when you try to add a comment.

The data macro 'Comments.AddComment' cannot be found.

I'm guessing that because the table is in the backend and so is the macro, therefore the frontend can't find it. I have read that data macros are not supported with linked tables.

Can anyone knowledgeable in this area suggest an alternative method to re-enable my comments?

The database I'm using is the contacts web database template provided by MS in Access 2010, though I have converted the web database to a standard database.

If this cannot be done, please suggest ways to gain performance over a network and decrease the many error messages that report the database is locked when accessed by multiple users (typically only 3 users).

Thanks
Mike

[June7]

Why do you need code to add a comment?

[Modify_inc]

Why do you need code to add a comment?

I'm not sure why? I'm just using the template provided by Microsoft and that is the way they have it. The template is in Access 2010.

[June7]

I have not worked with the contacts web template. If you want to provide your modified version I will try to understand and resolve issue. Follow instructions at bottom of my post.

[Modify_inc]

I have not worked with the contacts web template. If you want to provide your modified version I will try to understand and resolve issue. Follow instructions at bottom of my post.

Thanks very much for offering too, that is more than I expected!

I tried to upload it, but it says only 500KBs allowed and the db file is 1.02MBs. Your sig says up to 2MBs, am I missing something?

I can uploaded it to my drop-box if you like, just let me know.

Thanks again!

Mike

[June7]

2mb zip file allowed. Use Windows to create a zip folder and copy the db into it.

[Modify_inc]

2mb zip file allowed. Use Windows to create a zip folder and copy the db into it.

ok, got it.

I attached the version before the split and you will notice the "add comment" works. Once you split the database, and then proceed to add a comment, you will notice the error message I stated earlier, The data macro 'Comments.AddComment' cannot be found.

The main form is "Main"

Thanks again!!

Mike

Test Database.zip

[Modify_inc]

I have not worked with the contacts web template. If you want to provide your modified version I will try to understand and resolve issue. Follow instructions at bottom of my post.

Not sure if you had a chance to look at my database, but I stumbled on something regarding the database.

I noticed the ClientDetails Form has a macro in it for saving and creating new contacts, yet it works just fine in the split database.

Any idea why this is so?

[June7]

Do you mean the form named ContactDetails? That macro is not a data macro so it works fine in split db.

The ContactCard form that has the Comments as a subsubform is odd. Never seen this. The Comments subsubform container has a report as SourceObject. Reports cannot be used to enter data so the developer created a textbox and a button for entry of comments then refreshes the report so the new comment will display. I have no idea why this design was chosen. Maybe the developer just like the way the report looked on the form and/or didn't want user to have to scroll down to new record row for adding comments also maybe didn't want to allow edit of comments after saved.

Options are:

1. Change the SourceObject property to Comments table. Delete the comment textbox and button. You can set a DefaultValue of Now() for the CommentDate field.

2. Modify code for the Save Comment button.
I use VBA which means selecting [Event Procedure] in the Click event property, click the ellipses (...) to go to the VBA editor and type code:
CurrentDb.Execute "INSERT INTO Comments(CommentDate, Comment, ContactID) VALUES(#" & Now() & "#, '" & Me.txtAddComment & "', " & Me!ID)
Macro equivalent would probably require creating an UPDATE query object that the macro would then execute with OpenQuery method.
Both will require a refresh of the subsubform.

Do you know how to create and edit macros? Access Help has guidelines. That's where I went to figure out the data macro.

[Modify_inc]

Do you mean the form named ContactDetails? That macro is not a data macro so it works fine in split db.

The ContactCard form that has the Comments as a subsubform is odd. Never seen this. The Comments subsubform container has a report as SourceObject. Reports cannot be used to enter data so the developer created a textbox and a button for entry of comments then refreshes the report so the new comment will display. I have no idea why this design was chosen. Maybe the developer just like the way the report looked on the form and/or didn't want user to have to scroll down to new record row for adding comments also maybe didn't want to allow edit of comments after saved.

Options are:

1. Change the SourceObject property to Comments table. Delete the comment textbox and button. You can set a DefaultValue of Now() for the CommentDate field.

2. Modify code for the Save Comment button.
I use VBA which means selecting [Event Procedure] in the Click event property, click the ellipses (...) to go to the VBA editor and type code:
CurrentDb.Execute "INSERT INTO Comments(CommentDate, Comment, ContactID) VALUES(#" & Now() & "#, '" & Me.txtAddComment & "', " & Me!ID)
Macro equivalent would probably require creating an UPDATE query object that the macro would then execute with OpenQuery method.
Both will require a refresh of the subsubform.

Do you know how to create and edit macros? Access Help has guidelines. That's where I went to figure out the data macro.

I'm not sure if I'm following you correctly here, actually, I'm confident I'm not.

1. Change the SourceObject property to Comments table.
I do not know where to find the SourceObject property to change it to the Comments table.

2. Delete the comment textbox and button.
I assume you are referring to the form NameCard, and you want me to delete the textbox and Save button for the comment field?

3. Set a DefaultValue of Now() for the CommentData field.
I assume this is to be done in the table Comments?

4. Modify code for the Save Comment button.
If my assumption was correct in step 2, I have no Save button to modify, because I deleted it.

5. Do you know how to create and edit macros?
Not on my own, but I have edited them with help.

Thank you and I hope you don't decide to give up on me, because of my lack of expertise with Access.

Mike

[June7]

1. SourceObject is property of subform/subreport container control. A SourceObject can be table, query, form, report. Control and object properties are on the Property Sheet. Interacting with the Property Sheet is basic to creating and designing objects and controls.

2. Yes, if you choose that option 1 from post 9.

3. Yes, or in textbox on form.

4. If you choose option 2 from post 9 the comment textbox and button will still be used, not deleted.

5. Time to learn how to code on your own - VBA or macro or both. I have never coded macros for my own projects, only explored in helping forum posters. I used the Access Help to quide me in deconstructing your data macro.