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

[guinness]

Thanks vbaInet

I've taken the employye ID which is the primary key from the employees table and put it in to the Hours Holiday_P1 query. Do you need me to take the module and place it on the form as well?

 

[vbaInet]

Yes you need to move it to the form. Not as well, but a cut and paste. There should only be one copy of that function and it should only be in the form.

And by the way it's called a function.

 

[guinness]

Ok that's the function in the form and the Payroll_Number field in the query. The query is sorted by Start_Date ascending. The function is presently called by cicking a button called Test_Button

 

[vbaInet]

Now the function is in the form, put another Msgbox before the "Msgbox DLookup" line to return the values of the start date and trainer name values.

 

[guinness]

Thanks vbaInet

I stop today for a week and wont have access to the database till next Saturday. You can be sure you'll know when I get back.

Cheers

Guinness

 

[RainLover]

The second of the two message boxes didn't work. It kept comming up invalid use of Me. I thought it was just an alternative to the first so wasn't worried.


I haven't used ID in either my form or my query so would then have to either add it to both or reference the employees table as well as both the form and query. By making trainer Name a full name I've avoided duplicates so far and If we do get two John Smiths I can just use a digit to separate them. I'm much more concerned as to why I can't get the most recent date based on the date entered in the form.

You should really take note of vbaInet. If you don't understand why he asks for something special then ask him and learn rather than ignore and fail.

 

[guinness]

I'm back. vbaInet I hope you had a peacefull time without me nagging away with my questions

Rain, I know far better than to ignore vbaInets advice.

Anyway I've created the second message box as requested and it accurately reflects the trainer name and start date that I enter. The Holiday button however still finds the first holiday in the list rather than the most recent one.

Any ideas?

 

[vbaInet]

Interesting! Upload a test db.

But before you do, did you format the Start Date and End Date textboxes?

 

[guinness]

Hello again vbaInet

I'll upload a sample dbase this morning. What formatting were you wanting for the text boxes?

 

[vbaInet]

Look through the options in the Format property and select one that suits.

 

[guinness]

I'm assuming that you mean make sure it's set to general date which both are. I'm going to post a copy of the db. Can you let me know when you get a copy as it contains information that I don't want to leave on the site but it would take forever to rebuild the db if I removed it

 

[guinness]

Db attached

 

[vbaInet]

The fields don't have a Time part to it do they?

 

[guinness]

dd/mm/yyyy

 

[guinness]

Can I safely remove the db now?

 

[vbaInet]

You may, but I'll look at it later.

 

[guinness]

Thank you

 

[vbaInet]

Give me some parameters and tell me what you expect.

 

[guinness]

Hi vbaInet

The idea was to identify the date of the last holiday taken using the start date and the date of the next holiday booked using the end date.

 

[vbaInet]

That's why I said give me some values to enter and tell me what date you expect. I don't want to waste time second guessing.