Create a record in another table from data on a form

[Najemikon]

Hi,

I thought this would be a doddle! How wrong am I or am I just missing something obvious?

I've developed a database over the past few years for tracking engineer jobs: there's a table for site visits & one for telephone calls, which have to be timed. Both have worked well for some time. There is no relation between the two, although they have similar fields.

We now want any phone operator to be able to open an incident for a client & then an engineer can look at the list of open incidents & attach a phone call or site visit.

So I've added a third table called Incidents. Its unique autonumber can be passed to a related field in the other tables, so a resultant query can group multiple jobs under one incident & add up the total time. This works so far, I just need the query.

To make it smoother though, I want buttons on the Incident form that can create new records in the other tables; for example, engineer opens an existing incident, that already has a description & the all important reference number. He clicks a button to create a job & it copies those fields & others to a new record in the other table.

You see, the tables match in terms of fields like 'description' & 'incident number'. Surely I can get them to duplicate across tables?

Any ideas you might have, I'd be very grateful for!

 

[PHV]

Take a look here:

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Najemikon]

Thanks for the link, it is a good article. However, it is the approach I've been taking for some time & I am comfortable with relational databases. I always start on paper & work out the natural paths for the data & what reports I'll need. It's a good method.

This is slightly different though; it started as two different databases. They were so similar, I brought the two together to aid reporting. It isn't an ideal design to start with now & I've been seriously tempted to start again.

This is because our overall approach has changed dramatically. When I designed the seperate databases it was simply a convenience, now we want to bring in a much more powerful tracking element.

But something tells me I have everything I need! The tables are similar, I've added a third to create the reference & it works. I've created a button on the Incidents form & it filters the jobs in the other form to that current Incident ID; I just can't get it to create a new record from that data & don't know how to start!

 

[Najemikon]

Just wanted to update this thread as I've stumbled over a method & wondered if it was the best method!

I've managed to get this working attaching something like the following code to a button on the Incidents form (I'm just showing the essential part here):

Set rstTypes = CurrentDb.OpenRecordset("JobsTable")
rstTypes.AddNew
rstTypes!incNumber = Me.incNumber
rstTypes!Reason = Me.Description
rstTypes!JobTitle = Me.incTitle
rstTypes!CustomerNumber = Me.CustomerNumber
rstTypes!Engineer = Me.incEngineer
rstTypes!DateStarted = Me.incDate
rstTypes.Update

I haven't worked too much with recordsets myself. Have I got the right idea here? It does work, so it's probably good enough, but I'd appreciate any comments in case there's something I've missed.