macro to copy a record from a form and paste it into the next reco

[fred]

I use a work order database form to input data. Many work orders have
multiple tasks for the same WO number, property ID, address etc. Currently, I
enter a new work order filling in all fields. If there are multiple tasks, I
go to left of form, click copy, go to bottom of form and click the next
button, then go back to left of form and click paste, then I change just the
fields I need to new for the new task. I wish Access had a record macro
function like I use in Excel. Please help. Thanks, Fred.

 

[Steve Schapel]

Fred,

There are a few ways you could go about this in Access.

One possibility that might work well in your scenario here, is to use a
macro with SetValue actions to set the value of the Default Value
property of the relevant fields to the same as the data entered to the
existing record.

Another option would be the use an macro with an OpenQuerty action to
run an Append Query to insert a new record into the table that the from
is based on, with the same values as the existing record.

Can I just ask.... Does it also happen in your database that there can
be multiple tasks for the same WO that are for *different* Propertiy IDs
and Addresses within the same Work Order? Or are they always the same
Property ID and Address within a given Work Order?

 

[fred]

Steve,
Thanks for the input. All jobs on a single work order all have the same
receive date, work order number, case number, address, city, state, and zip.
The differences that I update for multiple tasks at a property are: task
description, task price, who assigned to, and for how much. Once they
complete the work, I go back in and insert a completion date and invoice
number. The job then goes to my invoice query where I transfer it to a
company invoice and it also goes to my 'unpaid labor' query, which I use to
keep track of workers I owe and how much and when I need to pay them. I was
hoping to find an easy way to automate this recurring task. Some work orders
may have 1-10 tasks on them which must be tracked seperately, mainly for
invoiceing purposes. Thanks, Fred.

 

[Steve Schapel]

Fred,

What you have indicated here is that there is a fundamental flaw in your
database design.

This data should not be repeated for every task. That is the primary
point of using a relational database such as Access.

Is your system set in concrete at this stage? Or could you consider a
review?

On the basis of what you have told me, there should be two tables to
manage your work order data, something like this (skeleton plan!)...

Table: WorkOrders
WO Number (primary key)
ReceiveDate
CaseNumber
Address

Table: Tasks
TaskID (primary key)
WO Number (to relate to WorkOrders table)
Description
Price
AssignedTo
HowMuch

When you are entering your data, you would probably plan to have a
Single view main form based on the WorkOrderrs table, and a Continuous
view subfrom based on the Tasks table.

Might seem more complicated to set up initally, compared to a
spreadsheet-like approach, but believe me it will save you truckloads of
work later if you get it right from the start.

 

[fred]

Steve,
I thought about that when I transferred over from Excel but I wasn't sure
how to deal with how the government issues me work orders. The case number
and address are always unique. The work order number changes every time a new
work order is assigned. The tasks on the work order must be processed and
invoiced per the work order number. When I query a case number for a property
address, I may have 30 work order numbers, each with 1-10 tasks.
Subsequently, when they pay me, they pay by the WO number. I would be glad to
send you the two main forms and or tables that I use. PS: I actually use 2
different tables since the second table is used to manage the RM (routine
maintenance) tasks. They handle it completely different than work orders.
Basically, we do RM's at 161 properties twice a month, at least 12 days
apart, put together a huge spreadsheet and bill all RM's twice each month.
Naturally, the database is always changing becasue properties get sold, and
new repossessions come into the system. That's why I need one databse to talk
to the other. When a new property comes into the system, they always order an
intial services work order which is my signal to load it into the RM database
and begin doing RM's 12 days after I get the initial done. Make sense? I
really do appreciate your help. Thanks, Fred.

 

[Steve Schapel]

Fred,

Thanks for the further information.

In my opinion, this calls for a pause and step back, try and get your
data model right. I realise that this may not be easy, given that the
existing system is in actual use right now, with live data that is being
worked on. But ideally that's what's needed.

You need the analyse the nature of the real-life relationships between
the data elements.

For example, there is a one-to-many relationship between Cases and Work
Orders. As such, there must be two tables to represent this data. In
this case, a Cases table and a WorkOrders table. And then there is a
one-to-many relationship between Work Orders and Tasks. So there goes
another table. Well that's 3 altogether now. Similarly, there's a
one-to-many relationship between Properties and RMs, and also a
one-to-many relationship between Properties and Owners. And so on. How
this gets analysed into your invoicing is another question... on the
basis of the little I know, I would imagine that a single invoicint
process should be capable of managing both WOs and RMs.

It will definitely be a huge advantage to get this right, from the point
of view of the future effectiveness of your database, and also from the
point of view of your paracetomol budget. But it is not a trivial
project, it will take tima and effort, you may need to read up on
normalisation rules and database design, etc. You will certainly get
help with specisic questions in the newsgroups, but total guidance with
the whole structure is probably beyond the scope of newsgroup help.