We help IT Professionals succeed at work.

MS Access - VBA to open a form in data entry mode

kevin1478 asked
I have a table that contains a customers details and 2 addresses (all in 1 table). The 1st address is a home address and the 2nd a business address.

I have a form based on this table. The form has a VBA button to open a 2nd form so that an address can be entered. The 2nd form opened could be 1 of 2 forms, depending on the entry in a tick box which identifies the address type.

I need to change the VBA so that the 2nd form will enter details in the same record as the 1st form.  For instance, at the moment if the cust_id on the main form is 12 then the 2nd form will enter details at record 13, not 12 as required.  This is because the 2nd form is in data entry mode.  But if I remove the acFormAdd the 2nd form opens with the previous data and not blank.

How can I open the 2nd form so that it will enter into the same record as the first but the form will 2nd form will open blank?

The actual problem is a little more complex but if I can understand a simplified version I will then apply it.  I learn better that was rather than cut/past a given answer.

Any help is appreciated.

Private Sub Command18_Click()
On Error GoTo Err_Command1_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    If Me.business = True Then stDocName = "frm_bus_add" Else stDocName = "frm_cust_add"
    DoCmd.OpenForm stDocName, , , acFormAdd, , , stLinkCriteria
    Exit Sub
    MsgBox Err.Description
    Resume Exit_Command1_Click
End Sub

Open in new window

Watch Question

If I understand this correctly for each record there is a home address and a business address, and a set of fields for each address, is that correct? So frm_bus_add shows one set of fields and frm_cust_add shows the other set ?

In the first place I'm not sure if the data structure that has been created for this scenario is ideal, but leaving that aside, you will need to make sure that the id from the first form is passed to the two address forms so that they operate on the same record

This may create another problem though because if your master form has a particular record open in edit mode, when you attempt to open another form on the same record you may get a lock conflict.  Anyway you can try and make sure that the RecordLocks property of the main form is set to 'No Locks'

Im going to assume that the main form actually creates the customer record and that the other two forms are for editing only so, if there is an ID field on the master form you can use it to tell the address forms to edit the particular record

If Me.business = True Then stDocName = "frm_bus_add" Else stDocName = "frm_cust_add"
    DoCmd.OpenForm stDocName, ,"ID=" &  Me.ID, acFormEdit, , , stLinkCriteria

Note "ID=" &  Me.ID This passes the ID from the master form to the address form, (assuming there is a field called ID in the controlsource, if not amend as appropriate you will need to make sure that the recordsource ofr the address froms also include this field as well as their respective address fields.  acFormEdit is being used because you want to edit an existing record, acFormAdd will always create a new record so the data will never go into the same record.

Hope this helps!


Very useful. Thank you.