Ask Your Question

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

asked 2017-03-26 17:27:57 +0200

martouf gravatar image

updated 2017-03-26 17:30:30 +0200

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 <enter> 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. :( 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
edit retag flag offensive close merge delete


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

martouf gravatar imagemartouf ( 2017-03-26 17:30:28 +0200 )edit

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"?

Ratslinger gravatar imageRatslinger ( 2017-03-26 17:40:10 +0200 )edit

Is the purpose just to update the Tally?

Ratslinger gravatar imageRatslinger ( 2017-03-26 17:43:47 +0200 )edit

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. ??

EasyTrieve gravatar imageEasyTrieve ( 2017-03-28 04:02:13 +0200 )edit

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.

martouf gravatar imagemartouf ( 2017-03-28 05:28:44 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-03-26 21:29:14 +0200

Ratslinger gravatar image

updated 2017-03-27 06:36:09 +0200

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: C:\fakepath\GroupCode.odb


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

edit flag offensive delete link more


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.

martouf gravatar imagemartouf ( 2017-03-26 23:26:32 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-03-27 06:42:15 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-03-27 06:44:01 +0200 )edit

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.

martouf gravatar imagemartouf ( 2017-03-27 16:36:56 +0200 )edit

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!

Ratslinger gravatar imageRatslinger ( 2017-03-27 17:41:15 +0200 )edit

answered 2017-03-26 23:24:25 +0200

martouf gravatar image

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' ?

edit flag offensive delete link more


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?

Ratslinger gravatar imageRatslinger ( 2017-03-26 23:42:46 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-03-26 23:46:15 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-03-27 00:15:41 +0200 )edit

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.

martouf gravatar imagemartouf ( 2017-03-27 02:04:00 +0200 )edit

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?

Ratslinger gravatar imageRatslinger ( 2017-03-27 02:12:36 +0200 )edit

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.

martouf gravatar imagemartouf ( 2017-03-27 02:17:26 +0200 )edit

(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.

Ratslinger gravatar imageRatslinger ( 2017-03-27 02:21:03 +0200 )edit

No, I don't want to know how many are missing. I want to count how many times each might be entered as input data.

I had no intention of offending you by asking if you knew the word 'tally' and I apologize if you took offense. I would hope you pay attention to all the words (not just 10%) I make the effort to write.

martouf gravatar imagemartouf ( 2017-03-27 02:22:12 +0200 )edit

OK. The last comment may make some sense now. You have this table and every time you enter the code in a control on a form you want the counter to increase by 1?

Ratslinger gravatar imageRatslinger ( 2017-03-27 02:24:25 +0200 )edit

I really am not sure how else to explain I wish to create a tally sheet.

Shall I describe what a person would do with a piece of paper and pencil?

The paper would have on it at the start the list of codes and a space to the right of each code to make a tally mark next to a code. The person is handed a stack of cards, each card has one code printed on it. The only codes the person will see on the cards are known in advance and are already on the tally sheet.

martouf gravatar imagemartouf ( 2017-03-27 02:27:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-03-26 17:27:57 +0200

Seen: 441 times

Last updated: Mar 27 '17