Add multiple records to same table from subform

[1gixxer]

Ok where to begin,

I’ve only started using Access 2010 since the beginning of January and have google’d almost every problem to date. This forum has been a gold mine of help but after days of searching I cannot find a solution for this problem .

So I’m hoping you guys can help.

Some background

My db is used to show the status of material through a manufacturing process.
We start with one slab of material which gets cut up in to many parts.

The db works so far however I’ve come to the conclusion that the date was not normalized correctly. So I’ve created the following tables to fix this.

I have two tables the first of which holds data on the slab we start with and the second shows the status of the parts it has been cut up into.


Table 1 has the following fields

Cast Number
P/O
UST Status


UST Comment

Table 2 has the following fields

Cast Number
Blade ID (which will be 1 to 32)
Status
Comment

I would like table one to be displayed on a form with all fields. Have table two as a subform on the main form. But here is where I get lost!

I need the subform to show 32 text boxes to represent each part. Have each box assigned a default blade ID (1 to 32). Then depending which of the 32 text boxes get used to update those multiple records within Table 2.

I did have 32 status fields for every part but realised that meany were left null. Am I even going down the right road here?

I'm using, Access 2010 with Win 7


I hope that makes sense... I'd appreciate any and all help please.

[alcapps]

Cast number
bladeid

Event after insert

dim db as database
set db = currentdb()
for i = 1 to 32
Db.execute "INSERT INTO Cast number " _ & "(Cast number,Bladeid) VALUES " _
& "(" & me.castnumber & "," & i & ");", dbfailonerror


next

me.subformname.requery

you can tweak this and it should do what your trying to do.

Answered. On an iPad

hope this helps

[1gixxer]

Trent dB.zip

I’ve attached a mock db which should help explain what I’m trying to achieve


alcapps - Thank you, Ill give your script a whirll and let you know how i get on.

[1gixxer]

Ok, I’m a little unsure what object to apply the "Event after insert" code to.

I have to admit my knowledge of VBA is not the best.

I had imagined that each text box would require an after update or before update script that first defines its associated blade ID. Then write that ID to the next record within the BladesStatus Table along with the date entered within the text box.

Thank you so much for any help.

[alcapps]

Trent dB.zip

ok I had to change things around. You had a few things out of place. Here is an example of what you can do.

try not to add blanks to the field names it makes code and querys get cluttered up trying to handle the special characters.

[1gixxer]

alcapps...... you is da bomb!

Thank you sooooo much!!!!

Below is the code used in case it helps any one else.

Private Sub Form_AfterInsert()
Dim db As Database
Set db = CurrentDb()
For i = 1 To 32
db.Execute "INSERT INTO BladeStatus " & "([Castnumber],[Bladeid]) VALUES " & "('" & Me.[Cast Number] & "'," & i & ");", dbFailOnError
Next
Me.frmBladeStatus.Form.RecordSource = "SELECT bladestatus.castnumber, bladestatus.bladeid, bladestatus.Status, bladestatus.Comment FROM bladestatus where CastNumber ='" & Me.[Cast Number] & "'"
Me.frmBladeStatus.Requery
End Sub

Private Sub Form_Current()
Me.frmBladeStatus.Form.RecordSource = "SELECT bladestatus.castnumber, bladestatus.bladeid, bladestatus.Status, bladestatus.Comment FROM bladestatus where CastNumber ='" & Me.[Cast Number] & "'"
Me.frmBladeStatus.Requery
End Sub

Thank you again alcapps.