Copy table and form not contents

R

ReadReadWrite

Every year I copy our invoice table and form (without the data) and rename it
with the current year, so that our Main Switchboard reads, "1999 Invoices,
2000 Invoices," etc., through 2005. (She doesn't want all years dumped
together for searching purposes) I've been the one to start the new table
and form for the past couple of years. Trouble is, I can't remember how I
did it. I know it was simple--there was even an option to click on. Anybody
know what I'm talking about?
Thanks,
Glen
 
E

Ed Robichaud

You copy the table (Ctl-C, Ctl-V; or Edit-Copy, Paste) and select the "copy
structure only" option. Open your existing form (or new copy) in design
mode and change the record source to your new table/query.

As no doubt many others will tell you, this is not a good data design. How
do you show/print an invoice history for each client? Presumably by a query
that links all YearXX tables, but what a mess. A single tblInvoices (with a
date/time field) can easily be searched and/or filtered to show only the
desired date range, client, product, etc. That's what queries are for; and
your form(s) could (and probably should) have a query as record source).

If you fix this now, you'll skip a lot of problems later. There is the
"Northwinds" sample database and an invoicing template shipped with Access.
Look at either one for data structure examples.
-Ed
 
R

ReadReadWrite

When I learn more about queries and filtering, maybe. In the meantime,
this is a simple accounts-payable setup, and we've never needed to run
reports from it (unlike our periodicals routing or catalog databases).
They've been doing it this way (copying the empty form and table each year)
since before I started here.
Anyhow, when I copy I don't get a "copy structure only" option at all.
What am I doing wrong?
Thanks,
Glen
 
E

Ed Robichaud

Go to the database window (press F11), select the table you wish to copy,
press Ctl-C, then Ctl-V. You'll get a dialog box, select "structure only"
and name the new table.

Not sure what you mean by "copying the empty form" .
Each form that has a record source (as all bound forms do) will show blank
if there are no records. You can copy forms (like any other object) do the
same copy and paste routine as above. Assuming you want this new form to
show records from your new table, you'll need to set its record source
property accordingly.

And just because they've always done it wrong, it not a great argument to
continue. I'm also curious why they have separate databases for catalogs,
etc. Wouldn't it be nice to have one central list of customers and their
related payments, shipped document, etc.?
-Ed


forms
 
R

ReadReadWrite

I should have said "copying the form and empty table" instead of "copying the
empty form and table."
This is the library of a lawfirm, so we don't have paying customers.
I should have mentioned--I ain't in charge here! I'll eventually make
everything more efficient, but I want to be well prepared before I propose
each step along the way. At this point I'm still learning the basics.
Here's what our different Access databases are for:
1. The catalog I mentioned is a library catalog.
2. The periodicals routing database is for tracking the periodicals we
receive on a regular basis and making sure they're sent to the right
attorneys (using a report we call a "routing slip".)
3. Interlibrary Loans (recently added) tracks the books we borrow from other
libraries and firms to make sure the attorneys get them back to us so that we
can return them on time, and so that we know where to find the title quickly
next time we need to borrow it.
4. Invoices is accounts payable--books purchased, print and online
subscriptions we pay for, etc.. It includes the info on the invoices we
receive and the checks accounting sends out. We have a Main Switchboard set
up so my supervisor can click on the year she wants to see.
 
Top