Adding multiple records in Subform for one on Master Form

[SJWatsi]

Hello all - hopefully something easy to help with\solve!

I have a form and sub-form combination, and am using the subform to enter five different records for each record in the main form.

I would like the subform to automatically add 5 new records (numbered 1-5\A-E) when a new record is added on the main form, so that all the records will match up when they are being queried.

So far I have been able to write some (probably overly long) code that adds 5 new records when I press a button. But this is still contained on the subform.

I am struggling to implement the code on the main form. How can I refer to the subform from the first key field on the main form?

Hope someone can help. Hope this makes sense.

Cheers

SJWatsi

[SJWatsi]

Hello

Okay I've worked out part of this. I have worked out the code that will add add text to a subform as follows... (running from a command button)


Forms!tblStaff!tblSubform!txtletter.Value = "a"
Forms!tblStaff!tblSubform!txtnumber.Value = 1

I have two forms tblStaff and the subform tblSubform. For each new record in the tblStaff I want to automatically add 5 new records in the tblSubform. With entries A + 1, B + 2 etc...

I was thinking I could use DoCmd.GoToRecord , , acNewRec or DoCmd.GoToRecord ,"tblSubform" , acNewRec to move to a new record after writing a + 1, and so forth... But it won't allow that.

Originally I had the button contained on the subform and DoCmd.GoToRecord , , acNewRec worked there. So how can I move to the second record on the subform?


SJWatsi

[Trevor]

I don't understand A+1 B+2 etc.
is the A or B a Name ? and is the # 2 the number of blank records you want to add?
if so try forms![Mainformname]![SubformName].RecordCount = #

[SJWatsi]

Yes sorry. I realised I might not be making too much sense. Hey ho. I'll see if I can better describe the form & code.

The Forms: Two forms, one is a subform to the other, and are related by an ID number (Study_ID) in each table. The first form is basically just enter the ID number for the person and then their name and date.

The subform is continuous and has the ID number again and then three fields.
The first two fields are txtletter and txtnumber, where txtletter is either A,B or C and txtnumber is 1 or 2. The Study_ID (on the subform), txtletter and txtnumber make up the key on the subform\table. So the letters and numbers I (poorly) mentioned above are just the values that go into the textboxes, (which we use to identify and sort the records).
The last text box but that is left empty to be filled in later and isn't important to this situation.
Does that make sense? Maybe I should draw a picture.. :think:

For each ID (\person) on the master form, there should be a set number of records in the subform. It is a continuous form with all the records displayed underneath each other.
The first being: [the matching ID], A (txtletter), 1 (txtnumber), and an empty field.
The second:[the matching ID], B (txtletter), 1 (txtnumber), and an empty field.
The third: [the matching ID], B (txtletter), 2 (txtnumber), and an empty field.
Then two more with C (letter) - 1 (number) and C (letter) - 2 (number).

All five records on the subform should be added (preferably) automatically when the new record is added on the master form.

I found the code to write these values in on button press, as I included in the message below. That isn't a problem. When I press the button the ID is already placed in there because it matches with the ID main form. Then it adds in 'A' to the txtletter and '1' to the txtnumber fields.


Forms!tblStaff!tblSubform!txtletter.Value = "a"
Forms!tblStaff!tblSubform!txtnumber.Value = 1

The main issue is moving onto the next record on the subform (but the same record on the master form). So I can continue to write the code for the other 4 records as specified above.

I have tried: DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord ,"tblSubform" , acNewRec

Which was suggested on another website I was looking on, but niether seem to do anything. When I add the next lines after the 'gotorecord' (as below) it overwrites the previously entered lines.


Forms!tblStaff!tblSubform!txtletter.Value = "b"
Forms!tblStaff!tblSubform!txtnumber.Value = 1

So my question is... How do I add a second new record in the subform - or how do I move onto the next record when I have just added a record using VB?

I'm sure this should be easy. And I don't think I am making this any easier for me\you. :doh:

Thanks for looking, I really appreciate any help, I hope I've been able to explain myself better.. If not then I quit :rotlaugh: . Whoops, sorry for this being so long!

Cheers.

SJWatsi