Discussion Locked

MS Access Subforms

0 helpful
10 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
My application uses a Main form and multiple subforms for data input. Without making up entirely new forms I want to be able to use code behind a command button to open the same form in data entry mode and edit only mode. How do I get the subformsto open in the same mode as the main form. I don't want to use the property pages due to the different roles the same form will have to take on in the application.

10 replies

by sl-campbell

I am not sure how you are calling the sub forms, but if you have a command button with a DoCmd.OpenForm, then you can assign opening arguments for the form, (ie. DoCmd.OpenForm "frmDEPT", acNormal, , , acReadOnly, , cDATAVIEW

cDATAVIEW is one oftwo public constants that I made for this demo. These constants are in a module:

'===========================
'Module1
Option Compare Database
Option Explicit

Public Const cDATAVIEW% = 0
Public Const cDATAENTRY% = 1
'===========================

You can define these as you wish and add more constants.

So. now go to the sub form to be opened by the DoCmd. In design view of the form, go to the menu point 'View' and click on 'Code'. The code window will open. There are 2 drop downs at the top of the code window. Click on the left one and select 'Form'. An Event (Load) should be placed in the code window for by default if there are no other events for the form object. We do not need this event for this demo. We need the Open event. You will need to select it from the right drop down. The Open event automatically fires when the form opens. In this event place the following code:

'===========================
Private Sub Form_Open(Cancel As Integer)
Dim vOpenOptions As Variant

vOpenOptions = Me.OpenArgs
'If no opening arguments were passed the set to the default
If IsNull(vOpenOptions) Then vOpenOptions = cDATAVIEW

Select Case vOpenOptions
Case cDATAENTRYMe.DataEntry = True
'Here, more properties can be set

Case Else 'cDATAVIEW = default
Me.DataEntry = False
'Here, more properties can be set

End Select

End Sub

'===========================

by sl-campbell

Ran out of space!
Continued.............

'===========================

Then in order to call the sub form you can use a 2 command buttons with the following code:

Private Sub cmdOpenFormAddNew_Click()

On Error GoTo Err_OpenForm

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDEPT"

'Change the OpenArgs parameter to cDATAVIEW for the command button that opens the form to view all data.
'You can add arguments to open the form as read only, or whatever, either in the DoCmd or by passing the arguments via OpenArgs.

DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, , OpenArgs:=cDATAENTRY

Exit_OpenForm:
Exit Sub

Err_OpenForm:
MsgBox Err.Description
Resume Exit_OpenForm

End Sub

Ok, this is only for demonstration purposes and was created 'on the run', but I think you will get the idea. You can set the properties for the form or its' objects.

Have fun!

by wkmtn

The main form has an Access 2000 Tab Control with 8 tabs and anywhere between 2 and 7 sub forms on each tab, thus the embedded subforms are are opened whenever the main form opens. I don't want the user to be able to enter multiple records on the subforms in "single form view" (currently they can just by tabbing through the text boxes. I want the entire form, (including the subforms to basically entail one record and then use a command button to close and save the from/record.

This discussion is closed to new replies.