Auto Create Records

[James Frater]

Hello Gang,

I look after events and would like to have a facility that would allow me to
generate recurring bookings, without having to enter each one manually.

For example, I have a Team that's booked the Rugby Pitch every Sunday for 8
weeks from dd/mm/yyyy to dd/mm/yyyy between 1000 and 1300. So Ideally I'd
loved to enter the start date, end date, day, venue, start time and end time
in a form and through a _click() event create those records.

I really don't expect a comprehensive piece of code, although that would be
mega (well I had to ask ;-) tee hee) but any pointers of where to look or
resources to learn about this would be great,

As always, thanks in advance chaps.

JAMES

 

[Mr B]

James,

I don't have specific code for doing what you want, but here are a couple of
thoughts that might just help get you kicked off in the right direction.

First, I assume from your posting that you are currently use a form where
your fields are bound to the controls where you can provide the data for the
booking. If you are going to try to develop the solution that you have
described, you will need to develope a form where the fields are not bound to
the fields in your table.

In this new form, you can place a control where you can enter the starting
data. Then you might consider a control where you would enter the number of
of bookings, then a list box where multiple days of the week could be
selected and a combo box where the venue can be selected.

Then you could use VBA code to process the selections made by users to add a
record for each booking. The DateAdd function would be helpful in
determining the dates on which each of the bookings needs to be made.

 

[Evi]

This isn't a code but you could create macro to run this query if you want.

Out comes Dale Fye's Number table yet again! For detailed instructions how
to create it, Google
microsoft . public . access . queries
for

"Create Date Records"

(Dale, have you got it anywhere on a website somewhere with a
beginner-friendly step by step guide ?)

Create the number table and date query as described - we'll call the
datefield AllDates
Put the date query into another query and add intNumber and Alldates to the
grid.
In query design view, in an empty column next to AllDates type
WkDays:Weekday(AllDates + intNumber],2) to get the days of the weeks for
the date. (Monday is 1, Tuesday is 2 etc)

In the next column put

Interval:1

(you will be changing this number as required)
in the next column put
EndDate: AllDates + Interval
Type in the times into the query

StartTime:10:00
EndTime:14:00

Filter the AllDates field so you get the start and end dates for your range
of appointments
Filter the WkDays field (so if you want the event to be every Monday, type 1
under it

For every day except Saturday and Sunday, filter using <>6 AND <>7


Change the query into an Append query and Append the dates to your
Appointments table

For real classiness, you could do the filtering via a form using an unbound
form and 2 combo boxes which use unfiltered date query for their data and
text boxes to type in the Weekday


Evi