No, I'm not saying that. It has countifs, though, and gets new functions more often than Access does, I think.
Probably the most exciting recent one is XLookup
I'm with you on data in the VBA project. I think mostly the OP wanted to learn how functions worked, which is a great worthy endeavor. This was just a random opportunity to try it (maybe not the ideal one nor the focus)
I took all of your guys input to heart and changed course to doing the table rather than hard coding. I thought of the same issue you guys brought up. What if me or another admin is not there on a given day and something important needs done? They would be straight outta luck.
I took all of your guys input to heart and changed course to doing the table rather than hard coding. I thought of the same issue you guys brought up. What if me or another admin is not their on a given day and something important needs done? They would be straight outta luck.
with the table driven approach, you still need to think of "how will they change the table anyway?" (same as the "how will I change the vba code?) - so make sure to include an Administrator portion of the database for managers, to do things like this - assign or reassign permissions. Self perpetuating, of course, since a person without permissions won't be able to access it - ha ha
with the table driven approach, you still need to think of "how will they change the table anyway?" (same as the "how will I change the vba code?) - so make sure to include an Administrator portion of the database for managers, to do things like this - assign or reassign permissions. Self perpetuating, of course, since a person without permissions won't be able to access it - ha ha
I have already made a mistake such as this when playing with UI permissions. Locked myself out of the entire design aspect and had to resort to a backup
I have already made a mistake such as this when playing with UI permissions. Locked myself out of the entire design aspect and had to resort to a backup
Ha ha ha - yep, it happens.
Just yesterday I needed to edit an Excel vba project where the form was showed modally when the workbook first opened. Only way to do it was to disable all macros in trust center - but that didn't work! Still haven't figured that one out actually.
I finally resorted to Ctrl+PauseBreak, to interrupt the userform, which I hate doing as I then have to restart my PC.
I have already made a mistake such as this when playing with UI permissions. Locked myself out of the entire design aspect and had to resort to a backup
I would have an extra not known useradmin login.?
I have tended to do that for Windows for many years.
If my profile is mucked up for some reason, I have another administrator username to use. Of course now they tend to be shown as Icons. I am talking about the days of W2K
I would have an extra not known useradmin login.?
I have tended to do that for Windows for many years.
If my profile is mucked up for some reason, I have another administrator username to use. Of course now they tend to be shown as Icons. I am talking about the days of W2K
I don't know if it's a coincidence that you are talking about functions but looking specifically about authorisation.
If you are interested in user authorisation, then the thing is, you rarely want authorisation to be at the user level. If you have dozens of forms, and hundreds of users, it's a big job.
It's better to put the users in user groups (actually put each user in multiple groups). Now if you have a user group called "System Manager", you can control access to a given form by requiring the user to be in the "System Manager" group. Now when you get a new user, you just put him/her in the System Manager group, and he/she gets all the powers of a "System Manager".
It's much the same idea but easier to use.
Then in each form's open event you have a function.
Code:
If not(CanIOpenThisForm, me.name) then 'me refers to the form name
msgbox "Sorry - you aren't permitted to use this option"
cancel = true 'just refuses to open. You might need to dismiss a 2501 error.
exit sub
end if
and your library function does all the work.
Code:
function CanIOpenThisForm(formname as string) as boolean
- checks the group memberships of the current user
- checks the required group membership of the form
- returns true or false depending on the access permissions to this form of the user.
end function
This general idea is in a video here. I had a general understanding of the concept of a function. But, watching this video and the "thinking out loud" nature of it helped me get a better grasp of their nature and when and how I might use them in the future.
A vba function is just a sub that can return a single value. If you don't want the function result then you can ignore it.
The idea in general is to keep subs and functions small, with a single task in each. It makes them easy to read write and re-use. It makes programmes easier to write. Pass arguments and parameters into the sub and function. Put comments in the subs to remind you. The subs and functions work like little black boxes. You don't need to know how they work internally - you just pass them the question and accept the answer. Of course you have to design them, but once they are working as you want, you can leave them alone. It's the same as when you call a function like instr(). You don't really know or care how instr() does what it does - You just know what parameters you give instr() and you accept the result. In A97, there wasn't an instrrev(), so we had to build our own. They added instrrev() to A2003.
So instead of long spaghetti code, you get smaller easily more read code.
so here my standard open event for a form first tests to see if the user is logged in. If you are still developing this function, or you don't actually need a login, just set it to return "true". within the function. That way it's easy to add the functionality if you decide you do want to include it further on. It also means your database works for the moment. Once you get the sub working correctly you can switch in the real function fro the placeholder function. Only after it establishes whether you are properly logged in does it test if you have permission to use the form. Again just set this to "true" while you are developing the code.
You could do these the other way round. I think it makes more sense to do check the login first.
Anyway, once your login functions are complete, they can be used in all your apps. Finally, I have a sub (not a function - although you could write it as a function) that remembers the previous (or sets a default) size and position of the form, and restores it to that position. Useful to allow users to design their own access desktop space as they prefer. This means that when you close a form, you also need a sub to save the position. SaveFormSizeAndPosition(me)
You could put the login code and the permissions code in a single function, but then it might actually become a bit more complex inside those functions. Now if you want to give the user a warning message, you can either put the message in the form, as noted below, or in the function, There are pros and cons for each, so it's a matter of taste.
Code:
Private Sub Form_Open(Cancel As Integer)
If Not validlogin Then
'advisory message
Cancel = true
Exit Sub
End If
If Not CanIOpenThisForm(me.name) then
'advisory message
Cancel = true
Exit Sub
End If
RestoreFormSizeAndPosition(me)
End Sub
A vba function is just a sub that can return a single value. If you don't want the function result then you can ignore it.
The idea in general is to keep subs and functions small, with a single task in each. It makes them easy to read write and re-use. It makes programmes easier to write. Pass arguments and parameters into the sub and function. Put comments in the subs to remind you. The subs and functions work like little black boxes. You don't need to know how they work internally - you just pass them the question and accept the answer. Of course you have to design them, but once they are working as you want, you can leave them alone. It's the same as when you call a function like instr(). You don't really know or care how instr() does what it does - You just know what parameters you give instr() and you accept the result. In A97, there wasn't an instrrev(), so we had to build our own. They added instrrev() to A2003.
So instead of long spaghetti code, you get smaller easily more read code.
so here my standard open event for a form first tests to see if the user is logged in. If you are still developing this function, or you don't actually need a login, just set it to return "true". within the function. That way it's easy to add the functionality if you decide you do want to include it further on. It also means your database works for the moment. Once you get the sub working correctly you can switch in the real function fro the placeholder function. Only after it establishes whether you are properly logged in does it test if you have permission to use the form. Again just set this to "true" while you are developing the code.
You could do these the other way round. I think it makes more sense to do check the login first.
Anyway, once your login functions are complete, they can be used in all your apps. Finally, I have a sub (not a function - although you could write it as a function) that remembers the previous (or sets a default) size and position of the form, and restores it to that position. Useful to allow users to design their own access desktop space as they prefer. This means that when you close a form, you also need a sub to save the position. SaveFormSizeAndPosition(me)
You could put the login code and the permissions code in a single function, but then it might actually become a bit more complex inside those functions. Now if you want to give the user a warning message, you can either put the message in the form, as noted below, or in the function, There are pros and cons for each, so it's a matter of taste.
Code:
Private Sub Form_Open(Cancel As Integer)
If Not validlogin Then
'advisory message
Cancel = true
Exit Sub
End If
If Not CanIOpenThisForm(me.name) then
'advisory message
Cancel = true
Exit Sub
End If
RestoreFormSizeAndPosition(me)
End Sub
I like what @gemma-the-husky said and how they said it, and will add that another thing that is a matter of personal preference is precisely how deep of a call stack (I'm probably not using that precisely correct but you probably know what I mean) you want.
Things that are EXTREMELY function-ized can actually be a bit harder to troubleshoot, in my opinion. I mean F8 is your friend, and keeps everything to a line of code which is nice, but mentally I think it's harder to troubleshoot something with 10 nested layers of functions than it is one with 5.
I try to strike a good balance, but I think everyone falls a bit different place on that continuum.
I don't set a hard limit for nesting but I'd guess that 99% of the time, I don't go more than 4 levels and probably 80% of the time, I don't go more than 3. You need to understand coupling and cohesion and what code is likely to be reusable and how abstract it needs to be to be reused at all. For my security function, I pass in the form Name and the action the user wants to do. i.e. Add, Change, Delete and I get back True or False. The UserID is obtained from the login form which hides itself rather than closing once the credentials are validated and the menu is opened.
I finally got around to looking at the sample you post Pat. I always like how your stuff looks. Looks much better than mine and yours is just a sample to help others learn .
I do not have an eye for graphical design.
Glad you like it. I'm still refining it. It is always slightly different when I use it for a client because I let them pick a color scheme and graphic elements.
Glad you like it. I'm still refining it. It is always slightly different when I use it for a client because I let them pick a color scheme and graphic elements.
I took all of your guys input to heart and changed course to doing the table rather than hard coding. I thought of the same issue you guys brought up. What if me or another admin is not there on a given day and something important needs done? They would be straight outta luck.
It's up to the users how they use the database you supply. They have to balance responsible stewardship of their own data, with ease of updating. It sounds like you are developing in house rather than externally, but it's the same. Someone has to understand the way the application works, and allocate responsibility to responsible persons.
I don't set a hard limit for nesting but I'd guess that 99% of the time, I don't go more than 4 levels and probably 80% of the time, I don't go more than 3. You need to understand coupling and cohesion and what code is likely to be reusable and how abstract it needs to be to be reused at all. For my security function, I pass in the form Name and the action the user wants to do. i.e. Add, Change, Delete and I get back True or False. The UserID is obtained from the login form which hides itself rather than closing once the credentials are validated and the menu is opened.
The nicest solution would be to be able to put all this functionality in a code library, and call that from your database. Then you can keep all your apps on the current version by issuing a new code library. In practice it's not the easiest thing to do, so you can end up with slightly different versions for each application you develop, and you don't necessarily go back and rework all the old ones up to the current standard, which Pat noted in her subsequent post.
Let's say you change your login form to let users add a hint for their password, and also to include a corporate logo or image on the login form. . It would be really good to be able to push that back to the clients by just issuing a new library, without the need to re-issue a whole new database.
(One hard bit I found was trying to have a location for the code library that might not be the same on a client machine, as on your development machine, within a .accde. I now think I could have got there by putting the code library in currentproject.path but I didn't realise that at the time. I might have another look some time)
I don't use add-ins for that reason. They are too difficult to manage. I keep databases with sample objects that I reuse as well as sample code but that's as far as I go with it.
