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

 

[pbaldy]

The code looks okay offhand. Make sure the SQL produces an updatable recordset. You can run the query and see if you can add/edit data directly in it.

 

[missinglinq]

The problem may be associated with the queries the subforms are based on, but you also have to remember that the AllowAdditions/AllowEdits/AllowDeletions properties of subforms are governed by the way these properties are set on the main form!

In other words, if these properties are set to No on the main form, they are effectively set to No on the subforms, regardless of how the properties are set on each individual subform!

 

[ashiers]

The code looks okay offhand. Make sure the SQL produces an updatable recordset. You can run the query and see if you can add/edit data directly in it.

OK. So how does one ensure a recordset is updatable from an SQL statement? I'm not familiar with that technique. All I used was a SELECT * FROM LANDLINES WHERE [yadda, yadda, yadda]
The WHERE clause provides specific filtering.

Please advise,

Alan

 

[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

 

[pbaldy]

Those are not simple "All I used was a SELECT * FROM LANDLINES WHERE" queries, as both have multiple tables in the FROM clause. Here's info on what makes a query read only:

http://allenbrowne.com/ser-61.html

http://support.microsoft.com/default.aspx?scid=kb;en-us;328828

 

[ashiers]

So, it would seem that from the literature you pointed me to, the thing that is preventing the subforms from being updatable is the fact that my query is accessing more than one table in a join?

 

[pbaldy]

What join?

Simple enough to test. Change the SQL to the simple bit you posted earlier, using only one table, and see if the forms become updatable.