Create multiple records automatically

[fnem]

I have a lawncare database that assigns a job to a customer.
Each customer may have multiple lawns.
Each job contains multiple tasks, each task being for a given lawn that the customer may want treating. They may want all of their lawns treating, or just some of them.



I am able to create these tasks for the given lawns through use of a subform that assigns a task ID to a given lawn (chosen via combo box) and then links this to the Job ID. I find however that I have to create a new task for each lawn, one at a time.
I would like to automate this in one of two ways:

1, Allow quicker selection of required lawns using check boxes or similar and then create multiple tasks for these lawns all linked to the given job
or
2, provide an 'all' option on the combo box to immediately select all the customer's lawns (this will account for the majority of jobs) and create multiple tasks for these.

Any pointers or help greatly appreciated, I'm a newb.

[ranman256]

make a form with a listbox of Tasks. (tTasks table)
dbl-click the task to add them to a tPicked table. (append qry)
then select a customer (combo) on the screen as the target.

make a button to Post these tasks to the Clients tasks table, this button will fire an append query to add all the Tasks from the tPicked table to the Clients task to do table. They all get added at once.
then just change customer combo to change target customer.
add or delete an item from tPicked table if needed if a different customer has slightly different tasks.

[orange]

Can you show us your tables and relationships? Or a copy of your database with a few records to show us your forms/queries in context?

[fnem]

A lot to get my head around there. Thanks, I'll have a look at this.

[fnem]

Can you show us your tables and relationships? Or a copy of your database with a few records to show us your forms/queries in context?

I've uploaded a copy of the database relationships as they stand, I can't upload the database as small though it is it exceeds the 500kB max

Many Thanks

[Bob Fitz]

I can't upload the database as small though it is it exceeds the 500kB max

"Compact and Repair" the db, then zip it. Should be small enough then to post.

[fnem]

"Compact and Repair" the db, then zip it. Should be small enough then to post.

Thanks for your guidance Bob.Lawncare Database 02-02-21.zip

[orange]

fnem,

Where exactly in the life of this database are you at the moment? Seems you have something operational that is helping support your business, but that may have some shortcomings/cumbersome processes.

I am attaching a revised graphic of your tables and relationships --- extended all tables to show all fields, and rearranged things to reduce the number of crossed lines.

Have you tested your current model/structure against your requirements? Do you have a list of scenarios that you can "exercise" against your current structures without actually physically writing code?

You have gone to a very detailed physical set up, named your fields well (no embedded spaces) and tables seem to cover a broad scope of the business.

You know your business and requirements better than any reader so responses may seem more generic and high level than you require.

As for creating several records at one time, a typical approach would be to have a combo box and a listbox(es) and some code.
The combo box may contain Customers, and listbox may contain CustomerLawns, another listbox may contain Tasks/Treatments. You select a particular Customer; then select 1 or more of that Customer's lawns and then select the Tasks/Treatments for each of the selected lawns for the selected Customer. Some event code (eg button click) then iterates over the selections and executes SQL to insert records for the relevant combinations. Of course there would be some validation logic to ensure selections have been made.

Good luck.

[fnem]

Hi Orange,

Thank you for your reply.

I use commercial software for my business currently but find it both expensive and cumbersome and have convinced myself that I could make something in Access that would fulfill my needs better and with no ongoing costs. I have thought through a number of operational scenarios and the database you see is my first draft attempt at organising the structure to accommodate these. I am trying to think through my requirements before commencing the work but realise that my own ability may limit what I can achieve with the database and also find the process of construction helps stimulate fresh ideas.

As a relative novice but with some understanding of database principles I welcome all and any advice as well as any resources that might help a beginner such as myself build up a better knowledge of how to manipulate Access.

Regarding the questions I posed, I currently create the Job ID when I open a new form from the Customer form, the Customer ID is passed to this and the lawns owned by the customer are shown in a subform via combobox. I select the lawns I want to be included in this job and each is assigned a Task ID and attached to the job. This is all a bit laborious though and it would be much slicker to just click 'All lawns' and create tasks for all of the customer's lawns and/or select the lawns from radio buttons or similar rather than multiple combo boxes.

