Recordset not updateable

[jghouse]

Everyone,

Hopefully you can help me with a little problem I am having.

I have a need to limit the records shown in a form by a few different criteria. I also need these records to be editable.

I am capable of building the query to show the records but when I include one query I need to get a limiting value from (orders greater than 1 per day, calculated value in that query) the records are no longer editable. I believe this is because the final query I am adding is non-editable because it has some calculated fields in it (also has "Group By" clauses).

Does anyone have an advice as to how to limit the values on the form while still keeping them editable if I need to use values from that query as part of the limiting criteria? The only method I can currently think of is to save the results of the query that isn't updateable into a table and link that into the query for the form since it should then be updateable.

[mgoodrum]

There are two ways to ways to filter records on a form and leave the data in the form editable. The simplest way is to pass a filter when you open the form. (if you are opening the form from a button control. Here is a sample of what that might look like.

Private Sub ChangeNo_DblClick(Cancel As Integer)
Dim stLinkCriteria As String
Dim tmpTitle As String
'--------------------------------------
' Open detail page and pass record ID
'--------------------------------------
    stLinkCriteria = " ID = " & Me.ID
    DoCmd.OpenForm "Change_Order_Detail", , , stLinkCriteria
End Sub

The other way is more complicated, but if you have a form\sub form, where the form has drop downs to filter data presented in the sub form. This method works very well.

Here is a sample of what that might look light.

Private Sub Cmd_Filter_Click()
'--------------------------------------
' use screen input to create a filtered
' sql statement
'--------------------------------------
    Dim sqltxt As String, sqlwhere As String
    Dim SrchStr As String


    sqltxt = " SELECT * FROM PO_HEADER "
    sqlwhere = " WHERE 1=1 "
       
       
    If Len(Me.Fltr_DocType) > 0 Then
        sqlwhere = sqlwhere & " AND (DocumentType = '" & Me.Fltr_DocType & "') "
    End If
       
       
    If Nz(Me.IncludeExcluded, 0) = 0 Then
        sqlwhere = sqlwhere & " AND (Exclude = 0)  "
    End If
       
    If Len(Me.SrchVendor) > 0 And (Me.SrchVendor <> "*") Then
        sqlwhere = sqlwhere & " AND (VENDOR Like '" & Me.SrchVendor & "*') "
    End If
   
    sqltxt = sqltxt & sqlwhere
    Me.PurchaseOrder_Log_Details.Form.RecordSource = sqltxt
    Me.PurchaseOrder_Log_Details.Form.Refresh
End Sub

[FishVal]

Hello, jghouse.

You didn't give too much details. At least query(ies) SQL will give more ideas of how your problem could be resolved.
From what you have posted I could give one quite abstract suggestion:
in most cases join could be replaced with filtering by field value equality to either in a set of values using keyword IN.

Regards,
Fish.