Form to enter multiple entries simultaneously

[aian]

First of all, sorry for the lengthy post...

I have a tbl_APPOINTMENTS, and I have created a form (frm_ADD_NEW_APPOINTMENT) that allows the user to enter an appointment for a person in a specific date.

I would like to create a form (or edit the existing one) so that I can use a choice among the following:

- Add a single appointment (already done in my existing form)
- Add 10 appointments, one every working day, MON to FRI
- Add 10 appontments, one for every MON/WED/FRI
- Add 10 appontments, one for every TUE/THU

The third and fourth option would work as follows:

1. MON 2.WED 3.FRI 4.MON 5.WED 6.FRI 7.MON 8.WED 9.FRI 10.MON
|------week1-----| |-----week2------| |-----week3-----| |--week4--|

or

1.TUE 2.THU 3.TUE 4.THU 5.TUE 6.THU 7.TUE 8.THU 9.TUE 10.THU
|--week1--| |--week2--| |--week3--| |--week4--| |--week5--|


The tbl_APPOINTMENTS has the following fields:

• ID_APPTS (Auto generated)
• NAME (Text)
• DATE (Date)
• TIME (dropdown list text such as 08:00, 08:15, 08:30, 08:45...... 20:30, 20:45, 21:00)
• PLACE (dropdown list text such as ROOM1, ROOM2 etc)
• COMPLETED (Yes/No with "No" predifined)
• COMMENT

So when the user adds a new appointment, the form must have the following options for filling in the DATE field:

• [ ]Single Appointment
• [ ]10 Appointments MON to FRI
• [ ]10 Appointments MON-WED-FRI
• [ ]10 Appointments TUE-THU

If the user selects any of the three later options, the form will auto-create 10 different appointments (therefore 10 different entries for the tbl_APPOINTMENTS), automatically fill in the appropriate dates AND automatically fill in the NAME, TIME, PLACE, COMPLETED and COMMENT in each entry, along with a different ID_APPTS for each one (well, obviously).

My knowledge of VB is limited on a copy-paste level, so please bear with me if use of VB is required...

Any ideas if/how I can accomplish that?

 

[Galaxiom]

Firstly move the client name to a Client table and refer to them in the appointments table using a key. You should not be repeating their name in multiple records.

Unfortunately it requires quite complicated VBA to do what you want especially if you consider having to fit the scripted appointments around existing ones. It is a job for an expert and well beyond cut and paste.

Time to start seriouly learning VBA.

 

[aian]

Galaxiom thanks.

The client name is already in a different table, I just didn't mention that in my post.

If we left VB code aside, is there something I could do using macros?

Even if I couldn't do the whole thing, any little bit of automation would save me lots of time.

For instance TransferText Action with an external txt file?

 

[aian]

OK, maybe I've found a less complicated way.

I can make a form that includes a subform.
The main form will always add the person's name and the subform will contain all the rest

* ID_APPTS (Auto generated)
* DATE (Date)
* TIME (dropdown list text such as 08:00, 08:15, 08:30, 08:45...... 20:30, 20:45, 21:00)
* PLACE (dropdown list text such as ROOM1, ROOM2 etc)
* COMPLETED (Yes/No with "No" predifined)
* COMMENT

So I want to enter the first appointment and then have the following ones with TIME, PLACE, and COMMENT pre-entered, as these will always be the same as the first one.

Can that be done in a simpler way that advanced VB?

Thanks in advance

 

[aian]

Anyone with any ideas on how I can do this, please?