VBA Help

A

AJ

I am building a scheduling app, and have two tables for scheduling two
separate groups of people. However, I have to make a Master Schedule. I do
it now by querying both tables - one a "make table" query, and the other as
an append query.
Then I write a report off that new table. BUT how can I dummy-proof this so
the Master report can be update by the USERS during the scheduling process.

My question is, how do automate the following.

1. Delete the MasterSchedule table.
2. Run the make-table query off of Table1
3. Run the append query off of Table 2
4. Open the MasterSchedule Report.

(I just the VBA code. I have the queries all funtioning right now. I just
want to automate it, so it will work WITHOUT me.) Thanks in advance.
 
J

Jeff Boyce

This may not be a direct answer to what you posted...

It sounds like you've created separate tables to schedule two separate
categories of people. While this may be necessary if you were using a
spreadsheet, Access is a relational database.

First, what does having two tables allow you to do (i.e., why are you using
two)? I ask because it may be possible to use a single table, if the
underlying data you store for each category is the same (you would simply
add a "category" field to the table to show which category the person
belongs to).

Next, it isn't clear how your "make table" query helps you do ...?! Another
approach, if you need to clear out data and reload, would be to use a delete
query to empty a table, and an append query to reload it.
 
A

AJ

I appreciate your questions...
First, what does having two tables allow you to do (i.e., why are you
using
two)? I ask because it may be possible to use a single table, if the
underlying data you store for each category is the same (you would simply
add a "category" field to the table to show which category the person
belongs to).
Click to expand...

The two groups have quite a few differing attributes. One is a group of
music students (children) who need to be assessed on their instrument,
current and polished piece, reading level, options chosen, etc.
parent/guardian information also needs to be kept.
The other table the music teachers, who are coming for more training on how
to be better teachers. None of the above information is kept - just contact
info and registration info.
The tables look quite a bit different.
Next, it isn't clear how your "make table" query helps you do ...?!
Another
approach, if you need to clear out data and reload, would be to use a
delete
query to empty a table, and an append query to reload it.
Click to expand...

Very good point. I think your suggestions would work fine.

Now, do you have any reccomendations on hwo to write a function to execute
it all?
 
J

Jeff Boyce

AJ

Once you've written the queries that do what you want, there are a couple
approaches to consider.

If you are not comfortable with writing VBA, you could create a macro and
run your queries from within the macro.

If you are comfortable creating a procedure, you can either run the queries
or their underlying SQL statements in the procedure. If you wish to have a
way to "launch" the procedure, add a button to the relevant form and add
your procedure to the OnClick event of that command button.
 
Top