Calculating the information for a text field on a form from a query (1 Viewer)

[guinness]

Even thinking of the title for this question was difficult for me. When it comes to code I'm a rank amateur. Just ask vbaInet

Here's what I would like to do:

I have a data entry form [Resources] and I would like to display some information about holidays in the form footer. Once the user has picked a combination of Trainer_Name and Start_Date I would like the 'On Change' or 'On lost Focus' event (not sure which would be the best) to perform a datediff calculation.

The datediff calculation would compare the difference between the start date entered on the form and the most recent past Start_Date on a query called [Hours Holiday_P1].

If someone could help me do this then I could adapt the same code to also look for the difference between the End_Date on the form and the next Start_Date on the Query.

The idea is that when resourcing trainers I know how long it is since and how long it is till their next holiday.

I've included a couple of images that might help

The sql for the query is

SELECT Resourcing.Start_Date, Resourcing.Trainer_Name, Resourcing.Duration, Time.Hours, [Hours]/7.4 AS Days
FROM (Resourcing INNER JOIN Employees ON Resourcing.Trainer_Name = Employees.Trainer_Name) INNER JOIN [Time] ON Resourcing.Duration = Time.Time
WHERE (((Resourcing.Start_Date) Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3,31)) AND ((Resourcing.Activity) Like "Holiday*"))
ORDER BY Resourcing.Start_Date;

As always any help is really appreciated.

Thanks Guinnes

 

[guinness]

Oops should have removed that Where condition!!

WHERE (((Resourcing.Start_Date) Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3,31)) AND ((Resourcing.Activity) Like "Holiday*")

 

[vbaInet]

When it comes to code I'm a rank amateur. Just ask vbaInet

And here he is again

Is the query doing what you want it to do?

 

[guinness]

Hi vbaInet

I hoped you would see this one.

Aside from the where condition the query simply shows each individual date that has the activity 'Holiday'. I just need to be able to calculate the datediff between my start date and the last holiday start date.

So the form will let you know that trainer x hasn't had a holiday for 60 days and that should be a warning that it's not ok to book a course lasting another 20 days.

Hope that makes sense.

You'll be glad, if not a little exasperated) to know that my forms with conditional formatting now run up and down with dates on the left and trainer names across the top.

I do learn. It just takes me a while

 

[vbaInet]

I suppose you decide whether you want the WHERE condition there or not. I can see that it returns only dates within the current financial year.

Let's take Derek Lockhart as an example, you want to calculate the difference between the Start Date entered and 18/04/2014?

Good to hear some progress

 

[guinness]

Hi vbaInet

The where condition was indeed a financial year condition. I used it to work out remaining holidays as our Holiday year still runs April to March although our financial year is January to December.

I will still use that function but will use it from a seperate query as people using the database would be scheduling hours up to December 2015 in September 2014 so the function is no longer relevant.

So I would want code that would:
Take the start date entered on the form
Check the most recent date prior to that start date on the query
Show the number of days elapsed since that person had a holiday

I would then adapt this to do the same between the End Date and the next booked holiday (presuming a holiday had been booked).

The combination of days since last holiday, days being resourced and days till next holiday would tell you whether it was a good idea to book that resource.

The idea is to prevent managers scheduling every available hour of the day

 

[vbaInet]

All I want to know is, based on the records I'm seeing in your screenshot...

Let's take Derek Lockhart as an example, you want to calculate the difference between the Start Date entered and 18/04/2014?

 

[guinness]

Yes thanks vbaInet

Based on the screenshot that's correct. I've attached a screenshot that shows only Derek. Based on it if I was trying to schedule Derek for 17/8/2014 it would tell me that it was two days since his last holiday on 15/08/2014 and however many till his next on 01/01/2015.

 

[vbaInet]

Looks like you've added another variable to this story.

I don't see how 15/08/2014 and 01/01/2015 relates to anything in your screenshot. I would have expected 01/01/2015 to be actually 02/01/2015 (since that's the most recent date for Derek) and I don't know how you worked out 15/08/2014.

 

[guinness]

Hi again

The scenario is that someone needs Derek to deliver a course on the 17/08/2014. The course may be a six week course or there may even be three or four courses taking place between August and December. So the Start_Date that they enter on the form [Resourcing] is 17/08/2014. At this point I want an on lost focus event to trigger that says that Dereks most recent holiday (based on the Start_Date field 17/08/2014) was on the 15/08/2014. On entering an End_Date on the form I want it to let the user know that his next Holiday is booked for 01/01/2015.

The purpose is to allow the person doing the resourcing to see that, Although Derek is available between the 15/08 and 01/01 that would be quite a long period to go with no holidays. We have found ourselves in a situation where someone has been booked to deliver for nearly four months straight without any holidays.

It should be pointed out that we don't book all our holidays at the start of the year. We are pretty flexible with our dates. Our only rule is that you can't book holidays when you're due to deliver. August to December however is quite a long time to go without a day off. So I want the person doing the scheduling to be aware that if they fill all the white space between August and December then the guy has no opportunity to get a holiday.

I hope that makes sense. So the code isn't looking for the most recent holiday on the list. It's looking for the most recent holiday based on the start date in the form.

 

[vbaInet]

This was all I needed, makes sense.

