built in toolbar record delete button doesn't stay active

[Lhuffst]

I have a form in access 2003 that the first time you open the form the delete record button on the built in tool bar is active. If I go to another form (search form) and come back, the delete record button is inactive.

The search f orm allows you to fill in a text box and via sql returns the appropriate record back to the first form.
I added in the line

me.allowdeletions = true
but that doesn't work. Not sure how or why the delete records button no longer works. Any suggestions are welcome.
thanks lhuffst

 

[lameid]

The search f orm allows you to fill in a text box and via sql returns the appropriate record back to the first form.

That doesn't make sense. Via SQL you can update the Rowsource for the form or combo/list boxes on the form but you cannont update the form. In all those cases you need to requery the object once the data is changed. I am guessing your issue is Access is not letting you delete a record that doesn't match what you have on screen.

 

[Lhuffst]

Thank you for the info. Maybe I am not understanding this code properly.

The sql statement builds properly.

 ' Open the Parking Permit Application Form and populate the fields.
    Forms("frmParkingPermitAppl").RecordSource = ssql

    DoCmd.OpenForm "frmParkingPermitAppl", acNormal
    [Forms]![frmParkingPermitAppl]!ViewAllRecords.Enabled = True
    [Forms]![frmParkingPermitAppl].AllowDeletions = True
'Set field values on the Search screen to null once a valid record is found and close _
    the Screen.
    fldPermitNo = Null
    fldLastName = Null
    fldFirstName = Null
    fldMiddleInit = Null
    fldId = Null
    fldVehYear = Null
    fldVehMake = Null
    fldVehModel = Null
    fldLicenseNo = Null
    fldPermitNo.SetFocus
    
    [Forms]![frmSearch].[Visible] = False

The recordsource for the main form does populate from the 2nd form (search) properly, it just doesn't allow for record deletions.
Do i need to do something else to make sure that the record that is selected from the search form is the same record on the main form?

 

[lameid]

Does the value in ssql result in a query/table that is editable?

Also I do not think you can reference a form in the forms collection unless it is already. So the line where you set the recordsource seems out of place to me. Perhaps this is because it is a code excerpt.

 

[Lhuffst]

How do I tell if the query is editable? The query is a combination of two tables.
When you open the first form the recordsource has

 SELECT DISTINCTROW tblParkingPermitAppl.*
FROM tblParkingPermitAppl INNER JOIN tblParkingPermitVehList ON tblParkingPermitAppl.fldPermitNo = tblParkingPermitVehList.fldPermitNo;

I assumed that when the second form reset the recordset, it would automatically take precedence over the first one. Could that be the problem? The whole code on the search form is

Dim cnt As Integer
Dim ctl As Control
Dim nill As String
Dim ssql As String
Dim Hold As String
Dim db As Database
Dim rs As Recordset
Dim isblank As Boolean

Set db = CurrentDb()

isblank = True

cnt = 0
'figure out which search boxes are filled in
' If Nz(Me.fldPermitNo, "") & "" <> "" Then cnt = cnt + 1   'one method but didn't use
For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Then
    Debug.Print ctl.Name
    If ctl <> "" Then
        cnt = cnt + 1
    End If
  End If
Next ctl

If cnt = 0 Then
      MsgBox "You must enter something to search for.."
      fldPermitNo.SetFocus
      Exit Sub
   End If

