 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Macros / June 2005Creating Multiple Records in a Table
I have created a form (called ADD RECORD) that will be used for doing 2 things when a command button is pressed. The fields on the form are: Job Title, Schedule, Shift, Start Week, End Week, Head Count#, ST, OT and DT The two things the command button will do are: 1) Creating one record in one table (called HEADCOUNT) and enter values from textboxes on the form into the table. The fields needed by this table are Job Title, Schedule, Shift, Start Week, End Week, Head Count#. 2) The command button should also immediately create multiple records in another table (HEADCOUNT_EXTRA). The required textboxes by this table (from the form) are ST, DT and OT. The table Headcount_extra has only 3 fields ST, OT and DT. Note that the number of multiple records required to be added to the table, HEADCOUNT_EXTRA, will depend on the values types in the textboxes as shown in the equation below: [END WEEK] - [START WEEK] +1 e.g. 52 records will be created if you evaluate 52-1+1 Can anybody help develop an event procedure that will do this (or tell me macros that can do this). I only use macros in access and I am not savvy enough to write event procedures from scratch. Pele I saw your post on Friday in modulesdaovba and didn't respond because, frankly, it didn't make sense from a table design perspective. 1) The first part of your request takes care of itself if your form is bound to Headcount (or, more properly, a query based on Headcount). > 2) The command button should also immediately create multiple records in > another table (HEADCOUNT_EXTRA). The required textboxes by this table > (from > the form) are ST, DT and OT. The table Headcount_extra has only 3 fields > ST, OT and DT. 2A) There is no connection between Headcount_Extra and Headcount ?!? Are you sure you won't want to join these tables later on JobTitle or something? 2B) You want to create up to 52 100% identical records?!? Are you sure you don't want these records to include a WeekNumber (i.e., 1 to 52)? as well as JobTitle? Unfortunately, I don't write macros, only vba, so maybe someone else can help you on that score, but I think you need to re-think what you are asking, possibly starting with the basic concepts of table design and structure in a relational database. I generally try to answer the question a poster asks without second guessing them too much. However, in this case, even if I told you how to create 52 duplicate records at the push of a button, with the design you've laid out I don't see how you would possibly be able to do anything with them. That would be a waste of time on both of our parts. Maybe someone else will see it differently. HTH & Good Luck,  SignatureGeorge Nicholson Remove 'Junk' from return address. >I have created a form (called ADD RECORD) that will be used for doing 2 > things when a command button is pressed. The fields on the form are: [quoted text clipped - 29 lines] > > Pele Gerge, You are very correct with your comments and I sincerely apologize for posting such an inadequately worded question. Below is my up to date question. I have no problem at all if you use VBA to solve this problem since I am not even sure macros can do this. NEW QUESTION I have created a form (called ADD RECORD) that will be used for doing 2 things when a command button is pressed. The fields on the form are: HC_ID (autonumber), Job Title, Schedule, Shift, Start Week, End Week, Head Count#, ST, OT and DT The two things the command button will do are: 1) Creating one record in one table (called HEADCOUNT) and enter values from textboxes on the form into the table. The newly created field uses Access autonumbering for the field called HC_ID. The fields needed by this table are HC_ID, Job Title, Schedule, Shift, Start Week, End Week, Head Count#. 2) The command button should also immediately create multiple records in another table (HEADCOUNT_EXTRA). The link between the two tables is HC_ID. That is, the button needs to use the HC_ID created by Access in table HEADCOUNT as a field in HEADCOUNT_EXTRA. That is, each record created will share the same HC_ID. The table HEADCOUNT_EXTRA also has another field called WEEK which keeps track of the number of weeks encompassed by the recordss created. The first record will have WEEK = Start Week entered on the form and the last record will have WEEK=End Week. Week increases from Start WEek to ENd Week. Also, the numbers entered in ST, OT and DT on the form are then repeated for each record created in the table called HEADCOUNT_EXTRA. The required textboxes by this table (from the form) are HC_ID, WEEK, ST, DT and OT. The table Headcount_extra has 5 fields. Note that the number of multiple records required to be added to the table, HEADCOUNT_EXTRA, will depend on the values typed in the textboxes as shown in the equation below: [END WEEK] - [START WEEK] +1 e.g. 52 records will be created if you evaluate 52-1+1 Week will start from 1 to 52 and HC_ID is repeated for all 52 records. Can anybody help develop an event procedure that will do this (or tell me macros that can do this). I only use macros in access and I am not savvy enough to write event procedures from scratch. Pele > I saw your post on Friday in modulesdaovba and didn't respond because, > frankly, it didn't make sense from a table design perspective. [quoted text clipped - 61 lines] > > > > Pele |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.