copy data from one table to another

[SueBK]

Let's see if I can explain this without doing my head (or yours) in. I have two tables -
tblStatusAspects is a list of 10 aspects (with an autonumber primary key field)
RAPtblStatus is the status (and other info) of those 10 aspects for the month.

I am trying to create a command button on my form that goes to tblStatusAspects and copies the 10 aspects into new records in the RAPtblStatus. I madly copied the code below on Friday from a very brief session with a very overworked Access guru, and it don't work :-(

I can go back and hassle him, but I'd actually really like to understand what I'm doing. I'm a little confused because (to me) the code seems only really be referring to one of the tables. If the form I'm in is linked to RAPtblStatus - then should I actually be opening the other table (the list I want to copy from)?

The code creates a new record, but doesn't put any info in it. The error message highlights "addnew".

Me.AllowAdditions = True
Set db = CurrentDb()
Set tblAsp = db.OpenRecordset("RAPtblStatus", dbOpenSnapshot)
tblAsp.MoveFirst
Do While Not tblAsp.EOF
With tblAsp
.AddNew
.RAPtblstatus.[ProjectAspect] = tblStatusAspects.[ProjectAspect]
.Update
End With
Loop
Me.AllowAdditions = False
Me.Command17.Enabled = False

 

[Galaxiom]

It is easier to read a description if you use unambiguous terminology.
Although it can be determined by reading a couple of times "a list of ten aspects" could mean ten records or ten fields. A list is something you find in a list box.

Copying records straight from one table to another usually suggests you should revise the structure of the database so the second location is related to the first rather than duplicating information.

 

[SueBK]

Gotta love when terminology and real life clash

In "tblStatusAspects" I have 10 records, two fields - AspectID and ProjectAspect. This table is used by several other tables/forms/reports, including the one I'm interested in.

Every month the user opens a form (RAPfrmStatus) linked to the table "RAPtblStatus". The command button to open the form filters the data available against a specified monthly report. The user then updates information about each aspect for that month.

I wish to add a command button at the top of the form. When clicked it creates 10 new records, and populates the "ProjectAspect" field with the information in the ProjectAspect field of "tblStatusAspects".

For example; my first three aspects (three records in tblStatusAspects) are:
ID Aspect
1. Overall
2. Financial
3. Risk

I need to create the June monthly report. I open "RAPfrmStatus", which has no records showing, because nothing has been created for June yet. I click the command button, and I now have three records:
ID Aspect Status Comment etc
1. Overall
2. Financial
3. Risk

Having created my aspect records for this month I can now update the rest of the information.

Hope that's a bit clearer.

 

[Galaxiom]

You definitely should restructure your database.

Presumably each Aspect has multiple properties or you would already have the Aspects as fields in the Monthly report table with a value for each entered into the record. Lets call this table tblMthreport.

These properties should be fields in another table, say tblPrptyValues with fields including a foreign key for tblMthReport so the values can be related to a particular month. Another foreign key field would be the AspectID and then the fields for each of the range of property values.

If the Aspects all have the same property types then it is simple to name these fields as such. Otherwise call them Property1, Property2 etc.

In this case another table, say tblProperties will have fields for PropertyID (1,2,3 etc), AspectID and PropertyName. You use this table to retreive the name of the property as appropriate for the particular Aspect you will be displaying. You might also add other information about the properties in this table, such as who is responsible etc.

To make the report you retreive the set of values from tblPrptyValues with the foreign key that matches the month you are reporting.

Making a new report is simply displaying the fields in tblPrptyValues and you are not having to create multiple records before you can start.

Hope this makes sense. No doubt you couold come up with better names for the tables.

 

[Galaxiom]

Sorry if I have confused you. I should have read your post more carefully the first time.

I can see that what I have described is close to what you have. The main difference is that you appear to be using the name of the Aspect in RAPtblStatus where you would be better using the AspectID.