Calculating the information for a text field on a form from a query

[guinness]

Before I read your last post I managed to compile the database by changing my code to this:

Function LastHol()
Dim startdate As Date
Dim Name As String
startdate = Forms!Resourcing!Start_Date
Name = Forms!Resourcing!Trainer_Name
Forms!Resourcing!Last_Hol = DLookup("[Start_Date]", "Hours Holiday_P1", [Trainer_Name] = Name And [Start_Date] < startdate)
End Function

I then tried to call the function using Start_Date after update event but got the debugger highlighting

startdate = Forms!Resourcing!Start_Date

The example I used was the LDate example as it seemed to more closely match what I was trying to do. Can you be more specific about the syntax? Am I even on the right lines?

 

[vbaInet]

= DLookup("[Start_Date]", "Hours Holiday_P1", [Trainer_Name] = Name And [Start_Date] < startdate)
End Function[/code]

The part I'm referring to is in red. If you look at the examples in the link provided, there's an example that describes which parts to put in quotes and which shouldn't.

 

[RainLover]

Post 19 and 21 Use Name as field name. This is reserved and should not be used.

 

[guinness]

Does this look any better?

Forms!Resourcing!Last_Hol = DLookup("[Start_Date]", "Hours Holiday_P1", "'[Trainer_Name]'" = Name And "[Start_Date]" < startdate)

The debugger still kicks in even before it gets to that line of the code.

The debgger says invalid use of null and when I debug highlights the line I mentioned above

 

[guinness]

Ok thanks for the tip about name. I've replaced each incidence of the with tName so the code now looks like:

Function LastHol()
Dim startdate As Date
Dim tName As String
startdate = Forms!Resourcing!Start_Date
tName = Forms!Resourcing!Trainer_Name
Forms!Resourcing!Last_Hol = DLookup("[Start_Date]", "Hours Holiday_P1", "'[Trainer_Name]'" = tName And "[Start_Date]" < startdate)
End Function

but it's obviously still wrong

 

[vbaInet]

Before you begin to assign anything to fields, first use a MsgBox to get it working.
i.e. MsgBox DLookup(... etc ...)

Test one of the following with a MsgBox():

Msgbox DLookup("[Start_Date]", "Hours Holiday_P1", "[Trainer_Name] = Forms!Resourcing!Trainer_Name And [Start_Date] < Forms!Resourcing!Start_Date")

Msgbox DLookup("[Start_Date]", "Hours Holiday_P1", "[Trainer_Name] = '" & Me.Trainer_Name & "' And [Start_Date] < #" & Me.Start_Date & "#")

... pay attention to the quotes and hash characters used in the second example. The first one doesn't need it because it's inside the function. And I see that you're still using the Trainer Name instead of the Trainer ID.

 

[guinness]

Ok you're forgetting who it is you're dealing with here. Am I creating a new function called MsgBox() or am I pasting the code into the function I've already posted?

Can you paste the full code that you want me to test please?

 

[vbaInet]

You are replacing this line:

Forms!Resourcing!Last_Hol = DLookup(...

 

[guinness]

Not working

With the first I get invalid use of Null and again it highlights the line above the code that you sent

tName = Forms!Resourcing!Trainer_Name

With the second I get compile error, Invalid use of the Me Keyword and it highlights the Me at Trainer_Name in red below

Msgbox DLookup("[Start_Date]", "Hours Holiday_P1", "[Trainer_Name] = '" & [COLOR=red]Me[/COLOR].Trainer_Name & "' And [Start_Date] < #" & Me.Start_Date & "#")

:banghead:

 

[vbaInet]

Not working

With the first I get invalid use of Null and again it highlights the line above the code that you sent

What does that tell you?

 

[guinness]

I have absolutely no idea and even less idea why when I use the second code it doesn't just stop at the same error.

If it's working through each line of code sequentially then it shouldn't be able to get past it and highLight Me on the next line
:banghead:

 

[vbaInet]

There's no value in the Trainer Name field/textbox for the current record.

 

[guinness]

Doh!

That does raise another issue though. Sometimes there will be no value in the trainer box as it's possible to schedule an event before you know who will deliver it. Guess I'm need and If and elseIf statement now as well.

Hmmmm

 

[guinness]

Ok so here's where I'm at...

I re-wrote my code:

Function LastHol()
Dim startdate As Date
startdate = Forms!Resourcing!Start_Date
If Forms!Resourcing!Trainer_Name = Null Then
Exit Function
If Not IsNull(Forms!Resourcing!Trainer_Name) Then
MsgBox DLookup("[Start_Date]", "Hours Holiday_P1", "[Trainer_Name] = Forms!Resourcing!Trainer_Name And [Start_Date] < Forms!Resourcing!Start_Date")
End If
End If
End Function

No errors at all but it also doesn't do anything. I tried replacing the msgbox code with the second line that you supplied but it still says it's an invalid use of Me

 

[vbaInet]

Don't worry about the fact that it sometimes won't contain a value. We'll deal with that later. So remove all the code that does the checking for Null, you're trying to do two things at once. Enter the relevant data into the relevant textboxes and get the correct value returned from the DLookup() first.

 

[guinness]

Done and I do get a message box containing the date of the last holiday

 

[vbaInet]

Progress, yay!

Now although you're getting the correct result you need to ensure that the DLookup() is working on an ordered data set. So is Hours Holiday_P1 sorted in alphabetical order by the date?

 

[vbaInet]

I meant ascending order not alphabetical*

 

[guinness]

It is now

 

[vbaInet]

Now you can use the DateDiff() function on the value returned.

But before you do that, if I enter today's date, 06/08/2014, and Derek's last holiday is today, should it return today's date or the date before?