Form - records showing can not be edited

[lowesthertz]

Hi folks,
I have a single table that houses most of the data. There are a few small tables for the purpose of populating combo boxes. I have a single form in which all the data is entered, it has a bunch of text boxes and combo boxes. The form works great, it always shows all the records in the table.

So I need to be able to open this same form but have the information filtered to show records that are ready for approval. So I copied the form and made one small addition. I put a statement in the "on load" area to filter using two criteria. One date field is null and another date field is not null. So I open the form and it shows the correct records. The problem lies in that I can not edit any of the records showing. I just get a beep and the record does not change.



Any ideas how to fix this?
Thanks
Chris

[John_G]

Is the Allow Edits property of the form set to "No"? That will prevent any changes to the data.

What is the statement you are using to filter the data?

[lowesthertz]

Allow edits, allow deletions, allow additions, and data entry are all set to yes.

The filter statement is as shown below:
Private Sub Form_Load()
DoCmd.OpenForm "Car Board Review", acNormal, WhereCondition:="[D5 - Corrective Action Implementation Date] Is Not Null and [D8 - Date of CARB Review] Is Null"
End Sub


Could it have to do with the "acNormal"?

[rpeare]

if you have put criteria on tables that are not the 'main' table of the data entry that may cause a record lock like you are experiencing. Is the form based on *1* table or a query that is only referencing *1* table? The smaller tables are irrelevant if they support combo boxes and should not be included in any query running the form. Secondly, you do not need a second, identical, form to do what you're after. you can simply put an option group on your form (or combo box or any other mechanism really) and create a calculation in the query driving your form that can show or not show any combination of items you want unless it's horribly complex.

For instance if you have basically 2 options (show everything or show only 'ready for approval' items. If you add a combo box that is UNBOUND to your form and give it a value list of "All";"Pending" and name this combo box INDICATOR

Then in the query driving your form you could have a field like:

ShowInd: iif(forms!formname!indicator = "all", 1, iif(forms!formname!indicator = "Pending" AND not isnull([d5 - Corrective Action Implementation Date]) AND isnull([D8 - Date of CARB Review]), 1, 0))

Then in the criteria of this field you have a 1, all you'd have to do is requery your form when you change the value in your INDICATOR combo box.

[John_G]

In your On Load procedure, you are opening the form "Car Board Review". If that is the same form that the On Load procedure is in, then what you are doing is opening a second copy of the same form, and as a result a second reference to the form's table. Thst is what is causing the problem - the second instance of the form cannot be edited because the first instance has the table locked for editing.

In the On Load, try it this way:

me.filter = "[D5 - Corrective Action Implementation Date] Is Not Null and [D8 - Date of CARB Review] Is Null"
me.filteron = true

[lowesthertz]

John_G,
I tried what you said and I got the same thing. I put the code in the "on load" event of the form and I still can not edit.

I am not sure if this a a clue or not...but I also have a single field in my main table that stores multiple values based on the choices in a combo box on the form. When I open the form, I get multiple records if that field has mulitple choices for that specific record (if that makes any sense). Another words on my form I have a combo box that allows me to multi select the approvers of the record. It could be 1, 2, 3 etc approvers and varies by record. In the case where I had three approvers on one record, when I open my form, I now see three records (two duplicates). Could be completely unrelated, but I am unsure at this point.

[lowesthertz]

rpeare, first of all the form is running directly from the table, there is currently no query. I assume the method that you describe requires a query to run the form?

[rpeare]

Yes it does but the problem is your multi value field in all likelyhood. multi value fields/calculated fields are the worst things microsoft could have added to MS access they are an extremely bad idea and hard to work with if you do anything more than just look at them. Try creating a query ignoring any calculated/multi value fields for your form and see if it works as intended. If it still doesn't there may be other issues. If you're using a bound form (and it sounds like you are) remember to remove the multi value/calculated value fields from your form as well.

[lowesthertz]

The problem is resolved. I found that I was wrong, the form was getting the information from a query. I had put the query in there to try and do the filters (without any luck). Soon as I removed the query and went directly to the table with the load filter, it all worked fine. (including the multi select combo box). Thank you