Creating Multiple Records from One Form

[posterman]

Hello,
I am working on updating an Access 2003 database using Access 2007 and have a question about auto creating new records from a form.
The current form (frmMainForm) includes the following fields and creates records in the table (tblMainTable)
Inspection Date
Part Number
Serial Number
Revision
Assembler
2nd Party Check
Inspected By
Pass/Fail
Re-Inspection
Reason for Failure/Code
What I would like to be able to do is fill out the form as is, except be able to enter a range of serial numbers such as (starting serial # and ending serial #) and when the form is updated have it create new records in the tblMainTable for each of the serial numbers in the range using the information from the frmMainForm.
The serial number format is 0908W001 where 09 is the current month, 08 is the current year, W indicates the product line, and 001 is a sequential number from 0-999. The only part that needs to be incremented is the 001.
0908W001
0908W002
0908W003
etc…
Any help would be greatly appreciated.
Thanks in advance.

 

[dkinley]

I've attached a demo that should work for you ...

I am going to point out the important bit. I have set the counter to automatically copy all fields after the 4th field as noted ..

...
For iCount = 4 To rstDest.Fields.Count - 1
...

Any fields in the table prior to this won't be copied. I didn't know if you wanted to store this data or have them in unbound text boxes. By ordering the fields in this way, it automatically cycles through the fields.

I didn't do the special serial number header bit "0908W", I just made a control to hold this. You should be able to set this up using 3 text controls with so that a FormatDate will autopopulate the first two and then another third that is a manual entry.

Hope that helps.

-dK

 

[posterman]

Thanks for the quick reply, but I don't see the attachment.

 

[dkinley]

Sorry, I accidently hit submit and went back and edited the post.

-dK

 

[posterman]

dk, once again thanks for the quick reply.

With a little tweeking to fit my application your solution works great and does just what I was looking for.

Thanks,

posterman

 

[dkinley]

Good to go! Glad I could contribute. =]

-dK