Code counting -- How to tally and use table data to validate input

English
, ,
#1

I have been working on this for two days with reference to LibO Help, OO Documentation, StackExchange, and whatever else internet search engines could provide. I have managed various values of ‘success’ and even crashed LibO Base once, but have not managed to complete what I think is a simple task. My head aches from banging it against this cognitive wall. I must be missing something which is not made clear by any example or tutorial seen so far.

I have two tables. The first and simplest table is omitted from the sample database attached. This first table is just “CodeGroup” and “Description” (both columns are strings) – it provides descriptive name for each code group – with CodeGroup as primary key. I will use it for making the automation look nice later, so I left it out.

The second table is “CodeGroup”, “Code”, and “Tally”. The first two columns are strings, and the last is an integer. The table already contains the data which is static and has no need to change – the code group and the codes. The “Code” column is the primary key.

What I am trying to do is use the codes already in the table to provide a list box or combo box which autocompletes as a code is being typed (fixing lowercase and validating input at the same time) and then, once is pressed, increases the tally value by one, and make ready for the next code. I would also like to display all the codes and tallys belonging to the same code group after each code tally is updated.

Seems really really simple, yes? I thought I might find a working example or tutorial but nothing I found is designed this way. I am at a loss why I cannot complete this task. I have been experimenting with Forms and Subforms and List Boxes and Combo Boxes and Formatted Fields and Text Fields and SQL and Control properties and Forms properties and on and on …

I will greatly appreciate expert assistance and give many thanks.

ps: oh. :frowning: i must have 3 karma points to upload the sample database. and I have but 1. I’ll append an excerpt of the data below (you’ll see the pattern. the real data is different only in detail. each code is unique. each code group contains a varying number of codes, ranging from 4 to 8. the sample has code groups A1 to A5, B1 to B5, and so on, to E1 to E5):

CodeGroup Code Tally
A1	A1G	0
A1	A1F	0
A1	A1E	0
A1	A1D	0
A1	A1C	0
A2	A2G	0
A2	A2F	0
A2	A2E	0
A2	A2D	0
A2	A2C	0
A3	A3G	0
A3	A3F	0
.
.
.
E3	E3D	0
E3	E3C	0
E4	E4G	0
E4	E4F	0
E4	E4E	0
E4	E4D	0
E4	E4C	0
E5	E5G	0
E5	E5F	0
E5	E5E	0
E5	E5D	0
E5	E5C	0
#2

left out the mention of the primary key in the second table.

#3

I have increased your Karma to allow you to post your sample. I will have to re-read your question about 5 more times as I can’t grasp what the purpose is or the use of a listbox or combobox doesn’t quite make sense. If the codes are already in a table, why type them in the listbox? Why not just fill the listbox from the table? What is “tally”?

#4

Is the purpose just to update the Tally?

#5

Great that you have a good project. No it’s not simple. First get the list box to work. (Ratslinger taught me how to do this, and you can find his explanations by searching in Ask so as to not repeat that here.) Then write a macro to fire when the list box is updated which increments the tally for the current record. Is that the first part of what you want to do? And the 2nd part is show somewhere a query of what results you have so far. ??

#6

Yes, EasyTrieve, you have the general idea. I get a clean trigger of a macro using a textbox and the event ‘changed’ – clean meaning it is triggered after a whole code is input.

I like the behavior of a combobox with all the codes pre-loaded but the event ‘text changed’ fires on every character input. I tried to have the macro wait until the whole code is entered, but it is getting the input character-by-character instead of string-at-once.

#7

After reading the question a number of times, there seems to be no reason for the “Tally” field in the record. If this is a total of the records in the group, it is not record related and does not belong. If this is a sequential numbering within the group, every time an addition, deletion or change is made to the table, the “Tally” field would need to be re-evaluated and updated for all records within the table.

Counts can always be arrived at using SQL and really don’t need to be stored. The attached sample may be what you are looking for. There are two forms. Form “Code Group” - select a group in the listbox then select it using the button. Code records will be displayed in the Table grid & record count in box next to listbox. Don’t even need this since the record count is already present in the grid control.

