Making access split form read only - building safety business model

[jaryszek]

Hi Access Gurus!

I would like to continue a topic from here:

http://www.access-programmers.co.uk/forums/showthread.php?p=1515178#post1515178

but this will be a new one because i have new issues connected with Forms and Access Model.

I want to create a split database for consultants (about 15 users - 20 users) and for administrator (front-end and back-end).

This will be a very big combo application with split forms, possibility to login via login form, doing backups and most important - there will be only Access Runtime.
I would like to do it in very secure way and for this i need your help.

When user will open front-end file there will be option to login:
as user or as administrator.

In attachment please see my example split database, for now it is one front-end file and back-end.
There are two databases connected with this:

1. back-end (TeleIBS_wb.accdb) with 2 consultants' tables: tb_mwoj09 and tb_sle00 and with query table tb_razem (gathering all data from each table)
(back-end.jpg file)

2. front-end for one consultant- TeleIBS.accde (front-end.jpg file)

- linked table to tb_skle00 in back-end

- hidden query - tb_razem - I added query tb_razem table to front-end which is connecting to back-end query:

SELECT *
FROM tb_razem IN 'C:\Users\ljar01\Desktop\Makro\Makro IBS\Nowy System\TeleIBS_wb.accdb';

- Form tb_razem - it is a form with tb_razem query table.

1. First question - it is possible to do this Form read only? I have to lock table but with no chance to be changed by the user.

- Consultant Form tb_skle00 - it is a split form and this will be difficult:
I want to block for editing whole Split Form.

2. Second issue: Consultant should only have two buttons : one with possibility to add new record. And one with possibility to correct existing records (paste specific Ticket number and change existing data attached to it). It is possible? I want to block whole split form for editing. So only buttons should unblock the new one record or one choosen ticket number record.

You can ask me why do you want to do that?
Answer: Consultants can "accidentally" change some records and for reporting part and for our Complaint Team this can be a disaster.

I think that one time in the morning access back-end database should be copied: back-up should be done, but it is not enaugh for my client...

Please help Guys,
I will appreciate all your tips and clues.
Thank You in Advance,
Jacek Antek

 

[Frothingslosh]

