Form Challenge: Display Unique Values and Edit them

[sxschech]

Couple of issues, first one (I've done a work around by doing a make table each time procedure is run to get one row per group, but if there is a solution to this to use the original table, that would be great, otherwise skip to second issue).

1:
This probably wouldn't be an issue if using a combo box, but user wants to see all values at the same time (I set up as continuous form) and be able to edit/update one of the fields. The underlying table is a key words table and is used to assign an overall grouping to another table. I was hoping that since I need these same groupings for a summary purpose, I could use the same table. A summary query becomes non updatable. Problem is that the user is going to assign a value to a group. In this case it doesn't matter which row in the group is assigned the value, but only one row should be visible on the form for editing.

2:
I have put text boxes on the form that use a dcount to return a count of how many rows (records) would be selected based on the value the user entered. It is working, however, the reference to the textbox seems to require the actual name of the form rather than "Me". I would like to not have to hard code the form name as the name of the form may get changed when it goes from testing into production and then I have to remember to fix it or an error will result.

The formula is getting the table name from combobox on another form called "Utilities" and the values from the underlying table bound to form "Form4"

[tt]=DCount("Company",[Forms]![frmUtilities].[cboPreview],"Counter <= " & [Forms]![Form4].[txtCutoff] & " AND Title_Group='" & [Forms]![form4].[Title_Group] & "'")[/tt]

Here is sample data for the keyword grouping table (cutoff value before user data entry will be null):
[pre]
ID TitleGroup Title Priority Cutoff
1 IT Information 1
2 IT Technology 1
3 IT CIO 1
4 HR HR 2
5 HR Human Resources 2
[/pre]

Based on the sample data, the form should display two rows.
Underlines represent the text boxes that will be filled in and calculated
based on user input. Like this:

[pre]
Title Group Cutoff Count Total N Total C
IT __ __ 4 5000
HR __ __ 1 77
[/pre]

User will then enter a Cutoff number less than or equal to the "Total N" and this will then
result in a calculation appearing in "Count" based on the dcount formula

[pre]
Title Group Cutoff Count Total N Total C
IT 3 4200 4 5000
HR 1 77 1 77
[/pre]

 

[MajP]

however, the reference to the textbox seems to require the actual name of the form rather than "Me".

Me would be wrong for a calculated control; however, normally you do not have to reference the form when talking about a control on that form.
I would think simply
"Company",[Forms]![frmUtilities].[cboPreview],"Counter <= " & [txtCutoff] & " AND Title_Group='" & [Title_Group] & "'"

I may not understand the first question, but the data does not look normal. You show cutoff in the "key word grouping" table, but my guess is that value is unique to the TitleGroup. Also Priority seems uniqe to the group. However, each entity in that table is title - titlegroup. I think I would have a seperate group table. It would simply have

GroupID  Title Group  Priority CutOFF
1          IT            1
2          HR            2

Then my Title table has

Title         GroupID
Information           1
Technology            1
CIO                   1
HR                    2
Human Resources      2

 

[sxschech]

Thanks for replying MajP. Sorry wasn't able to articulate part 1. Mainly was seeing if I could use an existing table for multiple purposes rather than creating a second table, which is what I ended up doing (as mentioned in first line of original post) and you suggested. Since I'm using multiple tables now, and hadn't been checking in over the holidays, I decided to do the dlookup and form references in the underlying query rather than as controls on the form and that seemed to allow edit/updates as well as the calculations.

 

[Darrylles]

I have in the past had a requirement to identify the form name at run-time (for function arguments) - you can ascertain the current form name with .Activeform & the Screen Object.

https://msdn.microsoft.com/en-us/library/office/aa223199(v=office.11).aspx

A combo box can display many, many items at once - it need not be the default 8. In any case, what will happen when the number of items increases so that the continuous form cannot display 'all' items (and a scroll bar appears)?
A separate, single text box can display the 'editable' field as each item is selected.

As for user 'requirements' of wanting to see 'all values' - I have learned to bite the bullet and put my foot down regarding such.
E.g. I have many users, all of whom are used to Excel, and want everything to 'look like' Excel.
I tried to accommodate them for a time, but it quickly became obvious that production / maintenance / performance became a nightmare.

I now say (even if it's a 'boss'): "If you want Excel, then get someone else to produce your solutions (and maintain them, because I will not support them).
My job-spec / contract clearly states my skills, and I do not use Excel to produce business-critical solutions for good reason.
These are database solutions which give massive benefits over Excel and are 'optimised' to work in a certain fashion, and those optimisations are lost when 'pretending' to be Excel.
In any case, when you can 'see' all data - you don't 'see' all data at all - your brain can only process ONE row at a time - and THAT is how a database solution will display it (with efficient search and filter functionality)."

I do understand that the company dictates what it wants, but most companies will have policies that state that software must be produced in line with best-practice and, you ARE employed for your technical skills; they state WHAT they want - you state HOW it's implemented in an expert fashion. Submitting to non-technical employee whims simply makes you (and the company) less efficient - THEY don't answer for that - YOU do.

ATB,

Darryle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[sxschech]

Hi Darryle,

Thanks for the observations and link. I agree with what you are saying. However in this instance, the layout does need to show several rows of summarized data in order to see and perform the calculations and make appropriate decisions. The purpose of the form is for building a where clause.