- Joined
- Nov 17, 2006
- Messages
- 4
- Reaction score
- 0
Hi, my visual basic skills are pretty limited but I'm trying to create a relatively simple macro or module to create new records.
I have three tables. Table A is the master table and tables B and C are both related to table a by foreign keys.
Table A is simply a list of names and dates. Tables B and C carry data for each name-date in table A.
What I'm trying to do is simple:
Copy the last record in table A and add 5 additional records with interger multiples of one month added to the date. I'm not really sure what is the best way to add one month to the date either (and actually, dates only need to be accurate to the month. to my knowlege access doesn't trunkate dates in month/year format so I'm keeping everything on the first day of the month. . . please correct me if there's a better way).
so if the last record in table A is:
Primary key: 1 (this is autonumbered)
Name: john, jacob
Date: 01-jan-7
then I would like to add 5 additional records
Primary keys: 2,3,4,5,6
name: john, jacob
dates: 01-feb-2007 , 01-mar-2007, 01-apr-2007, 01-may-2007, 01-jun-2007
in addition, I would like to have all of the records in tables B and C that correspond to the original name-date (primary key =1 in the example).
to also be copied exactly and made to correspond to the newly created 5 entries in table A.
example(table B has two tasks for a given name-date):
Table B:
Primary key:1
Foreign key : 1
task: fix control lever
requirements: standard nuts, bolts, and wrenches
specifications: N/A
comments: This lever operates an important machine
primary key: 2
foreign key: 1
task: replace rope on pully system
requirments: 1/2 nylon rope.
specifications: 30 ft. length
comments: currently rope is fraying. Dangerous.
I would like to copy both of these rows 5 times and link them to the 5 new entries in Table A:
Table B:
Primary key:1
Foreign key : 2, 3, 4, 5, 6
task: fix control lever
requirements: standard nuts, bolts, and wrenches
specifications: N/A
comments: This lever operates an important machine
primary key: 2
foreign key: 2, 3, 4, 5, 6
task: replace rope on pully system
requirments: 1/2 nylon rope.
specifications: 30 ft. length
comments: currently rope is fraying. Dangerous.
Now I do have a form for entering all of this and keeping track of the relationships. the sub form enters all of the data for tables B and C
perhaps there's a way to copy all of the data on the subform, create the 5 new name-date entries in table A, and then paste the data into the subform for each of the new 5 name-dates.
I know that it shouldn't be too difficult to do all of this. Its nothing terribly fancy but I'm having trouble getting started with it and I don't think that macros are very effective for record level editing/cutting/pasting.
Any help would be greatly appreciated.
Thank you.
Eager beginner
I have three tables. Table A is the master table and tables B and C are both related to table a by foreign keys.
Table A is simply a list of names and dates. Tables B and C carry data for each name-date in table A.
What I'm trying to do is simple:
Copy the last record in table A and add 5 additional records with interger multiples of one month added to the date. I'm not really sure what is the best way to add one month to the date either (and actually, dates only need to be accurate to the month. to my knowlege access doesn't trunkate dates in month/year format so I'm keeping everything on the first day of the month. . . please correct me if there's a better way).
so if the last record in table A is:
Primary key: 1 (this is autonumbered)
Name: john, jacob
Date: 01-jan-7
then I would like to add 5 additional records
Primary keys: 2,3,4,5,6
name: john, jacob
dates: 01-feb-2007 , 01-mar-2007, 01-apr-2007, 01-may-2007, 01-jun-2007
in addition, I would like to have all of the records in tables B and C that correspond to the original name-date (primary key =1 in the example).
to also be copied exactly and made to correspond to the newly created 5 entries in table A.
example(table B has two tasks for a given name-date):
Table B:
Primary key:1
Foreign key : 1
task: fix control lever
requirements: standard nuts, bolts, and wrenches
specifications: N/A
comments: This lever operates an important machine
primary key: 2
foreign key: 1
task: replace rope on pully system
requirments: 1/2 nylon rope.
specifications: 30 ft. length
comments: currently rope is fraying. Dangerous.
I would like to copy both of these rows 5 times and link them to the 5 new entries in Table A:
Table B:
Primary key:1
Foreign key : 2, 3, 4, 5, 6
task: fix control lever
requirements: standard nuts, bolts, and wrenches
specifications: N/A
comments: This lever operates an important machine
primary key: 2
foreign key: 2, 3, 4, 5, 6
task: replace rope on pully system
requirments: 1/2 nylon rope.
specifications: 30 ft. length
comments: currently rope is fraying. Dangerous.
Now I do have a form for entering all of this and keeping track of the relationships. the sub form enters all of the data for tables B and C
perhaps there's a way to copy all of the data on the subform, create the 5 new name-date entries in table A, and then paste the data into the subform for each of the new 5 name-dates.
I know that it shouldn't be too difficult to do all of this. Its nothing terribly fancy but I'm having trouble getting started with it and I don't think that macros are very effective for record level editing/cutting/pasting.
Any help would be greatly appreciated.
Thank you.
Eager beginner