Humanipro application (1 Viewer)

[Pat Hartman]

Much more complicated is limiting different users ability to do certain task within a form, (eg delete records). as every form will need manual coding to test the function and the user.

Take a look at my simple security. That's the point of the add/change/delete/view options. Each relevant event calls security as the first line of code in one of the four relevant events and either allows the event to proceed or cancels it.

 

[gemma-the-husky]

Take a look at my simple security. That's the point of the add/change/delete/view options. Each relevant event calls security as the first line of code in one of the four relevant events and either allows the event to proceed or cancels it.

Yes, but doesn't that mean you have to modify inside the form to call the appropriate function and determine what privileges are needed to execute the event? It's easy if the atomic event is "the form". It's much harder if each form contains multiple decision points. Don't you also need to explain to users how to manipulate the settings to authorise the users.

 

[MarlonVisser0408]

You gave us a bunch to "chew" on and you can probably expect comments to take a while in coming. I saw the above and want to comment on the specific implications. "Group" is unfortunately one of those words that runs into ambiguity in the Windows environment. The commonplace use of the word "group" - meaning some number of people who will use the application in a similar way - can be implemented by determining a User ID in some way and having a user table that lists the groups (or perhaps you mean roles) that each user would play.

The idea is that AccessUserGroup will hold the data from both the group and the AccessUserID. The form AccessUserOption will hold the options inside of it. In my head, they're not often touched. The Group will hold all the things you need to do your job and therefor you don't need to touch the AccessUserOption. The only reason that there's a second layer of permissions is so granting (temporary) access to a part of the application will be a lot easier and has a lot more ways of finetuning, all the while that you don't have to touch a group role or add one.

The complication is that Windows has groups, too, and they actually have the same meaning - but very different granularity - as the "role" concept that we often see in Access apps. The difficult part is that there is no built-in Access function to test Windows Group Identifiers. You must use one of the Win32 API calls and the one I looked up is ugly. I have used a single Windows Group Identifier as a way to gain/grant permission to open an Access app, but the user role within Access is best determined totally within Access. You would use the user's Domain ID to identify the user and then look up the user's role in a table. There are several relatively easy ways to more reliably determine a user ID if you are in a domain-based environment, and I might suggest some articles by our member isladogs to be an excellent - though not the only - source on that subject. Our SEARCH facilities allow you to put in a topic AND a username for your search, so that should help narrow down some articles.

Thats a very good point to make. I'll need to change the names to represent them being different kind of data than the Windows/Microsoft native data carrying the same name.

I've talked at length about some security options with a friend of mine. He's really into helping with small parts and coding at the background and he already looked into my situation for giving me some security outside of Access. There's multiple machines that I'm using throughout the week and some of them don't have the domain setup correctly. He's looking into making a custom set of code and help me get through a proper way of securing things - it's the reason I'm not too worried about that part.
One of the reasons is that we're going to put that part through Windows and my Microsoft environment for liability reasons. *If I understand it all correctly. It took him a flat 3 seconds to start talking about things that went straight over my head

I'm responding to a narrow subsection of your post simply because it will take a while to digest the whole thing and this is something I could offer quickly. If you really mean from that quote that you want to discuss a user's role in your company as a guide to how they would use the app, the keyword to look up in this forum is "role-based" (with or without the hyphen).

Yeah, that's the perfect thing to do here. At least now we got a better idea of some of the difficulties, the scale and some of the reasoning. I hope it wasn't too much of a tough read

 

[MarlonVisser0408]

@MarlonVisser0408

The trouble with spreadsheets is that they don't offer you the strong data-typing at the column level, that you get with a database field.
Their really is no comparison. It's pretty bad that spreadsheets get used for all sorts of things where a database would make much more sense, especially in commercial and even government applications. The problem is that a database requires a much deeper understanding than a spreadsheet, and a casual user just can't achieve credible results.

For instance though, a database is always multi-user, unlike a spreadsheet.

Yes, this I found out. Part of what made me wonder is the ignorance about if it would be able somehow to have both the applications communicate in a better way. Now that I found out that I should not look at them as compatible; things got really easy - no can do.

The idea came from the fact that IMEX=0 worked the way it did. The data that I originally tested the idea on was a passive excel dataset containing area information (provinces, municipalities, cities, neighborhoods). Considering that there was not really any other data then ID's and short texts combined with it being a 'passive' dataset, everything worked beautifully.

