Recurring Events via Allen Browne

[PaulWilson]

Hey,
I used Allen Browne's recurring events concept to create a massive database that needs a recurring events component.
http://allenbrowne.com/apprecur.html

It works! It was great because it taught me about Cartesian joins. The problem is that Cartesian join queries are by definition read-only. I created a big set of recurring events using the Cartesian query, but I can't seem to do anything to them (like link them to tables where information about each event instance is held).

If I link a read-only query to something else, the result is also read-only!

So how do I get around it? Should I keep a shadow table identical to the read-only query in my system and keep updating that? That leads to update anomalies it seems to me. How is this kind of thing usually managed?

The Cartesian concept is fun, but it can lead to trouble for those with moderate skills (like me)!

 

[Guus2005]

You can use a read-only query as input for your update/delete/insert/... query.
You can not use the query itself to update/delete/insert because it is as you said, read-only!
Example:

update Table1 set Table1.Field1=1 where Table1.Id in (select Id from qryReadOnly)

Or something similar and more complex.

HTH

 

[PaulWilson]

Thanks. I think we're on the same page.

Actually, Allen Browne provided the answer in his sample database. He provides a mechanism for cancelling or re-scheduling events using an accessory table linked to the read-only query. This accessory table is altered via a separate form. He used a unique method of unpacking the onopen where condition that eluded me for a while.

I just used his example and added another accessory table and have it working now. Neat trick. Getting it user-friendly is a challenge though!

 

[AccessDabbler]

I'm making a senior transportation database, and I need to schedule recurring appointments. I was pointed at Allen Browne's recur database, but the problem I have is that the occurrences only show in the subform, and don't appear in the tblEvent with the parent event. I'm not trained in Access, and so I don't understand the solutions posted in this thread. I'm hoping someone could elaborate on them? Thanks!

Thanks. I think we're on the same page.

Actually, Allen Browne provided the answer in his sample database. He provides a mechanism for cancelling or re-scheduling events using an accessory table linked to the read-only query. This accessory table is altered via a separate form. He used a unique method of unpacking the onopen where condition that eluded me for a while.

I just used his example and added another accessory table and have it working now. Neat trick. Getting it user-friendly is a challenge though!

 

[burrina]

Actually the scheduled events do appear in tblEvent. Not sure how you have it setup. frmEvent is based on tblEvent.

 

[AccessDabbler]

Sorry to be confusing, I meant I want the recurrences that you onky see in the subform to be stored in the tabke with the original event, i.e. th event occurrs every Monday, so all of those are stored in the table.

Actually the scheduled events do appear in tblEvent. Not sure how you have it setup. frmEvent is based on tblEvent.

 

[burrina]

The recurrences are also stored in tblEvent. Only the Re-Scheduled (EventException) are stored in tblEventException. Have you modified the db from it's original design?

 

[AccessDabbler]

Okay, so I found out I don't actually need to store the recurrences in a Table (per the IT department at my job) so I'm using a query to view all of them, however, I hit a glitch. On my Event form I'm using two combo boxes to populate the fields of Client and Destination. The result of combo boxes are the ID#'s (primary keys) for each of those tables. How do I create a query or a part of a query to show that the the Client Name etc is equal to the information stored with each of those ID#'s?

 

[burrina]

If you don't truly understand this db you could be in for a world of headache. That said, you need to add your fields to tblEvent and set your combo box source to it. Otherwise you will need to add a field(s) to tblEvent and then link it to your new table for Clients.

Good Luck!