Making subforms editable

[ashiers]

Hi there,



I have a form with two subforms. I set the RecordSource property for each subform progammatically by setting it to an SQL statement. This works, however, the subforms are read only. I can't edit them for updates to the appropriate tables.

I created a button for each subform on the main form which is supposed to change the behaviour from read only to editable, but the code I'm using is not working as hoped. See below for code of one of the buttons:

 
'At top of main form
Dim LLUnlocked As Boolean
 
Private Sub cmdLLUnlock_Click()
  If LLUnlocked = False Then
    Me.cmdLLUnlock.SetFocus
    Me.cmdLLUnlock.Caption = "Lock Table"
    Me.frmLANDLINES.Form.AllowAdditions = True
    Me.frmLANDLINES.Form.AllowEdits = True
    Me.frmLANDLINES.Form.AllowDeletions = True
    LLUnlocked = True
  Else
    Me.cmdLLUnlock.SetFocus
    Me.cmdLLUnlock.Caption = "Unlock To Edit"
    Me.frmLANDLINES.Form.AllowAdditions = False
    Me.frmLANDLINES.Form.AllowEdits = False
    Me.frmLANDLINES.Form.AllowDeletions = False
    LLUnlocked = False
  End If
End Sub

Please advise,

Alan

[RuralGuy]

Have you checked the RecordSets to which you are setting the SubForms to see if they are updateable? BTW the cmdLLUnlock control has the focus or you would not be in that code so SetFocus is not necessary. You also cannot set the focus to the control with the focus in *any* event of that control.

[ashiers]

ok...I've made sure that the properties AllowEdits, AllowAdditions, and AllowDeletions are all set to true on the main and subforms. It was also suggested that the Recordset be a type that allows updating. Following are the two versions of my RequeryForm() method. One deals with the Recordset directly, while the other doesn't. Both work in the sense that they display the proper data, but neither allow me to edit any of the fields in the subforms. Someone please tell me what needs to be included in my code to ensure the subforms are editable!

Alan

Public Sub RequeryForm()
On Error GoTo ErrHandler
'Obtain the name of the Employee first and display in top label
Me.Label10.Caption = GetName(CLng(Module1.employeeID))
'Create an SQL statement for the frmLANDLINES recordsource property
Module1.landlineSQL = "SELECT LANDLINES.DEVICE_ID, LANDLINES.LANDLINE_CC, LANDLINES.LANDLINE_NUMBER, LANDLINES.LANDLINE_PORT, LANDLINES.LANDLINE_JACK" & _
" FROM LANDLINES, COMMUNICATIONS WHERE COMMUNICATIONS.EMPLOYEE_ID=" & Module1.employeeID & " AND COMMUNICATIONS.DEVICE_ID=LANDLINES.DEVICE_ID AND COMMUNICATIONS.DEVICE_TYPE='Landline';"
Me.frmLANDLINES.Form.RecordSource = Module1.landlineSQL
Me.frmLANDLINES.Requery
'Create an SQL statement for the frmMobile recordsource property
Module1.mobileSQL = "SELECT MOBILE.DEVICE_ID, MOBILE.MOBILE_CC, MOBILE.MOBILE_TYPE, MOBILE.MOBILE_NUMBER" & _
" FROM MOBILE, COMMUNICATIONS WHERE COMMUNICATIONS.EMPLOYEE_ID=" & Module1.employeeID & " AND COMMUNICATIONS.DEVICE_ID=MOBILE.DEVICE_ID AND COMMUNICATIONS.DEVICE_TYPE='Mobile';"
Me.frmMobile.Form.RecordSource = Module1.mobileSQL
Me.frmMobile.Requery

ErrHandler:
    If Err.number = 2467 Then
       MsgBox Err.number & "One of the subforms or tables (LANDLINES or MOBILE) is already open by you or someone else." & vbCrLf & _
              "It needs to be closed before you can view the COMMUNICATIONS form in its entirety.", vbOKOnly
       Exit Sub
    End If
    If Err.number <> 0 Then
      MsgBox Err.number & " Description: " & Err.Description, vbExclamation
      Exit Sub
    End If
End Sub

Public Sub RequeryForm()
Dim rst As Recordset
'On Error GoTo ErrHandler
'Obtain the name of the Employee first and display in top label
Me.Label10.Caption = GetName(CLng(Module1.employeeID))
'Create an SQL statement for the frmLANDLINES recordsource property
Module1.landlineSQL = "SELECT LANDLINES.DEVICE_ID, LANDLINES.LANDLINE_CC, LANDLINES.LANDLINE_NUMBER, LANDLINES.LANDLINE_PORT, LANDLINES.LANDLINE_JACK" & _
" FROM LANDLINES, COMMUNICATIONS WHERE COMMUNICATIONS.EMPLOYEE_ID=" & Module1.employeeID & " AND COMMUNICATIONS.DEVICE_ID=LANDLINES.DEVICE_ID AND COMMUNICATIONS.DEVICE_TYPE='Landline';"
Set rst = New ADODB.Recordset
rst.Open Module1.landlineSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
Set Me.frmLANDLINES.Form.Recordset = rst
Me.frmLANDLINES.Requery
'Create an SQL statement for the frmMobile recordsource property
Module1.mobileSQL = "SELECT MOBILE.DEVICE_ID, MOBILE.MOBILE_CC, MOBILE.MOBILE_TYPE, MOBILE.MOBILE_NUMBER" & _
" FROM MOBILE, COMMUNICATIONS WHERE COMMUNICATIONS.EMPLOYEE_ID=" & Module1.employeeID & " AND COMMUNICATIONS.DEVICE_ID=MOBILE.DEVICE_ID AND COMMUNICATIONS.DEVICE_TYPE='Mobile';"
Set rst = New ADODB.Recordset
rst.Open Module1.mobileSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
Set Me.frmMobile.Form.Recordset = rst
Me.frmMobile.Requery

ErrHandler:
    If Err.number = 2467 Then
       MsgBox Err.number & "One of the subforms or tables (LANDLINES or MOBILE) is already open by you or someone else." & vbCrLf & _
              "It needs to be closed before you can view the COMMUNICATIONS form in its entirety.", vbOKOnly
       Exit Sub
    End If
    If Err.number <> 0 Then
      MsgBox Err.number & " Description: " & Err.Description, vbExclamation
      Exit Sub
    End If
End Sub

[RuralGuy]

What is the Module1 in:
Module1.landlineSQL = "SELECT ...

[ashiers]

What is the Module1 in:
Module1.landlineSQL = "SELECT ...

landlineSQL is just a String variable I have in Module1. There's also a String variable named mobileSQL there. I placed them there in the event I may need these on the fly in other methods, though I may change it later.

[RuralGuy]

You need to put your SQL strings into the SQL view of the query builder and see if your query is updateable as a static query.

[ashiers]

You need to put your SQL strings into the SQL view of the query builder and see if your query is updateable as a static query.

Well, I did what you suggested and placed the query into the SQL View of the query builder and looked at the properties dialog box. However, there's nothing there to indicate whether a query is updatable or not. I must be looking in the wrong place. Where do I find that information?

[RuralGuy]

After you get the SQL into the query you switch to Datasheet view and see if you can change anything. If the query is not updateable then you can not change any of the fields and it will beep every time you try.