This created the illusion that I could implement this to at least the point of me being more knowledgeable about things. You must understand that I'm currently going through the face that I'm redoing a lot of the things I previously did - simply for knowing better. The idea of using excel was more of a way to keep it modular and reduce the amount of stuff that I need to redo. Everything that I connect more data - it updated the spreadsheets and it created a 'database' in its simplest forms with correct data.

As said, I know better now. It's always been about trying to get this whole down to smaller (read: more understandable) parts and less about the database itself.

Much more complicated is limiting different users ability to do certain task within a form, (eg delete records). as every form will need manual coding to test the function and the user.

That's interesting and makes me want look into it, haha. I thought that it was as simple as checking if statements are true? Ignorant is bliss

 

[MarlonVisser0408]

Take a look at my simple security. That's the point of the add/change/delete/view options. Each relevant event calls security as the first line of code in one of the four relevant events and either allows the event to proceed or cancels it.

I'm pulling it apart as we speak! Thank you a lot for this; it's perfect

 

[Pat Hartman]

You're welcome. Glad to help.

 

[Pat Hartman]

Yes, but doesn't that mean you have to modify inside the form to call the appropriate function and determine what privileges are needed to execute the event? It's easy if the atomic event is "the form". It's much harder if each form contains multiple decision points. Don't you also need to explain to users how to manipulate the settings to authorise the users.

There are only four decision points in my sample - BeforeUpudate, BeforeInsert, Current, BeforeDeleteConfirm. If you want to control functionality within a form so that user A can update fields a,b,c and user B can update b,f,d - that is WAY more complicated and not at all what I suggested. That kind of control requires additional tables and a call to security at EACH point of decision. I don't recommend it. It is better to make separate forms. But, once you have multiple forms to update the same data, you have to call common validation code from each form. Duplicating it will be a maintenance nightmare.

 

[MarlonVisser0408]

There are only four decision points in my sample - BeforeUpudate, BeforeInsert, Current, BeforeDeleteConfirm. If you want to control functionality within a form so that user A can update fields a,b,c and user B can update b,f,d - that is WAY more complicated and not at all what I suggested. That kind of control requires additional tables and a call to security at EACH point of decision. I don't recommend it. It is better to make separate forms. But, once you have multiple forms to update the same data, you have to call common validation code from each form. Duplicating it will be a maintenance nightmare.

Thank you again for the assistance! It gave me the push I needed to make the solution to my liking. It's good for me to have some ideas reinforced; I'm always scared that my inexperience will cause me to overlook possible solutions. Your post with the form did exactly that!

Of course thanks to everyone else too that helped me to get more confidence and direction into solving this problem nicely.

 

[Pat Hartman]

You're welcome. This version of simple security is pretty easy to implement since there are only ever 4 decision points per form and they are well defined. You just insert the call to security as the first line of code in each of the four relevant event procedures and that's it.

 

[MarlonVisser0408]

Logbook/Notes System

Introduction​

I’ve been preparing for this part while avoiding it until now: the logbook/notes system.

Concept​

The idea is to integrate multiple logbook entries across various pages and records throughout the application. For example:

• In the Contact Relation Manager (CRM), we manage all contacts, including potential sales leads, residents in refugee shelters, and stakeholders of various locations. This list is filtered based on assigned labels and user/group permissions, which we’ve discussed previously.
• For instance, if I am assigned the role of a sales broker, I should only see leads categorized under the CRM’s “Leads” label, while being restricted from personal logs or unrelated data.

Current Approach​

My plan is to create a form for every record and page that's going to have a logbook section to it. While this will be time-consuming, I anticipate that structuring it properly from the outset will be less of a headache in the long run. (I can make it, connect it and then use the 'hide' function to never look at it again). Each logbook will have a standardized format, making query duplication simple—just a matter of copying and pasting with normalized names.

This setup ensures that the logbook remains functional, even if the application is incomplete. It also allows me to insert historical data early on to validate system behavior before moving on to other sections.

Implementation​

This structure will result in 10–15 logbooks connected to different pages, which will then merge into a unified query. Normalized data entry will minimize complexity during merging.

Potential Concern​

I’m concerned about the performance impact of handling extensive long-text fields in a large query. However, I believe that filtering queries at a binary level before generating content could mitigate this issue. Does this approach make sense?

User Permissions and Filtering​

