Design Question

[roarcrm]

We currently have a database where the main form is Client and Activities is the sub-form, so basically we can search for a client and the activities are all contained in the sub-form. It's easy to add activities, we just search for the client and add it in the sub-form. However, we've just started doing events so there could be up to 30 clients at these events, it would be time consuming to search for all 30 and input them individually. Can anyone suggest a better way? Is it possible to create another form for example that would allow multiple clients to be selected and this data could be saved for all of them?

[orange]

The first step in design decisions is to get a clear understanding of what the things involved are and how they relate to your business. A good definition of Client, Activity and Event would be a good step. Some examples of Events and Activities would also be useful.
Your business rules/facts along this line of thinking:

A Client can be involved in 0,1 or many Activities OR
A Client can be involved in1 or many Activities
A Client may Participate in 1 or many Events
An Event may involve 1 or many Clients

What, if any, is the relationship between Activities and Events?

Getting your tables and relationships set up to meet your business requirements is critical.

Readers don't know what an Activity or Event is in your context. We need more info to be more focused in our replies/suggestions.
Good luck.

[roarcrm]

Hopefully it's more simple than you think. An event is simply classed as an activity, the only thing we log are clients and activities.

A client can have many activities over time. Each time we deal with a client we log an activity.

Sometimes, with our events, we now have the same activity to log against multiple clients and this takes time to input. A solution to this is what we're after.

Thanks.

[orange]

It may be more simple, but when you say in your first post

However, we've just started doing events..it would be time consuming.....

and now you say

An event is simply classed as an activity,..

.

You either have
A: nothing to change because you've been dealing with Activities for a long time. OR
B: a lot of rework because you haven't got a good definition of Activity and/or Event OR
C: some combination of A and B to create a tables and relationships to meet your requirements.

If an Event is simply an Activity, then what exactly is the purpose of your post?
Good luck.

[roarcrm]

If an Event is simply an Activity, then what exactly is the purpose of your post?
Good luck.

An event is simply an activity. The problem is that when we have 30 people attend the event it takes a long time to search for each client and create a new activity for each.

[NVRensburg]

HI there

I have an Access database which was designed by someone else. Does anyone know why i wouldn't be able to change an entry if it's not in the same month. i.e. if an entry was created last month and I go to try and change it now it won't allow me, i'd have to go and change the date on my PC to any day in February and then I'd be able to change details in it.

[orange]

NVRensburg,

I suggest you make a new thread, specific to your issue. You have jumped into the middle of the thread and issue of roarcrm.

I also suggest you add some details so that readers have some idea of your context and application.

[orange]

roarcrm,

If updating 30 records is a major issue for you and/or your database, then I recommend you post a copy for readers to review. This is a relatively small number for most database applications.

[NVRensburg]

Hi Sorry I'm new here so I didn't realise. Sorry about that. OK i'll try post an example of the database

[roarcrm]

I think basically what I want is a form to be able to select say 10 clients, then enter the details of the activity and for this to somehow create 10 entries in the activities table, one associated to each of the clients selected. Is this possible?

[RonL]

A suggestion (from another novice ☺️). Use a multiselect list box to select the desired clients, then have code loop through them using whatever technique you use now to add records to the recordsource of the subform. (This assumes the total client pool is not inconveniently large.)

-Ron

[orange]

You start with tables and relationships that support your business facts. Get some test conditions and make sure your data model supports the business. Then move to queries/forms etc.

[roarcrm]

A suggestion (from another novice ☺️). Use a multiselect list box to select the desired clients, then have code loop through them using whatever technique you use now to add records to the recordsource of the subform. (This assumes the total client pool is not inconveniently large.)

-Ron

Hi,

This sounds exactly like what I need it to do. Is there somewhere I could get tips on the coding for this? Thanks for your help.

[RonL]

Google on multiple selection listbox. Here's an example of what you'll find:

KB827423 How to retrieve the selected items in a multiple selection List
Box as a comma-delimited string in Microsoft Access
http://support.microsoft.com/kb/827423

Edit - You could then construct the appropriate SQL update or insert statement ... Where [client] IN that comma-delimited string.

Edit2 - check that last advice. The IN clause requires a list of comma delimited values, not a single string containing commas. Instead of constructing the string, may need to run the SQL action statement each time through the loop.

[roarcrm]

Google on multiple selection listbox. Here's an example of what you'll find:

KB827423 How to retrieve the selected items in a multiple selection List
Box as a comma-delimited string in Microsoft Access
http://support.microsoft.com/kb/827423

Edit - You could then construct the appropriate SQL update or insert statement ... Where [client] IN that comma-delimited string.

Edit2 - check that last advice. The IN clause requires a list of comma delimited values, not a single string containing commas. Instead of constructing the string, may need to run the SQL action statement each time through the loop.

Thanks for that, I don't understand your edit2 though? Also, how do you create the loop within access?