Form with several necessary options..Don't even know where to start...

[Nilotec]

Alright... I spent 7 hours on this, this past Saturday and the logic of Access is just not meshing with my thought processes, especially since this is my first use of access in any real world application...

Here is what I am working with:
85k records.
Each record has a unique identifier that should not be changed.
Each record has an owner that should not be changed.
There are several incomplete fields on each record.

What I want to happen:
I want a form that I can select an owner and the form will filter all records to display the first incomplete record by that owner.
I need 3 fields to be locked in each record when they load including the "Owner", "Unique Identifier" and one other
I need 4 fields to remain open to editing unless a "Record Complete" yes/no box is checked in which case if the record is complete it should be completely locked AND it should not show up in the filtered list.

What I can make happen:


Using a form/subform of an Owner-Owner relationship I can filter all the forms to display only that Owner's records. Unfortunately when I try to click anywhere I get a message that a duplicate record will be created and then cannot enter any information. Alternatively I can lock the main form or not allow additions and then the combo box drop down becomes locked and I can't select an "Owner" to filter by (my results were the same by attempting a VBA filter on a single form instead of a form/subform). What on earth am I missing?

[Nilotec]

OK.. So after some more messing around I have got the Form and Subform working correctly. No new records are being created and the fields that need to be locked are working as intended and staying locked. There are two more pieces I'm working on and that is making the "Record Complete" yes/no checkbox in the subform disable the fields that can be edited and then a filter Radio Button in main form that will display All, Incomplete, or Complete records.

[pbaldy]

The easiest way to do the first is probably Conditional Formatting on the ribbon. Highlight the textboxes you want to enable/disable, click on Conditional Formatting. Select Expression Is and enter:

[RecordComplete] = True

and see what happens.

[June7]

Use the Change event of checkbox, something like (use Visible or Enabled):
Me.controlName1.Visible = Me.checkboxName
Me.controlName2.Visible = Me.checkboxName
...

EDIT: Did it again, forgot about Conditional Formatting to disable/enable controls.

Are radio buttons part of an OptionGroup control? Code in Click event to set Filter and FilterOn properties of form, something like:

Me.FilterOn = False
Select Case Me.OptionGroup
Case 1
Me.Filter = ""
Case 2
Me.Filter = "StatusFieldName='Incomplete'"
Case 3
Me.Filter = "StatusFieldName='Complete'"
End Select
Me.FilterOn = True

[Nilotec]

Thank you, everyone. I have made a lot of progress today and ended up using the following VBA on Current Form and After Update of the SubForm:
If Me.[Checkbox field] = True Then
Me.[Text field 4].enabled = False
Me.[Text field 5].enabled = False
Me.[Text field 6].enabled = False
Me.[Text field 7].enabled = False
Else
Me.[Text field 4].enabled = True
Me.[Text field 5].enabled = True
Me.[Text field 6].enabled = True
Me.[Text field 7].enabled = True
End If


One last item I am trying to add is a an additional filter via a combobox (in addition to the "Owner" filter that is already existing) that will only show Complete, Incomplete, or All records for that particular owner based on the above checkbox field. I just don't know if this filter should be done on the Main Form or the SubForm and how to tie it to the status of the Yes/No checkbox above. If the combobox is All records it should just return everything for that user... (no filter), if it is Incomplete records it should return only unchecked records for that user... and finally if it is Complete records it should return only checked records.

[June7]

I provided some example code, including simpler version for the checkbox condition. However, your code is the reverse of what I expected so could do:
Me.[Text field 4].Enabled = Not Me.[Checkbox Field]


Is the status field on the main form? Where is the Owner filter? Is it a parameter in form RecordSource or is this form opened filtered to a specific owner by using the WHERE CONDITION of DoCmd.OpenForm?

[Nilotec]

Hello June7,
Hopefully a picture is worth a thousand words. I have highlighted the two areas I need tied together between the MainForm and SubForm.

As far as the rest of what you asked... it's gibberish to me, unfortunately.

[June7]

That gives me partial answer.

I see the Owner filter criteria is entered into a combobox on main form. Is the combobox unbound? Need to know how that input is used. Is the main form's RecordSource a query? Is there a filter paramater under the Owner field in the RecordSource that references the combobox?

Why does Owner textbox on subform say 'Please select ...'? User should not be able to make Owner selection in the subform because of the Master/Child links properties. Don't even need to display on subform.

The form and subform have different data sources (parent/child tables)? If not, I suspect you have form/subform arrangement when a simple form will do.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Nilotec]

Both ComboBoxes on the main form are unbound I believe. Currently I just had a default "Please select..." option that actually doesn't create a record as it just filters the records when a name is selected. I'm not sure why it appears in the SubForm. The main forms Record Source is an "Owners Table" that is basically now defunct. It's an empty table with a combobox in the first record. It is not a query. The Owner field itself on the main form is actually just a Value List of possible owners. As far as I could see there was no filter parameter in the Owner RecordSource. I could not get the simple form to work without it overwriting old records and creating new records whenever I selected an Owner which is why I went Form/Subform

[June7]

Why bind a form to 'defunct' table. Linking form/subform is meaningless.

A simple form will work just as well. If it didn't then your code was bad. Put unbound Owner combobox in form Header.

I still don't know how the combobox input is used. If there is no query with a parameter, then is there code (macro or VBA) or criteria in form Filter property?

Still willing to look at your db if you provide.

[Nilotec]

It's NOW defunct.. before it wasn't. In an attempt to do what you are suggesting I created a new combobox on the old sub form (now new main form) as a query box but a whole slew of new problems arrived... Because there are only 8 owners for the 85k records I now have 8 names repeated 85,000 times instead of each name listed once. When I select a name it will not filter anything but just pull up that single record out the 85k.

[June7]

You don't have an Owners table? You are drawing the Owner names from the 85,000 records? Use the DISTINCT keyword in the combobox RowSource query.
SELECT DISTINCT Owner FROM tablename ORDER BY Owner;

You are filtering the table ID field instead of the Owner name.

[Nilotec]

Alright, I have tried what you said and the new Filter by Owner field is blank. This is soooo frustrating. I deleted 99% of the records and am attaching a copy for you to look at. There is a master table and 2 forms. The Owner form works for filtering just fine except for when I delete the "Please select..." option because it keeps overwriting the first record and I guess its "unclean" because it is a form/subform... The other "Hoster..." form that I have tried adding the owner filter to is the one I can't get to work at all. Email Host Data [Test].zip

[Nilotec]

Any ideas?

[June7]

First, combobox used to input filter criteria should be UNBOUND, otherwise you change value in record or start a new record. The Owner combobox should not have ControlSource.

Second, you have a valuelist as the Owner combobox RowSource, not a query, which is fine but not what I thought you were doing from description in earlier post, although I should have realized that because of the 'Please select ...' instruction. Don't have the 'Please select ...' instruction in the combobox RowSource as this complicates coding. Put the instruction in a separate label above the combobox.

Third, move the search comboboxes into main form header and get rid of the subform. Form/subform with same data source is usually a bad idea. This along with the bound combobox was probably somehow contributing to the error about duplicate records.

Fourth, the combobox AfterUpdate event code isn't complete. If you want multiple criteria, use a command button Click event for search code. This tutorial http://datapigtechnologies.com/flash...tomfilter.html shows one way to filter a form with multiple criteria. However, all the criteria is text - no numbers, dates, Yes/No. Another technique is to use VBA code to construct filter string. Review http://allenbrowne.com/ser-28.html. Follow the link in that tutorial for example of code to build multiple criteria string.