By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Recordset not updateable

P: 9

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
Share this Question
Share on Google+
2 Replies

P: 1
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

Expert 2.5K+
P: 2,653
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.