[Micron]

the lawns owned by the customer are shown in a subform via combobox. I select the lawns I want to be included

Red flag - tells me you're using multi value fields yet I see no indication of that in the relationship pics. I say that because unless I'm losing it, you can't multi select from a combo unless that combo is bound to a mvf.

You might want to consider having this done professionally and incur a reasonable one time cost if this is going to cause you to take time away from the business you need to support. I imagine that even if lawn maintenance is delayed because of snow, you do snow plowing (which you could incorporate into this db) or need to focus on getting new contracts for the spring.

[orange]

fnem,

Regarding the questions I posed, I currently create the Job ID when I open a new form from the Customer form, the Customer ID is passed to this and the lawns owned by the customer are shown in a subform via combobox. I select the lawns I want to be included in this job and each is assigned a Task ID and attached to the job. This is all a bit laborious though and it would be much slicker to just click 'All lawns' and create tasks for all of the customer's lawns and/or select the lawns from radio buttons or similar rather than multiple combo boxes.

I made a mock up based on your first post #1 yesterday. I worked from a simple model, guessing some things about Job, Lawn, Task and Service. I then generated a database from the model (trying to understand some software I only use now and then). I realize it is a much simpler model than your business entails.



I have a test form to demo how one could create multiple records.



The process is on the record of Customer I show
-the lawns he/she owns in a combobox
-the general list of tasks in a listbox

1 get the customer record you want
2 in lawns combo select a lawn for which tasks will be performed
3 multi select the tasks for this lawn
4 press the Assign Tasks button
This will create a JobId for this customer for this lawn and these actions. It also inserts records into LawnCareService table.

5 Select another lawn for this customer
6 Since same customer in this session get new JobId (how are jobs identified if Customer has multiple lawns and many need work??? same JobId or separate for each lawn??)
7 multi select tasks for this lawn
8 press the Assign Tasks button

These sql statements are executed when the assign task is clicked with appropriate selections.

INSERT INTO Job(CustomerID) values(1);
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(20,4,3,#03-Feb-21# );
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(20,4,1,#03-Feb-21# );
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(20,4,2,#03-Feb-21# );
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(20,4,6,#03-Feb-21# );
INSERT INTO Job(CustomerID) values(1);
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(21,2,4,#03-Feb-21# );
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(21,2,6,#03-Feb-21# );
INSERT INTO Job(CustomerID) values(3);
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(22,5,3,#03-Feb-21# );
INSERT INTO Job(CustomerID) values(4);
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(23,6,4,#03-Feb-21# );
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(23,6,2,#03-Feb-21# );
INSERT INTO Job(CustomerID) values(4);
INSERT INTO LawnCareService(JobID, LawnID, TaskID, ServiceDate) Values(24,6,6,#03-Feb-21# );

Those in blue are for Customer 1 one lawn and related tasks for that lawn.
Those in this color(middle brown) are also for Customer 1, different lawn, separate tasks
Those in orange are customer 3, new jobID, single lawn and single task
Those in this blue are for Customer 4, new Jobid, single lawn, 2 tasks
Those in this purple are for Customer 4 but this as in a new session ,so he gets new JobId,single lawn, single task.

Hope it's useful.
Good luck.

[fnem]

Thank you very much for taking the time to provide such a detailed response. You have a slightly different structure to what I have in mind but the principle is just the same. Plenty to go on there and I'll definitely be having a fiddle with this over the weekend to see if I can get something working. Much appreciated!

[orange]

Happy to help.
I'm attaching the code that is behind the Assign Tasks button. You may get some ideas from the code that will allow creating multiple records "automatically".

This code is slightly modified to that used with post #11. Here, if the Customer has multiple lawns and multiple tasks for each, and this is all in 1 session of Access, then I put the multiple lawns and their multiple tasks in 1 JobId. I'm not sure what your intent is/was.

Good luck. with your project.