469,609 Members | 1,183 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Recordset not updateable


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.
Mar 2 '09 #1
2 1087
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ChangeNo_DblClick(Cancel As Integer)
  2. Dim stLinkCriteria As String
  3. Dim tmpTitle As String
  4. '--------------------------------------
  5. ' Open detail page and pass record ID
  6. '--------------------------------------
  7.     stLinkCriteria = " ID = " & Me.ID
  8.     DoCmd.OpenForm "Change_Order_Detail", , , stLinkCriteria
  9. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Cmd_Filter_Click()
  2. '--------------------------------------
  3. ' use screen input to create a filtered
  4. ' sql statement
  5. '--------------------------------------
  6.     Dim sqltxt As String, sqlwhere As String
  7.     Dim SrchStr As String
  10.     sqltxt = " SELECT * FROM PO_HEADER "
  11.     sqlwhere = " WHERE 1=1 "
  14.     If Len(Me.Fltr_DocType) > 0 Then
  15.         sqlwhere = sqlwhere & " AND (DocumentType = '" & Me.Fltr_DocType & "') "
  16.     End If
  19.     If Nz(Me.IncludeExcluded, 0) = 0 Then
  20.         sqlwhere = sqlwhere & " AND (Exclude = 0)  "
  21.     End If
  23.     If Len(Me.SrchVendor) > 0 And (Me.SrchVendor <> "*") Then
  24.         sqlwhere = sqlwhere & " AND (VENDOR Like '" & Me.SrchVendor & "*') "
  25.     End If
  27.     sqltxt = sqltxt & sqlwhere
  28.     Me.PurchaseOrder_Log_Details.Form.RecordSource = sqltxt
  29.     Me.PurchaseOrder_Log_Details.Form.Refresh
  30. End Sub
Mar 2 '09 #2
2,653 Expert 2GB
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.

Mar 2 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Edward | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.