This worked out great to be able to post on the web database. But this cause me one problem. It didnt allow me to edit any of the fields in that row, because before any change it would find it being a duplicate. If there was a way of doing this only on insert, and only testing on change of the uniqueness. Is this possible?
Unfortunately, I don't have an answer for that. You might ask this question on www.utteraccess.com. They have a forum for web databases and one of the regulars might have an answer: http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html
Thank you, thank you, thank you!!!! I have been searching for this answer for HOURS!!! It's wonderful that MS has provided SharePoint and Access integration to provide quick (at least supposed to be quick) solutions for smaller databases; however it is very frustrating that they take away data integrity features in web databases but do not make it clear how to work around those stringent restrictions. Again, thank you for this article. It will help me so very much!! :)
Hi Admin, I just left you a question about this issue, but have since solved it through the comments here: http://blogs.office.com/b/microsoft-access/archive/2010/02/18/composite-keys-in-web-databases-through-data-macros.aspx
Which might be worth mentioning in your article.
That is: If you have more fields than the ones you want to index, you will need an extra expression to make this macro allow changes to those other fields.
Hi Roger - I wonder if you have any similar experience with this problem in Access 2013 Web databases? I am trying to get it to work but I notice a couple of things: firstly at the table level the macro event names are "On Insert" and "On Update", rather than "before insert" and "before update". The problem I then have (e.g. on inserting a row)which may be caused by this is that the error is triggered regardless of what values I put into the 2 "key columns".
In my case the table I am using is "stakeholders" and the 2 "key fields" are "Program ID" and "Email". So, I set up a data macro behind "On INsert" on the "stakeholders" table, added a "Look up a record" block and used the following condition:
[Program ID]=[Stakeholders].[Program ID] And [Email]=[Stakeholders].[Email]
Would you be able to give me any advice? Many thanks!
I’d suggest asking your question on http://www.utteraccess.com. Several of the regulars are much more knowledgeable about them. There’s a forum specifically for Access Service (Web Databases) http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html
10 comments:
This worked out great to be able to post on the web database. But this cause me one problem. It didnt allow me to edit any of the fields in that row, because before any change it would find it being a duplicate. If there was a way of doing this only on insert, and only testing on change of the uniqueness. Is this possible?
Unfortunately, I don't have an answer for that. You might ask this question on www.utteraccess.com. They have a forum for web databases and one of the regulars might have an answer: http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html
Thank you for your response! I quickly found a soloution using the search option.
http://www.utteraccess.com/forum/index.php?showforum=74
All that is needed, is a If statement with the following
If [IsInsert]=true Or Updated("UNIQUE") then
Keep up the great work!
Thank you, thank you, thank you!!!! I have been searching for this answer for HOURS!!! It's wonderful that MS has provided SharePoint and Access integration to provide quick (at least supposed to be quick) solutions for smaller databases; however it is very frustrating that they take away data integrity features in web databases but do not make it clear how to work around those stringent restrictions. Again, thank you for this article. It will help me so very much!! :)
Thaaaaaaaank you! for sharing your talent. Practical, useful! Thanks.. again
Hi Admin, i have similar problem. I will really appreciate if you write full macro code.
thankyou
Hi Admin, thanks for the great post. Strangely, this BeforeChange macro is not allowing me to make any changes to an existing record.
The error triggers when I try edit the table directly in Access. Have you come across this before?
I am now looking for a way to exclude the current record from the LookUpARecordIn action.
If you have a solution to this, I would appreciate it since I am very new to Access.
Hi Admin, I just left you a question about this issue, but have since solved it through the comments here: http://blogs.office.com/b/microsoft-access/archive/2010/02/18/composite-keys-in-web-databases-through-data-macros.aspx
Which might be worth mentioning in your article.
That is: If you have more fields than the ones you want to index, you will need an extra expression to make this macro allow changes to those other fields.
Thanks again!
Hi Roger - I wonder if you have any similar experience with this problem in Access 2013 Web databases? I am trying to get it to work but I notice a couple of things: firstly at the table level the macro event names are "On Insert" and "On Update", rather than "before insert" and "before update". The problem I then have (e.g. on inserting a row)which may be caused by this is that the error is triggered regardless of what values I put into the 2 "key columns".
In my case the table I am using is "stakeholders" and the 2 "key fields" are "Program ID" and "Email". So, I set up a data macro behind "On INsert" on the "stakeholders" table, added a "Look up a record" block and used the following condition:
[Program ID]=[Stakeholders].[Program ID] And [Email]=[Stakeholders].[Email]
Would you be able to give me any advice? Many thanks!
I’d suggest asking your question on http://www.utteraccess.com. Several of the regulars are much more knowledgeable about them. There’s a forum specifically for Access Service (Web Databases)
http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html
Post a Comment