The merged logbook data will be filtered based on user and group permissions, as discussed earlier. This ensures that sensitive data is not exposed in an unfiltered query. By pre-filtering queries, I can seamlessly integrate them into pages and records while providing a reply section in the footer. This allows users to add relevant information without navigating away from the record, ensuring data consistency and efficiency.

Automated Entry Registration​

I would love to automate data tracking for log entries. Ideally, each record should include:

• CreatedBy
• CreatedOn
• ModifiedBy
• ModifiedOn
• ChangedData

This would greatly aid in tracking data integrity, rolling back errors, and reducing the need for manual log entries. For instance, if a user schedules a dentist appointment for a resident or orders a taxi, the system should automatically generate a log entry without requiring manual input.

Learning Path and Challenges​

I’ve struggled to find solid learning resources on automating this type of data logging in Access. While searching YouTube, I encountered more unanswered questions then solutions and it drove me crazy. I'm pretty sure that I'm using the wrong keywords to get to the information that I need.

Example Scenarios​

• Game Development Example: In a turn-based system, pressing a button should create a new table entry. Each subsequent button press should append another entry, generating data based on the previous one.
• Company Inventory Example: A live inventory system should automatically update stock levels when new items arrive or are used. If stock levels drop below a threshold, an automated request should be sent to the order form, ensuring a continuous inventory cycle.

I’d appreciate a pointer towards a place where I can learn about these problems. Does anyone know a good course or an author?

 

[Pat Hartman]

My plan is to create a form for every record and page that's going to have a logbook section to it. While this will be time-consuming, I anticipate that structuring it properly from the outset will be less of a headache in the long run. (I can make it, connect it and then use the 'hide' function to never look at it again). Each logbook will have a standardized format, making query duplication simple—just a matter of copying and pasting with normalized names.

A logbook is a logbook and you should never duplicate tables. If you were Amazon, would you create separate product tables for each type of product you sell because they have different attributes? NO, you would not.

Each contact is with an entity so you have an entity table and a logbook table. If you want to break out the log reports, you can do it by entity type.

You can't enter old logbook data before you enter the Entities. Parent records ALWAYS get loaded before dependent child records.

If you have to do extensive searching of long text fields, I think there are special tools in SQL Server. Jet/ACE do not have such tools.

Queries filter your data depending on how you want it reported and who is making the request. If certain users are restricted to certain entity types, then you need to set up security to handle this.

This would greatly aid in tracking data integrity, rolling back errors, and reducing the need for manual log entries. For instance, if a user schedules a dentist appointment for a resident or orders a taxi, the system should automatically generate a log entry without requiring manual input.

This doesn't happen automagically. You need to write code to make it happen. Sometimes you can use Triggers but code in forms might be more flexible.

Game Development Example: In a turn-based system, pressing a button should create a new table entry. Each subsequent button press should append another entry, generating data based on the previous one.

I wouldn't use Access to create a game. If each record is linked to the previous entry, you need to add a FK to the previous entry as you add the current entry. By definition relational tables are unordered sets of data and no row has any relation to any other row. To overcome this requires user coding.

Company Inventory Example: A live inventory system should automatically update stock levels when new items arrive or are used. If stock levels drop below a threshold, an automated request should be sent to the order form, ensuring a continuous inventory cycle.

Inventory levels are NOT stored. They are calculated when you need them. When you fill an order, you calculate the remaining quantity using a query and if necessary generate a replenishment order.

Do not restrict your searches for Access solutions. ANY solution created with a relational database will show a reasonable schema based on that application's business rules. The thing to be careful of is restrictive business rules. There will NEVER be more than 3 items on any order. The user may tell you this but bells should be clanging very loudly in your head because you know that no order has ever had more than three different items because the company only sold 3 products. Duh! Once they add a fourth, then that business rule vaporizes. So make sure the rules you are implementing are not red herrings.

 

[MarlonVisser0408]

A logbook is a logbook and you should never duplicate tables. If you were Amazon, would you create separate product tables for each type of product you sell because they have different attributes? NO, you would not.

This very much makes sense; it was the reason I was already struggling with this idea. Good to have this confirmed. So I'd be way better off making a single logbook and adding some automated values to the way the entry was made (if it's made on a residential record, we already have the data needed to do this). I believe that what you're saying is the thing that I initially thought would be the best way to implement it.

Queries filter your data depending on how you want it reported and who is making the request. If certain users are restricted to certain entity types, then you need to set up security to handle this.