The scenario is that someone needs Derek to deliver a course on the 17/08/2014. The course may be a six week course or there may even be three or four courses taking place between August and December. So the Start_Date that they enter on the form [Resourcing] is 17/08/2014. At this point I want an on lost focus event to trigger that says that Dereks most recent holiday (based on the Start_Date field 17/08/2014) was on the 15/08/2014. On entering an End_Date on the form I want it to let the user know that his next Holiday is booked for 01/01/2015.

So have you made an attempt on this? Ignore the event trigger side of things for now, getting the required values is what you should be after.

 

[guinness]

I haven't made a start yet and wont be able to until either the wife goes to bed tonight or when I get to work in the AM. It's that old question of whether I value the dbase or my marriage

You know my talent for code cannot be measured (because you can't measure nothing) but I'm thinking Me.[Start_Date] is going to form part of an If statement. I'm also thinking I need some sort of Dim but because I'm so dim I'm not sure what that will be. I am also not sure whether it will be a sub or a function.

I know that you are trying to encourage me to learn and I actually appreciate it (I even spoke to my boss about getting some sort of foundation course in vba) but please don't fall off your seat laughing at whatever I post next.

Guinness

 

[vbaInet]

Your db of course, that's a no brainer

A function returns a value a sub doesn't you decide what you need.

Take your time, do some research and I'm sure you will come up with something at least. To keep things simple, use a DLookup() function and perform on a query sorted by name and followed by the date.

By the way, don't you have an Employee ID? You keep using their name.

 

[guinness]

Thanks vbaInet

I do have an employee number and also a unique (primary key) ID for each employee. Thing is anyone searching for anything to do with the employee will do so by name. That's why I took the unusual step with this database of not bothering to separate forename and surname like I have in the past.

I'm going to do a bit of research on how to write a function using dlookup in vba for access 2010 and see what comes up. I know that I'll get somewhere but please don't despair if I come looking for help.

I hate having to be so reliant on help from other people as I'm usually pretty good with software but access has always baffled me. I created something much more difficult than what I am creating now using Lotus Approach in the late 90s with no experience and without half the problems access is causing me nearly 18 years later.

Why am I writing expressions, learning vba, using macros and writing sql? Where's the consistency, the user interface, the integration. No wonder people feel clever when they can work with access. It's the forgotten member of the office suite. Every other part of the office suite has been simplified and improved till it's almost intuitive. Not access. I've even heard that 2013 sees the loss of graphs and pivots.

Rant over. Got some research to do to try and impress you with what I can find

 

[vbaInet]

I do have an employee number and also a unique (primary key) ID for each employee.

What's the difference? Or did you mean "and it's also"?

I'm going to do a bit of research on how to write a function using dlookup in vba for access 2010 and see what comes up. I know that I'll get somewhere but please don't despair if I come looking for help.

Here's a lifeline:
http://www.techonthenet.com/access/functions/domain/dlookup.php

Why am I writing expressions, learning vba, using macros and writing sql? Where's the consistency, the user interface, the integration. No wonder people feel clever when they can work with access. It's the forgotten member of the office suite. Every other part of the office suite has been simplified and improved till it's almost intuitive. Not access. I've even heard that 2013 sees the loss of graphs and pivots.

Access is a "rich development" application in its own right. It's Microsoft's fault for bundling it with Office in the first place because it was meant for developers. How many non developers do you find developing in Visual Studio? And you'll notice that newer versions of Office no longer come bundled with Access.

 

[guinness]

Last post till tomorrow

Unique ID is generated by access and exists nowhere else. The unique employee ID is generated by our company (I could have made this the primary key but didn't).

I'll examine the link in the morning.

As for Access. I see so many complex excel files that fall flat on their backs trying to handle way too much data or perform too many functions. Worse still I see teams that use three four or five unrelated spreadsheets to deal with data that could be obtained and managed from a single table within Access. The ability to understand what databases are capable of and how they should be used is something every PC user would benefit from. The reason that they don't benefit is two fold: lack of awareness, and the fact that it's like learning three foreign languages at the same time. IMHO

 

[guinness]

Sorry

Got so busy with my rant that I forgot to say thank you

Thank you

 

[vbaInet]

Hmmm... yes you could have used that as your primary key. Well I suppose the Autonumber field could be put to other use at some point. And yes you should be using the Employee ID in all of your searches, not their name because names are not unique.

Let us know how you get on.

 

[guinness]

Help

Ok so I thought I would try to just return the date of the last holidaytaken prior to the start date entered on my form before I started calculating date differences etc.

I added an unbound text box to my table called Last_Hol

I tried creating a module with this code

Function LastHol()
Dim startdate As Date
Dim Name As String
startdate = Me.Start_Date
Name = Me.Trainer_Name
Last_Hol=Dlookup(("[Start_Date]", "Hours Holiday_P1",  [Trainer_Name]=Name AND [Start_Date]<Startdate)
End Function

In my head this is what I wrote

Fill in the Last_Hol textbox with a date by doing the following:

Look up the Start_Date field of the Hours Holiday Query where the Trainer_Name field on the query was equal to the Trainer_Name field on the form and the Start_Date on the Query was less than the Start_Date on the form.

I can't even test my stupidity as I have a compile error with the commam between Start Date and Hours Holiday_P1

Help!!!!

 

[vbaInet]

For starters the syntax isn't right. Did you look at all the examples given in the link for appropriate syntax?