How to add a new record on a sub form?

[lawdy]

I have a form (frmSpecialBilling) which has a sub form (sfSpecialBilling) on it. To add a new record on the main form, all I have to do is write the code - DoCmd.GoToRecord acDataForm, "frmSpecialBilling", acNewRec. That work great. However, it don't seem to work on the subform. I have 2 combo boxes that I used to select my choice of fields. I am able to add a new record to the sub form using a record set. I am wondering if it can be done easier like on the main form. Thanks in advance for your help.

[June7]

Why do you have a subform? What is nature of data on each form and how do they relate to each other? Have you set Master/Child links properties of the subform container control? Why do you need to use code to add record to subform as opposed to just doing data entry?

[Ruegen]

If you are trying to create a new record in the subform via a button and vba command - you could make a subform "header" in the subform and place a button with the same command? I am assuming you have the subform as continuous forms?

Another way would be to add a button on the main form which opens the subform records in a new "data entry" mode form which once saved will add a new record - then have a refresh list on the mainform once you have finished with the popup data entry form

just some ideas..

[lawdy]

You asked why I am using a subform. I thought that was the way to go. The main form shows a company name. Then the subform show each property for that company. They are all related. I am using code because the the 'properties' are in a master list and I want to select one, not type it in. When I say 'properties', i am speaking of a commercial building or a shopping center, not a computer property.

[ItsMe]

June is asking these questions because when you add a record to a subform you need to make sure that you maintain referential integrity, among other things.

You mention two comboboxes. You will probably want to transfer some data that these comboboxes have to the new record(s) within your subform. The code will need to include the names of the comboboxes, the column number that has the PK, the field name of the FK in the subform, and the subform's container name. You will need this info to start.

You will also have to determine if the subform's query is updateable. You may have to update tables directly, one at a time. (referential integrity)

You need to provide some info before June7, or anyone else can help.

[June7]

Suitability of that form/subform depends on table relationships. Each company can have many properties? Each property can associate with many companies?

[lawdy]

I have referential integrity. There has to be a record in the main form before a record can be added to the subform. It is just so simple to add a new record in a field on the main form with one line of code (DoCmd.GoToRecord acDataForm, "frmSpecialBilling", acNewRec). To add a new record on the subform, I use a recordSet. I thought there must be an easier way.

[June7]

That code moves pointer to a new record row. Record is committed to table (after at least one field has data) when form closes, move to another record, or run code to save, or set focus into the subform.

Do you want code to move the subform pointer to a new record row? Seems I've been through this question with another poster. Think code must first set focus on the subform then can use acNewRec.

[ItsMe]

To add a new record to the subform try...

DoCmd.GoToRecord acDataForm, "Forms!frmSpecialBilling.Form.sfSpecialBilling ", acNewRec

I still don't know what the subform container is named or what fields need to be populated and with what. This is assuming the container has the same name as the subform.

[lawdy]

I put the code DoCmd.GoToRecord acDataForm, "Forms!frmSpecialBilling.Form.sfSpecialBilling ", acNewRec behind the CBO's afterupdate. When I run it, I get the error "The object 'Forms!frmSpecialBilling.Form.sfSpecialBilling' isn't open. I do have referential integrity. There is data in the tables now. It properties stay with the proper company. As far as the name of the subform container, I am not sure about that. I made the subform and saved it as "sfSpecialBilling", then added it on the existing form 'frmSpecialBilling'. I have not changed the name of it since putting it on the form. As far as the field to populate, the table key 'fLngPropertyNo' along with the a field for the billing address (fStrBillingAddress) and similar field for the city, state and zip.

[June7]

See post # 8.

[ItsMe]

I tested that and it doesn't work. If the subform is updateable then this will work (Sort of). Long story short, if a subform is linked, you should not be using the Docmd to add records. You probably should not be adding records, period. It is a rare occasion where it would be beneficial to add a record in a subform with a Master - Child link.

Me.sfSpecialBilling.SetFocus
DoCmd.GoToRecord , , AcNewRec

[ItsMe]

Like what June7 said

[June7]

And it might be necessary to set the focus on a control of the subform, memory not clear and don't want to go through testing, seems I did that exercise already. Of course, can't find that thread again!

[lawdy]

I am really confused now. Are you saying not to add records to a subform where the subform is the child and the main form is the master? What is the form for if records cannot be added? For June7's post # 8 (Do you want code to move the subform pointer to a new record row?). The answer is yes. That is exactly what I am trying to do.