VBA question

  coolteentom 14:38 29 Oct 2007


Just wondering if anyone has any knowledge of VBA?

I am currently in the middle of creating a database on Access. I have successfully created a 'login' form which takes a username, password, then when the 'GO' command button is pressed, I have some VBA code which ensures the username and password match with the data in the 'Employee' table. The user is then led to a Control Panel, which I would like to contain buttons for the user to change their personal details, access their personal timetable etc.

However, I am unsure of how to relate the buttons to the specific employee that has logged into the system.

Has anyone got any ideas, or any info on somewhere that may be a better place to post this?

Many thanks, Tom

  silverous 16:02 29 Oct 2007

Why don't you use Access' built in user facilities rather than creating your own login/password processing?

Out of interest, how do you stop the user from browsing other's data in the tables of the database?

You can make it so the buttons have code that opens up forms on specific records relating to that user, but as per the above have you thought about how to stop them looking at others?

How about linking the access username with their network login name (assuming they log into the network with a userid) ? You can then make all the queries restricted to querying for the current user.

Here's some info about securing access database generally:

click here

See also:

click here

  coolteentom 10:44 13 Nov 2007

Brilliant, thanks a lot for the help silverous, I finally got round to applying the code to my project, and after messing around a bit, got a successful login system.

One last question - is it possible to create a simple allocation system in which I put in the details of a job on the database, and the job number is automatically shared out and entered between 2 different types of employee 'Designer' and 'Programmer'?

  silverous 16:59 13 Nov 2007

Hi Tom,

Glad it worked. RE: your 2nd question, I'm not clear on what you want. When you say "automatically shared out and entered between..." - how would that manifest itself?

Do you have a job table? Will it always be shared between the 2 different types of employee? Do you want it to choose specific employees e.g. "job 1 is shared between Jim and Bob" "job 2 is shared between Jane and Jennifer" etc. ?

I'm not clear on what you want exactly, will need more info about what you want the end result to be/look like.

Sounds interesting though, happy to help.

  coolteentom 10:47 20 Nov 2007

Hi Silverous, again, thanks for all your help, much appreciated!

Yeah, reading through my previous message, it really doesn't make much sense - probably because I wasn't entirely sure of what I wanted myself xD... Have been thinking about it, and basically am using the 3 relevant tables:

Jobs. [Primary Key = Job_No]

Employees. [Primary Key = Employee_No]

Job Allocation. [Linking table]

I am trying to create a command button in a form that automatically puts the job number into 1 programmer and 1 designer record, as this is a website designing system project.

Not sure if this is possible... maybe by grouping 1 designer and 1 programmer into teams of 2 or something?

Any ideas? Thanks!

  silverous 14:09 20 Nov 2007

OK. I've got a clearer understanding now.

Are you comfortable with how to actually add the job to the two employees in terms of inserting rows in those tables based on the button press?

I can help with that bit if not but the bit it sounds like you want help on, maybe, and the bit I'm still not clear on is how you decide which programmer and designer. Should it be based on existing job workload (kinda intelligent) or is it random (it could be) ? I'm assuming there's say:

Jeff, Jim, Sally, Rupesh (Designers)

Paul, Steve, Jane, Jim (Programmers)

and somehow you have to allocate a job to one of each ?

There are almost certainly fairly complex algorithms out there for allocating work but we could choose one of each at random and in theory across all jobs they'd all get picked I guess?

Is this making sense to your requirement?

  coolteentom 19:58 20 Nov 2007

Pretty much silverous, so just to clarify, I need both a designer and a programmer to be allocated to each job.

So, using the names above, if I was to input details for a new job e.g. JOB #019, into a form and click on an 'Allocation' button then it would be allocated to both Jeff and Paul.

Job #020 > Jim and Sarah

Job #021 > Sally and Jane etc...

Possibly on a rota? Maybe If Jim and Sarah were called team "1", then sally and jane were called team "2" and it goes through each team, then starts back at the beginning?

But yeah, basically, that's it... I'm not particularly fussed about the method, and we could use the random allocation, if that is easier?

Also, if you could get give me a hand with the initial button press process of inserting rows, that'd be fantastic.

Cheers =D

  coolteentom 20:01 20 Nov 2007

Wow! I'm tired xD...

(Sarah actually being Steve!)

  silverous 22:54 21 Nov 2007

OK, I can definitely help you with this but it may have to be at a high level, step at a time with snippets of code that you adapt, and may need to go offline as this is getting quite specific. Will need to do in manageable chunks also - alternative is that I end up developing it for you which isn't good for either of us and probably won't fit your needs!

I like your team numbering idea but it assumes e.g. that Jim and Sarah are always a team. Is that practical in reality? Does the organisation concerned have a concept of a "team" that always work together? Your originally question implied a separately allocated programmer and designer - now we are talking about fixed teams?

If a team isn't fixed then it isn't something you can refer to in the database as an entity.

Here's some thoughts if you go with the allocating employees rather than teams approach:

i) On your allocation table you can have 2 id fields - designer_id and programmer_id. If more people might work on a single job than 2 you'd probably want to make this table more flexible.

ii) Put a "type" field on the employee table - can be either programmer or designer for now.

iii) If allocating individuals how about allocating those with the minimum workload currently (or actually for all time - we don't need to restrict it to "open" jobs (assuming jobs end) but we could if that would be better suited? That can be found via the allocation table and the employee table joined. So, I'm going to talk code now - if you need more detail on any one aspect let me know but I can't write the whole lot for you without your exact database so it will work best if I fill in any gaps you highlight after reading this I think - make sense?.

iv) You add a button to your Job form with a caption of "Allocate". Behind this, you need to add some code that finds the first programmer and first designer with the minimum workload. Initially in an empty allocation table it could be any of them but we need one.

Taking a step back, and thinking in terms of logic rather than VBA code (best place to start), we need a query that the VBA will run which looks at the allocation and employees (let's take programmers for now as a starting point) to figure who to allocate.

Here's an access query that does the job:

click here

It is quite a big image so you may have to zoom in.

I think that's probably enough for one night as I'm tired now!

Hopefully this is a good start, I tested the query and it does always return the first programmer with least work (if there are two it will use lowest employeed id).

We can then do similar for designer and then look at code to run this query in VBA on button press and insert out allocation record with the two employee ids found?

Night night!

  silverous 22:58 21 Nov 2007

Oh, and the join in that query is important. It has to be an "outer" join otherwise when there's no allocations we won't get a result, so you double click on the link between Employee and Allocation and choose options as follows:

click here

I've got a dummy database with this aspect working if you want it emailed. It doesn't do much except find the least occupied programmer right now!

  coolteentom 14:17 03 Dec 2007

Silverous, thanks so much, works great! Am just wondering now what I need to do with the code from the command button, for the top field in the list to be allocated to the relevant employee?



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

The secrets of creating gory VFX

How to update iOS on iPhone or iPad

WhatsApp : comment lire vos messages sans que l’expéditeur le sache