Yes, the intention is to precode all the security into it to the point that you can use the UserRecord and UserGroupRecord pages to change someones access based on booleans - what we discussed earlier.

This doesn't happen automagically. You need to write code to make it happen. Sometimes you can use Triggers but code in forms might be more flexible.

I shouldve been more carefull with my words. I want to precode it all to the point that the front-end prints the data automatically, based on how I got it coded on the backend.

I wouldn't use Access to create a game. If each record is linked to the previous entry, you need to add a FK to the previous entry as you add the current entry. By definition relational tables are unordered sets of data and no row has any relation to any other row. To overcome this requires user coding.

It was something I was working on at the start. A project to play with and to learn access by running into various problems and gaining some experience this way. I'm fully aware that Access is the wrong platform to be distributing a game from, but as far as I know; you could do it for the sakes of just learning

Inventory levels are NOT stored. They are calculated when you need them. When you fill an order, you calculate the remaining quantity using a query and if necessary generate a replenishment order.

Could it be as simple as having a single table that's working with positive and negative values?

Do not restrict your searches for Access solutions. ANY solution created with a relational database will show a reasonable schema based on that application's business rules. The thing to be careful of is restrictive business rules. There will NEVER be more than 3 items on any order. The user may tell you this but bells should be clanging very loudly in your head because you know that no order has ever had more than three different items because the company only sold 3 products. Duh! Once they add a fourth, then that business rule vaporizes. So make sure the rules you are implementing are not red herrings.

No, I'm very aware of this. One of the reasons that I'd very much like to save this kind of information is so I can add simple checks to notify me of interesting input. For example, it'd be very weird to see an entry being made at 02:00AM. I'd very much like to know if someone makes a log at 02:00AM and the details surrounding this event.

I think it'd be very wise for me and helpful for the user to have this done correctly and nicely. As said, I don't care the investment from my side of the story; I need the user to have a good experience rather then me having a good experience

 

[Pat Hartman]

Could it be as simple as having a single table that's working with positive and negative values?

To be auditable, inventory needs to be handled with a transaction table. Every item movement in/out/adjustment is a transaction. I use natural signs. A positive number increments inventory and a negative number decrements it. Sum the transactions to find the current balance. That means a sale with a positive sign is actually a return. But usually you would have discrete transaction types so you don't have to work these out by analyzing the sign of the transaction.

If you have a huge set of data with lots of movement, a few years down the road, you might want to archive details and replace them with monthly summaries once you're sure the individual actions are no longer relevant.

 

[MarlonVisser0408]

To be auditable, inventory needs to be handled with a transaction table. Every item movement in/out/adjustment is a transaction. I use natural signs. A positive number increments inventory and a negative number decrements it. Sum the transactions to find the current balance. That means a sale with a positive sign is actually a return. But usually you would have discrete transaction types so you don't have to work these out by analyzing the sign of the transaction.

If you have a huge set of data with lots of movement, a few years down the road, you might want to archive details and replace them with monthly summaries once you're sure the individual actions are no longer relevant.

Thats a good point to set my head towards. As soon as I am able to understand how the mechanics work, I feel more comfortable doing work that I know how to do, while learning what I need to learn here - I hope that makes sense. Thank you for this, this is exactly what I needed.

The idea of the automated logs and most of the data, I want to eventually make it so that every X amount of months, we convert what we have to excel and basically get rid of it. There's not enough moments that we look back to history beyond the threshold that I need to have the data ready on hand. I rather have the database to run smoother, this collective time saving will outweigh us doing it manually at times.

95% of the database will exist out of very wide data. It'll be a lot of tables, containing sometimes 20 entries, sometimes 300-400 - but never big amounts of data. On the taller data, I'll make sure to cap the amount of items it'll hold.

 

[Pat Hartman]

I rather have the database to run smoother, this collective time saving will outweigh us doing it manually at times.

It is rare that you actually need to archive data by removing it from the live tables. This is more of a problem for Jet/ACE which have a hard limit on file size of 2G. Usually you just use an ArchivedDT to keep the data out of most queries so I plan on that from the beginning for tables I know will become large. That leaves my options open. I can archive and leave in place because from day 1 most queries ignore records with non-null archive dates. Or I can actually remove if that makes more sense down the road.

 

[Pat Hartman]

95% of the database will exist out of very wide data.

Normalized tables are narrow and long. If yours are short and wide, you are probably not normalizing correctly.