The second form “GROUPTABLE” is just the two tables connected via relation. Selecting a record in the “Group” table displays records in the “Code” grid below it. Additions, changes or deletions can be done in either grid. Again, don’t see need for counts.

Sample: GroupCode.odb

Edit:

Ok. So after some searching for lost code (text selection) and a quick bite and last minute switch to your supplied table I am satisfied with this next sample.

Sorry about the confusion, but until you brought up the third party entry I was fixated on just the two mentioned tables. If you think that was difficult, you should try this side of the forum. Sometimes it takes a dentist to extract information.

This sample is the same as the previous with an added form (CodeGroup2) and two macros. One macro is to update the Tally and the other is to clear the Tally figures. I added some detail to the form:

  • The grid is not selectable
  • The entry to the text box is limited to three characters
  • Cursor placed right back in text box after an entry
  • Message displayed if item is not found
  • Maybe some more but I forgot already

Sample: RevisedGroupCode.odb

Math in Base Forms
#8

Okay, thanks for the response but you’re misunderstanding the task. Please refer to my comment with the attached example database, and I hope you will understand the task.

#9

Another function I added - entry can be upper or lowercase. The code changes it to upper case.

#10

If this answers your question please click on the :heavy_check_mark: (upper left area of my answer).

#11

THANK YOU for a working example of (what should be) a Simple Thing.

I had started off with a old code example that triggers an InitForm subroutine on the “when loading” event in a form (that much worked fine) which creates the database connection and is supposed to set a subroutine UpdateTable to be called upon an object event. An added challenge: some of the example references methods which are no longer in LibO 5.

Thanks to you, I have a solid foundation I can refer to and examine in detail.

#12

Finally! Glad it is what you are looking for. I know you can make adjustments for your needs. As far as code goes, it really is a fairly simple task compared to some others I’ve worked on. The task comprehension was harder for me! Had a temporary mental block!

#13

There is a reason for “tally” … i want to count the number of times the code E5C is encountered and entered, for example. I may or may not (by the time all the codes are tallied [that’s another word for ‘counted’]), have at least one observation of code E5E. But if I get code E5D, I need to count it and I’d like to see as I go if I have encountered at least one of every code in code group E5.

Example database is attached.
TallyDatabase.odb

ps: do you not know the song “Day-O (The Banana Boat Song)” ? or the term ‘tallyman’ ? Tallyman - Wikipedia

#14

Your sample is nothing more that the list in your question (already captured that in my sample). Also, there is nothing showing just what your end result should be. Your answer here makes even less sense. “… i want to count the number of times the code E5C is encountered and entered…”. This is a primary key according to you original question. It can only exist once in the table. How can you count that? What is missing in the sample I provided?

#15

Or is it you really want to see IF code E5C is entered for a CodeGroup? Whole different story than what you are stating!

#16

BTW I know what the word “tally” means. It is a matter of what it incorporates as to why it was asked.

#17

None of the various things I’ve tried have achieved the goal, so I didn’t include the attempt to use LibO BASIC or any of the versions of the Forms which didn’t work.

If it helps, think of this as a sort of inventory problem. I thought I have been very clear in my descriptions of the task and that the task is a simple one. I intend to create was is in essence a tally sheet.

#18

Ignoring 90% of what you wrote, here is what I gather from the above. Each “CodeGroup” should have the following associated "Code"s: xxC, xxD, xxE, xxF & xxG (where xx = same value of “CodeGroup”). Now as you are entering data, you want to know if one or more of these is missing?

#19

I am sorry if you find this confusing. I know code E5C, for example, can only exist once in the table. It is unique. I only want to keep a count of how many times I observe a code and type it in to tally it. All valid codes are known in advance, but not all codes will necessarily be observed and counted.

In different attempts, I built both list boxes and combo boxes with all the valid codes to help with data entry of only valid codes.

#20

(just got the comment) Your comment doesn’t really answer anything. What is being accumulated to be placed in the field “Tally”? You never answered my questions.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.