First of all, instead of a login form, have you considered just having a Users table, getting their username from windows via Environ("USERNAME"), and shutting down rather than opening the main screen if they're not an authorized user? That will also allow you to assign access levels, which is a far better way of handling permissions than doing it by person. You also avoid having to save and retrieve passwords (probably either hashed or encrypted) that way. You're already using a runtime, so most hotkeys are turned off, and you can explicitly turn off hotkeys as well to be even more safe. (You can also just intercept and replace the hotkeys via an Autokeys macro, but that's a bit more involved.)

In most cases, setting an entire form read-only is overkill, especially since controls can be enabled/disabled (ie - allowed to have the focus at all) or locked/unlocked individually. Doing it at the controls level also allows you to have controls locked for some users, but unlocked for others (such as admins and developers).

For Q2, you make two buttons. One opens the form in data entry mode, one opens the form in edit (ie - standard) mode. You can even set it up so the second button goes straight to a selected record.

 

[Minty]

I'm not sure you have grasped our ideas correctly. You still seem to be having a table per consultant. This is not necessary or good practice.

Your split forms, whilst easy to create, are not actually the best way to handle the data you are trying to manipulate. You can create a sub form that looks like a split data form but is completely locked down.

This avoids all the concerns you have about inadvertent data deletion etc.

 

[Frothingslosh]

I have attached an example of something similar two what Minty is talking about, although a little more complicated.

Leaving aside the rest of the structure of the form, the two large boxes are both subforms. You can actually scroll the one on the left up and down (there just aren't enough reports here to show the scroll bar), and when one is selected, an invisible text box is populated with the ID for the report. The subform on the right is joined to that text box, so it populates with the data for the selected report.

Two subforms, and for all intents and purposes, they act like a split screen but both have form-level control. This particular example is not locked down simply because it's hidden behind an admin-only gateway, but it wouldn't have been difficult to set everything to read-only or even disabled.

EDIT: Just as an aside, I actually considered using a split form, but it turns out they don't play well inside subforms. That's right, you're seeing a form, a sub form, and a pair of subforms inside that subform.

 

[jaryszek]

Hi,

thank you for your swift answers!

First of all, instead of a login form, have you considered just having a Users table, getting their username from windows via Environ("USERNAME"), and shutting down rather than opening the main screen if they're not an authorized user? That will also allow you to assign access levels, which is a far better way of handling permissions than doing it by person. You also avoid having to save and retrieve passwords (probably either hashed or encrypted) that way. You're already using a runtime, so most hotkeys are turned off, and you can explicitly turn off hotkeys as well to be even more safe. (You can also just intercept and replace the hotkeys via an Autokeys macro, but that's a bit more involved.)

Yes, i have considered using Environ("USERNAME") function but it depends on my end database
Sometimes one consultant has to go to second consultant file and Environ not be suited here.

The best solution for now: 1 combobox and 1 texbox:

1. One with users' logins (default value = Environ("Username")
2. texbox to input password.

In most cases, setting an entire form read-only is overkill, especially since controls can be enabled/disabled (ie - allowed to have the focus at all) or locked/unlocked individually. Doing it at the controls level also allows you to have controls locked for some users, but unlocked for others (such as admins and developers).

Why this is killing? I blocked my datasheet view in Split Form (onle read only) and only buttons can edit this or when you are adding new row manually. I think that it is great, i did not think so that Access is so easy to use !

For Q2, you make two buttons. One opens the form in data entry mode, one opens the form in edit (ie - standard) mode. You can even set it up so the second button goes straight to a selected record.

I did this like this way:
button connected with macro:

Private Sub Form_Current()

Me.AllowEdits = False

End Sub

Private Sub Polecenie247_Click()

Me.AllowEdits = True
Me.Polecenie247.Caption = "Edytowanie"

End Sub

It is a good approach? I have only controls which can enabled editing specific record. Or with second button you can simple add new record. I think that this solution is very good and it is working!

I'm not sure you have grasped our ideas correctly. You still seem to be having a table per consultant. This is not necessary or good practice.

Minty, but if i have 15 consultants there will be safe if they can kill only their table rathen then all consultants' table.
I don't understand how 1 table is safer then a few detailed tables?

Your split forms, whilst easy to create, are not actually the best way to handle the data you are trying to manipulate. You can create a sub form that looks like a split data form but is completely locked down.

Why not? In Split form you can also writing your Tickets and editing datasheet in table and you have in the same time preview for your data. What is bad within this?

Thank you for you tips Guys!
Jacek

 

[jaryszek]

I have attached an example of something similar two what Minty is talking about, although a little more complicated.

Leaving aside the rest of the structure of the form, the two large boxes are both subforms. You can actually scroll the one on the left up and down (there just aren't enough reports here to show the scroll bar), and when one is selected, an invisible text box is populated with the ID for the report. The subform on the right is joined to that text box, so it populates with the data for the selected report.

Two subforms, and for all intents and purposes, they act like a split screen but both have form-level control. This particular example is not locked down simply because it's hidden behind an admin-only gateway, but it wouldn't have been difficult to set everything to read-only or even disabled.

EDIT: Just as an aside, I actually considered using a split form, but it turns out they don't play well inside subforms. That's right, you're seeing a form, a sub form, and a pair of subforms inside that subform.

Hmm it is nice!

But what if have one login screen and next only 2 tables will be open one next to another?

So you are witing login and password and after that you have 2 forms:
form for editing and inputing new data and in second tab all consultant's view.

And...that is all

Jacek

 

[Frothingslosh]

You shouldn't be killing tables at all. When you have multiple tables, all with the same data, then you have a poorly designed database. Same with having tables with data in their names.

Instead, you make one table with all the data and a 'SalespersonID' or 'UserID' or 'ConsultantID' or similar field. You set it up so that users load the data into that table, with their internal ID in that field, and lo and behold, you can use queries to ensure that each only ever sees their data. It's also easier to create because you don't need to generate these god-awful procedures to determine the correct table name needed and then write SQL statements on the fly using the procedurally-generated table names; instead, you have one query that just pulls the data, filtered by the user's ID.

Before you go any farther on this, I would *STRONGLY* recommend reading up on database normalization. The concept exists for a reason, Access is designed around that concept, and you're going to find your project getting harder and harder to implement if you continue forward without normalizing it.

 

[Frothingslosh]

Also, as a rule, you normally want to avoid deleting records. It's generally better to flag them so they can be excluded once you're done with them - if nothing else, this allows you to keep historical data.

That said, sometimes you'll find you do need to save temporary data, but that's generally for things like multi-stage imports or queries that need to be updatable but ALSO require aggregate data, which are two things that really don't play well together. Those will use temporary or staging tables, but only as a stopgap until the final data is created and saved permanently.

 

[jaryszek]

Thank you Frothingslosh!

Ok i understand you. I will try to make my databases appropriate.

Ok So the best solution is to have one big database and linked one big table to it.

And for each user i can create query (question is how ? in order to make only their table visible (based on login).

Problem is one: how to do autonumber of Tickets based on logins?
I mean when consultant skle00 adding the ticket to one big table they should have number like skle00_HR00001, skle00_HR00002....skle00_HR001500 etc.

Jacek

 

[jaryszek]

With flaggging is good idea.

When user is adding a ticket there will be a flag_H = 1, and i can show only tickets without this flag.
but problem is when you have tickets which you should corrected. what in this case?

Jacek

 

[Frothingslosh]

Put the login on one field, the ticket number in another. Then just auto-increment the ticket number. You can always concatenate them together for display purposes. (Never forget that the saved data does NOT have to look just like the data the user sees - in fact, often times it won't look anything CLOSE.)

Then you can write a query pulling what you need. Assuming you're using the query builder and want to limit returns to just what the user created, you would include that Consultant ID field, uncheck 'show', and in criteria you can have it look up the ID required. A common example would be a text box on an open form, visible or not, and referring to it in the query criteria basically like this:

=[Forms]![FormName]![ControlName]

Then include a field with a unique name (I'm calling it TicketNumber here, but it can't have the same name as any field in the query) like this:

TicketNumber: [ConsultantID] & "_" & [TicketID]

Viola! You saved skle00 as the consultant ID and 001500 as the ticket ID, but you're showing the user a ticket number of 'skle00_001500'.

 

[Frothingslosh]

With flaggging is good idea.

When user is adding a ticket there will be a flag_H = 1, and i can show only tickets without this flag.
but problem is when you have tickets which you should corrected. what in this case?

Jacek

Who said flags have to be binary?

The project I pulled that reports screen from has a 'process status' flag that is actually an integer that represents any one of a half-dozen different 'buckets' - basically, processing steps.

Fields used as flags are there specifically to allow sorting and filtering - they serve as data and process control, nothing more. You write queries to look up what you need, and flags serve to allow you control that you wouldn't have in the actual data user-facing data.

If I were creating a form that allows an existing ticket to be corrected, I would put a combo box at the top of that form. The combo box would have a zero-width form with the primary key value used for the tickets, and then a display of however you look them up. (Note that the PK should NOT be any data you actually use, so the aforementioned shkl00_001500 should under no circumstances be your primary key. if it is, you really should change it. If you don't, then on the bright side, you won't need to have a hidden column in the combo box, but on the bad side, if you ever have to change the PK, you could break all SORTS of data.)

Anyway, I would also have a command button that moves the form's bookmark to the selected ticket. Viola.

It's easier to do than it sounds, but don't worry - if you create the form based on the appropriate query or table and then drop a new combo box in the form header, an Access wizard should pop up asking what you want to do, and one of the choice should say something about using the combo box to find a selected record. Just use that wizard.)

 

[jaryszek]

Put the login on one field, the ticket number in another. Then just auto-increment the ticket number. You can always concatenate them together for display purposes. (Never forget that the saved data does NOT have to look just like the data the user sees - in fact, often times it won't look anything CLOSE.)

wow it is too fast for me

(i am going to holidays and i am back on 8th of January so i will answer after my return).

In my table i can add autonumber field and already i have login.
Ticket Number is already also in my table.
(I would like to first paste my data from excel to access and from one moment add this autonumbering in Access).

Please see Main table.jpg attachment.

And how can i concatenate Login and Auto ID number?
It can be done only by doing a query like:

SELECT tab_all.ID, [ID] & "_" & [Login] AS Expression, tab_all.Login
FROM tab_all;

 

[Frothingslosh]

First: You should NEVER just use ID as the name of your ID field. At the very least, that really should be LoginID. (Mainly because each table gets 'ID' as its default PK, and it gets confusing fast. If you name them 'TicketID', 'LoginID', 'WhateverID', then you can tell what they ARE at a glance, especially in a query.)

In a SQL statement, it would be more or less what you posted, although you don't need to include tab_all.ID and tab_all.Login themselves unless you want to return them individually as well. The following is perfectly fine, assuming you want something like 1_ljar01 as your ticket value:

SELECT [ID] & "_" & [Login] As TicketID FROM tab_all;

Obviously you'd need to add in any other fields you want returned.

Personally, I'd do Login and then ID simply because that is a bit easier to read, but I don't know if that's an option for you.

Keep in mind that you'd never save the concatenated value; you'd only save ID and Login. Values like that should always be calculated on the fly, not saved. Saves space that way.

Anyway, you basically had it right for an actual SQL statement. If you use the query builder, then you would put the following in the 'Field' box in the QBE grid:

TicketID: [ID] & "_" & [Login]

 

[jaryszek]

First: You should NEVER just use ID as the name of your ID field. At the very least, that really should be LoginID. (Mainly because each table gets 'ID' as its default PK, and it gets confusing fast. If you name them 'TicketID', 'LoginID', 'WhateverID', then you can tell what they ARE at a glance, especially in a query.)

In a SQL statement, it would be more or less what you posted, although you don't need to include tab_all.ID and tab_all.Login themselves unless you want to return them individually as well. The following is perfectly fine, assuming you want something like 1_ljar01 as your ticket value:

SELECT [ID] & "_" & [Login] As TicketID FROM tab_all;

Obviously you'd need to add in any other fields you want returned.

Personally, I'd do Login and then ID simply because that is a bit easier to read, but I don't know if that's an option for you.

Keep in mind that you'd never save the concatenated value; you'd only save ID and Login. Values like that should always be calculated on the fly, not saved. Saves space that way.

Anyway, you basically had it right for an actual SQL statement. If you use the query builder, then you would put the following in the 'Field' box in the QBE grid:

TicketID: [ID] & "_" & [Login]

thank you Frothingslosh !

I am sure that this will be Login (first) and ID (second).

Keep in mind that you'd never save the concatenated value; you'd only save ID and Login. Values like that should always be calculated on the fly, not saved. Saves space that way.

Wow this is very useful information.

Ok to sum up (how it should look like?):
1. frond-end :
- linked table to whole data table(Main Table) in the back end
- built forms on that to service Access Runtime

2. Back end:
-Main table with consultants' logins
- query table with autonumbers of Tickets and logins.

and that is all??

So linked table in front-end should be linked to query table in back-end with autonumbers?

Jacek

 

[Frothingslosh]

Not quite.

Put the autonumbers into the tickets and logins table themselves - they refer to the tickets and logins, and thus logically should be part of those tables. ID fields are normally used as primary keys (look up 'surrogate key' vs 'natural key'). Keep in mind that the values of auto-number fields CANNOT be changed in Access, so there will be no alteration of your ticket numbers if you go that route.

Second, the back end should ONLY contain tables. Your queries are actually stored in the front ends (the runtime), along with any forms, macros, and code modules you may have. The only tables I put in front ends are what I call 'front end option' tables - things that need to be retained and are specific to a given user but don't need to be saved on the server. Most of the time I don't need them.

The front end gets linked to the back end (External Data - Import - Access - Link external tables), and from that point end will treat the linked tables as if they're in the front end. The main purpose here is to allow each user to have their own copy of the front end without having to copy the actual back-end data.

 

[Frothingslosh]

Here's an example of a user table (what you'd be using as a contractor table): The user table on one of my projects.

(This is, by the way, a case of 'Do as I say, not as I do.' I inherited this structure and didn't have the time required to fix it - had I built the table, it would absolutely be UserID, not ID.)

FYI, RoleAccess is actually a foreign key that refers to another table with user permission levels. Again, the naming is an artifact from the previous developer.

Normally there would be descriptions, but this is actually linked to SQL Server, and I couldn't get the descriptions to carry over.

 

[jaryszek]

Thank you very much for your help !

Ok, Sorry if I am repeating...(i have to fully understand this).

Table with logins and IDs should be in the Back-end yes?

But query to this should be in front end? (SELECT [ID] & "_" & [Login] As TicketID FROM tab_all in back-end.

I have to imagine that.

You are talking about separately table with logins , IDs and autonumbers but i can't imagine where this should be.

Could you please write simply where have to be this?
To write results?

Jacek

 

[Frothingslosh]

1) *ALL* of your tables will be in the back end. All your queries will be in the front end. In a split Access database, ONLY your tables go in the back end. (If you're using a SQL server back end, then things are a bit different, but it's still primarily true.) You can link tables, but you cannot link queries, only import them or run them via a somewhat complex VBA setup that's more bother than it's worth.

When you have linked the tables in the back end to the front end, they will appear in the front end and be available to your queries, your forms, and your code. The only real differences will be that they have a slightly different icon, and you cannot modify their structures from the front end.

Seriously, though. If you have tab_all in the back end and linked in the front end, then your queries in the front end will see and work with them just fine. Build tab_all in the back end. Link it through the External Data tab (LINK, not import). Write that query. Save it in the front end under whatever name you want. Run it. You won't even be able to tell that the table is in another database.

2) I think you need to step back and look at basic table design. At no point did I say you should have a separate table with IDs, logins, and autonumbers.

Every table should be about one thing, and everything in that table should be about that thing. In an RDBS like Access, every table should have a primary key, meaning a field that uniquely identifies each record. Most of us here will recommend using an Autonumber field as your primary key, and naming it along the line of 'UserID', 'LoginID', 'TicketID', etc. That ID normally shouldn't be used for anything else, since it cannot be changed once assigned.

That means your Login table should have a LoginID field as its PK, your Tickets table should have a TicketID field as its PK, etc, and these should be autonumbered fields.

For your query, I'm using the fields YOU provided, and am assuming you have fields called 'ID' and 'Login' in a table called 'tab_all'. I simply showed you how to concatenate - how to turn two values into one string. Assuming tab_all is where you contain both the ticket number and the login ID's (which is really bad design), then you would concatenate ID and Login to get that Login_ID format you mentioned.

For table layout, look at that screenshot I provided in post 17. Note that it has an ID field PLUS everything I'm tracking for the users. Trust me that that's the same for all of my tables, although the ones I laid out are named the way I told you, TableTopicID.

 

[jaryszek]

Thank you Frothingslosh for your full answer!

) All your tables will be in the back end. All your queries will be in the front end. In a split Access database, ONLY your tables go in the back end. (If you're using a SQL server back end, then things are a bit different, but it's still primarily true.) You can link tables, but you cannot link queries, only import them or run them via a somewhat complex VBA setup that's more bother than it's worth.

When you have linked the tables in the back end to the front end, they will appear in the front end and be available to your queries, your forms, and your code. The only real differences will be that they have a slightly different icon, and you cannot modify their structures from the front end.

Seriously, though. If you have tab_all in the back end and linked in the front end, then your queries in the front end will see and work with them just fine. Build tab_all in the back end. Link it through the External Data tab (LINK, not import). Write that query. Save it in the front end under whatever name you want. Run it. You won't even be able to tell that the table is in another database.

Ok this I understand. Almost So this query with autonumbers should be in front end and via connection to back end should write the results.

I have to also use here some function to get autonumbers for each consultant. for example ljar01 will have number from ljar01_00001,ljar01_00002, skle00_000001, ljar01_00003, skle00_000002 so each consultant should have number automatically but based on his login. Maybe DCount function will be good for that.
And how to write these autonumbers to my Main Table from front end?

I know what is Primary Key. But i don't exactly yet how to use it.

then you would concatenate ID and Login to get that Login_ID format you mentioned.

but concatenation of these two tables forces to do query table in the back end and this is not what we should have in back-end (only tables).

I will try tomorrow or on thuesday to build sample database and with your enormous help i think that maybe I will create something useful

Warm Regards,
Jacek