Add Record with Subforms (1 Viewer)

[jsmith1611]

I have a parent form with two levels of child forms. I have set all forms to default to AllowEdits = False. When I click an Edit button on the main form, it sets the AllowEdits to True for itself and both child forms like this:

Private Sub editRec_Click()
Me.AllowEdits = True
Me![type].Form.AllowEdits = True
Me![type].Form![Sequence].Form.AllowEdits = True

End Sub

This all works fine. I have a save button that also resets the AllowEdits back to False for the parent and all child forms like this:

Private Sub saveRec_Click()
On Error GoTo Err_saveRec_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Record Saved."
Me.AllowEdits = False
Me![type].Form.AllowEdits = False
Me![type].Form![Sequence].Form.AllowEdits = False

Exit_saveRec_Click:
Exit Sub

Err_saveRec_Click:
MsgBox Err.Description
Resume Exit_saveRec_Click

End Sub

This also works fine. The problem I am experiencing is when I click the addRec button for the parent form to add a new record. I can add a new parent record, but the child records are not available. When I click the addRec button, a message says “You can’t go to the specified record.” Here is the code for addRec on the parent form:

Private Sub addRec_Click()
On Error GoTo Err_addRec_Click

DoCmd.GoToRecord , , acNewRec
Me.tapeNum.SetFocus

Exit_addRec_Click:
Exit Sub

Err_addRec_Click:
MsgBox Err.Description
Resume Exit_addRec_Click

End Sub

When I click OK, I can add the parent record, but I still can’t see the child records or add a child record. This all worked fine before I made the change to the forms not allowing edits.

I have tried calling the addRec procedure for the subform, but no success there either. What am I missing here?

Thanks,

Josh

 

[MarkK]

What I would do is add a new record to the table, requery the form, and then navigate to the new record. You can write an insert query or open a recordset and use AddNew. Here's the recordset method, which simplifies the determination of the new ID.

private sub AddNewAndFind()
  dim newID as long
  dim rst as dao.recordset
[COLOR="Green"]  'add a record[/COLOR]
  set rst = currentdb.openrecordset( _
    "SELECT ID, Field1, Field2 " & _
    "FROM tTable;")  "
  with rst
    .addnew
    !Field1 = "< new record >"
    newID = !ID  [COLOR="Green"]'new ID here[/COLOR]
    .update
    .close
  end with

[COLOR="Green"]  'requery the form[/COLOR]
  me.requery

[COLOR="Green"]  'navigate to the new record[/COLOR]
  with me.recordsetclone
    .findfirst "ID = " & newID
    if not .nomatch then me.bookmark = .bookmark
  end with
end sub

Using this approach you can apply whatever business logic you need to, and have explicit control over the process, for instance you can add subform records here too. Also, you don't need to worry about whether the form allows edits.

 

[missinglinq]

In order to be able to add a new record to the child forms, you have to also set the AllowEditions on the Main Form to Yes. The subforms are actually controls on the Main Form, and if the Main Form's AllowEdits is set to No you can't edit the subform control, which is how Access looks at it when you try to add a new record to them.

 

[jsmith1611]

Thank you for all of your help. Setting AllowAdditions was all I needed to solve the problem. I just added it to my addRecord code from the main form.

 

[seopositive2]

First off let me say there is some terrific info here on this site, great job! Also, let me state that I'm designing my first database so I'll likely be asking some really stupid questions.

I'm designing a database which will allow customer work orders to be entered. The main form is pretty big but it requires a lot of info. Eventually I want to add a search option that will allow the user to search for all records for a specific customer, or address.

Anyway, I had an Add Record button working but it was causing other problems so I had somebody help me out but now I have problems with what they added to the add record button. If some of the required fields are not filled out there is no action when the add record button is pushed. I was getting into an endless loop because the "Resume" command was in the code so I took it out. Can anybody help me with this please?

Here's the code on the Add Record button:

Private Sub addnew_Click()

On Error GoTo Err_addnew_Click

DoCmd.GoToRecord , , acNewRec

Exit_addnew_Click:

MsgBox "customer added", vbOKOnly, App.Title
Exit Sub

Err_addnew_Click:
MsgBox Err.Description
Exit_addnew_Click

End Sub