ssql = "Select distinct " & _
        "a.fldPermitNo, " & _
        "a.fldLastName, " & _
        "a.fldFirstName, " & _
        "a.fldMiddleInit, " & _
        "a.fldLocation, " & _
        "a.fldTeamGroupUnit, " & _
        "a.fldGroupUnit, " & _
        "a.fldWorkPhone, " & _
        "a.fldOrganizationNo, " & _
        "a.fldId, " & _
        "a.fldInsCo, " & _
        "a.fldPolicyNo, " & _
        "a.fldMemo, " & _
        "a.fldLastUpdated, " & _
        "b.fldVehMake, " & _
        "b.fldVehModel, " & _
        "b.fldLicenseNo, " & _
        "b.fldDescription, " & _
        "b.fldVehState, " & _
        "b.fldVehYear " & _
        "FROM tblParkingPermitAppl a, tblParkingPermitVehList b " & _
        "Where b.fldPermitNo = a.fldPermitNo "

 For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Then
    Debug.Print ctl.Name
    If ctl <> "" Then

    Select Case ctl.Name
        Case "fldPermitNo"  'permit number
            If DCount("*", "tblParkingPermitAppl", "fldPermitNo='" & fldPermitNo & "'") = 0 Then
              MsgBox "zero records.", vbInformation, "System Error"
            Else
                ssql = ssql + " AND a.fldPermitNo = '" & fldPermitNo & "'"
            '  Debug.Print ssql
            End If

         Case "fldLastName"     'last name
              If DCount("*", "tblParkingPermitAppl", "fldLastName='" & fldLastName & "'") = 0 Then
                 MsgBox "zero records.", vbInformation, "System Error"
              Else
                 ssql = ssql + " AND a.fldLastName = '" & fldLastName & "'"
                     '   Debug.Print ssql
              End If
                     
         Case "fldFirstName"    'first name
              If DCount("*", "tblParkingPermitAppl", "fldFirstName='" & fldFirstName & "'") = 0 Then
                 MsgBox "zero records.", vbInformation, "System Error"
              Else
                 ssql = ssql + " AND a.fldFirstName = '" & fldFirstName & "'"
                     '  Debug.Print ssql
              End If
                     
         Case "fldMiddleInit"    'middle initial
              If DCount("fldMiddleInit", "tblParkingPermitAppl", "fldMiddleInit='" & fldMiddleInit & "'") = 0 Then
                 MsgBox "zero records.", vbInformation, "System Error"
              Else
                 ssql = ssql + " AND a.fldMiddleInit = '" & fldMiddleInit & "'"
                       ' Debug.Print ssql
              End If
                     
         Case "fldid"   'wssc emp id
              If DCount("*", "tblParkingPermitAppl", "fldid='" & fldId & "'") = 0 Then
                 MsgBox "zero records.", vbInformation, "System Error"
              Else
                 ssql = ssql + " AND a.fldid = '" & fldId & "'"
                       'Debug.Print ssql
              End If
                     
         Case "fldLicenseNo"     'license number
              If DCount("*", "tblParkingPermitAppl", "fldLicenseNo='" & fldLicenseNo & "'") = 0 Then
                 MsgBox "zero records.", vbInformation, "System Error"
              Else
                 ssql = ssql + " AND b.fldLicenseNo = '" & fldLicenseNo & "'"
                       ' Debug.Print ssql
              End If
                     
         Case "fldVehYear"    'Vehicle Year
              If DCount("*", "tblParkingPermitAppl", "fldVehYear='" & fldVehYear & "'") = 0 Then
                 MsgBox "zero records.", vbInformation, "System Error"
              Else
                  ssql = ssql + " AND b.fldVehYear = " & fldVehYear & ""
                    'Debug.Print ssql
              End If
                     
         Case "fldVehMake"    'Vehicle Make
              If DCount("*", "tblParkingPermitAppl", "fldVehMake='" & fldVehMake & "'") = 0 Then
                 MsgBox "zero records.", vbInformation, "System Error"
              Else
                 ssql = ssql + " AND b.fldVehMake = '" & fldVehMake & "'"
                      '  Debug.Print ssql
              End If
                     
         Case "fldVehModel"   'Vehicle Model
              If DCount("*", "tblParkingPermitAppl", "fldVehModel='" & fldVehModel & "'") = 0 Then
                 MsgBox "zero records.", vbInformation, "System Error"
              Else
                  ssql = ssql + " AND b.fldVehModel = '" & fldVehModel & "'"
                      ' Debug.Print ssql
              End If
        Case Else
                'do nothing


        End Select
     '   Debug.Print ctl.Name
       Else
       'skip it
       End If
     End If
    Next ctl
  Debug.Print ssql


   ' Open the Parking Permit Application Form and populate the fields.
    Forms("frmParkingPermitAppl").RecordSource = ssql

    DoCmd.OpenForm "frmParkingPermitAppl", acNormal
    [Forms]![frmParkingPermitAppl]!ViewAllRecords.Enabled = True
    [Forms]![frmParkingPermitAppl].AllowDeletions = True
    

   'Set field values on the Search screen to null once a valid record is found and close _
    the Screen.
    fldPermitNo = Null
    fldLastName = Null
    fldFirstName = Null
    fldMiddleInit = Null
    fldId = Null
    fldVehYear = Null
    fldVehMake = Null
    fldVehModel = Null
    fldLicenseNo = Null
    fldPermitNo.SetFocus
    
    [Forms]![frmSearch].[Visible] = False

 

[PHV]

ssql = "Select [!]distinct[/!] " & _
This query is NOT updatable !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

[blue]How do I tell if the query is editable?[/blue]

To tell if the returned recordset is [blue]uneditable[/blue] have a look at the [blue]AddNew[/blue] navigation button ... if it has that [blue]disabled look[/blue] then the recordset is uneditable!

If you cannot edit the data in a query, this list (courtesy by [blue]Allen Browne[/blue]) may help you identify why it is not updatable:
[ol][li]It has a [blue]GROUP BY[/blue] clause. A Totals query is always read-only.[/li]
[li]It has a [blue]TRANSFORM[/blue] clause. A Crosstab query is always read-only.[/li]
[li]It uses [blue]First(), Sum(), Max(), Count(), etc[/blue]. in the SELECT clause. Queries that aggregate records are read-only.[/li]
[li]It contains a [blue]DISTINCT[/blue] predicate. Set Unique Values to No in the query's Properties.[/li]
[li]It involves a [blue]UNION[/blue]. Union queries are always read-only.[/li]
[li]It has a [blue]subquery[/blue] in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.[/li]
[li]It uses [blue]JOINs of different directions on multiple tables[/blue] in the FROM clause. Remove some tables.[/li]
[li]The [blue]fields in a JOIN are not indexed correctly[/blue]: there is [blue]no primary key or unique index[/blue] on the JOINed fields.[/li]
[li]The query's [blue]Recordset Type[/blue] property is [blue]Snapshot[/blue]. Set Recordset Type to "Dynaset" in the query's Properties.[/li]
[li]The query is [blue]based on another query that is read-only[/blue] (stacked query.)[/li]
[li]Your [blue]permissions are read-only[/blue] (Access security.)[/li]
[li]The database is [blue]opened read-only[/blue], or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]