Populate a form field after selecting another field 1

[PlumDingo]

I have a table called Scheduled Vacation that basically tries to keep track of the vacation that an employee requests. I have a form that I use to enter the data. The form has the following fields:

Sch Ind which is an autogenerated number for the primary key
Empl ID
Date
Total Hours Scheduled
Classification

The table Scheduled Vacation has the same fields plus one more:

Sch Ind which is an autogenerated number for the primary key
Empl ID
Date
Total Hours Scheduled
Classification
Supervisor ID

The idea is to be able to tell who is on vacation for any given time frame whether partial day or whole day within what classification, and for which supervisor.

The idea is that I should be able to enter the employees Empl ID # and the Classification field and Supervisor ID should automatically update with the Name of their classification and the Name of the Supervisor respectively. I have another table called Employees that has the following fields:

Employee ID
First Name
Last Name
Classification ID(which is the ID number associated with that person's Classification)
Seniority Date
Supervisor ID

I have yet another table that lists the actual text name associated with the Classification ID called Classification List and it has two fields:

Classification ID
Job Classification

I have yet another table that lists the actual text name associated with the Supervisor ID called Supervisors and it has three fields:

Supervisor ID
LastName
Title

Also it would be great if (I don't know if this is possible on this form) after the person enters the empl id and the classification is updated and the date is entered a message could come up if there are more than the allowable limits of people in that classification off that same day. 10% during storm season and 25% all other times.

I hope that this makes sense and I hope that someone can help me.

Thanks

 

[PlumDingo]

I have tried everything I can think of. The funny thing is that the next section on code:

' Get the total number of people of that job class
intNumPeople = DCount("*", "Employees", "[Classification ID]=" _
& Me.[txtClassificationID])

works just fine. I was working on my form cleaning it up a little but for the life of me I cannot figure out what I did differently.

 

[RickSpr]

Oh wait! Classification ID is no longer in the Scheduled Vacation table, right? We need to modify the first DCount() to search a join between Scheduled Vacation and Employees. Change it to this:

intNumPeopleOff = DCount("*", _
    "[Scheduled Vacation] INNER JOIN [Employees] " _
    "ON [Scheduled Vacation].[EmployeeID] = [Employees].[EmployeeID]",
    "[Classification ID]=" & Me.txtClassificationID)

By the way, the domain aggregate functions (DLookup, DCount, DSum, DAvg, etc.) all execute a SQL statement and return the results. The SQL statement is built from their parameters as follows:
SELECT xxx FROM param2 WHERE param3
"xxx" depends on the function and first parameter:
DLookup: xxx = param1
DCount: xxx = Count(param1)
DSum: xxx = Sum(param1)
etc.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PlumDingo]

I had tried that already. I had created a query called Vacation Count because I could not figure out how to write it in the VBA language and I called Vacation Count in the function. My revised code looks like this:

' Get number of people of that job class off that day already
intNumPeopleOff = DCount("*", "[Vacation Count]", "[Classification ID]=" & Me.txtClassificationID)

My SQL for the query is:

SELECT [Scheduled Vacation].[Empl ID], Employees.[Employee Number]
FROM Employees INNER JOIN [Scheduled Vacation] ON Employees.[Employee Number]=[Scheduled Vacation].[Empl ID];

I still get the same error.

By the way, thanks for the information on the D functions, it helps to clear up some last lingering questions I had.

 

[RickSpr]

At this point I don't know what could be wrong. You're getting the error on that line in your last post?

What I would do at this point is to make the txtClassificationID control visible, put a breakpoint on that line, and run the code. At the breakpoint, first check that txtClassificationID has the expected value. Next try opening your Vacation Count query. If that succeeds, try executing the DCount function in the Immediate Window:
debug.print DCount("*", "[Vacation Count]", "[Classification ID]=[red]xxx[/red]"
(xxx = value of txtClassificationID)
This may help narrow down on what part of the statement is causing the problem.

I've been assuming that Classification ID is a number. That's right, isn't it?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PlumDingo]

Okay Rixk, it works. I had forgotten to put Classification ID in my query so that it would show up in the result and that is where it all went wrong. It helped to debug the code like that. Thanks so much. I think that I should be able to tweak it now. I hope that if it goes wrong again, that I can call on you excellent assistance. This whole post derserves mucho stars!!!

 

[PlumDingo]

One more question. Now that I am teaking the code a little bit I realized that the count of people on vacation is not dependent on the date. I am trying to add that dependency but it is not working. I modified the infamous line to now read:

intNumPeopleOff = DCount("*", "[Vacation Count]", "[Classification ID]=" & Me.txtClassificationID And "[Date] = '" & Me.txtDate & "'")

I even tried it like this:

intNumPeopleOff = DCount("*", "[Vacation Count]", "[Classification ID]=" & Me.txtClassificationID And [Date] = & Me.txtDate)

and I get type mismatch error. I know that this has something to do with the date variable.

Also, I undated my Vacation Count query to the following:

SELECT [Scheduled Vacation].[Empl ID], Employees.[Employee Number], Employees.[Classification ID], [Scheduled Vacation].Date
FROM Employees INNER JOIN [Scheduled Vacation] ON Employees.[Employee Number] = [Scheduled Vacation].[Empl ID];

Can you help figure out how to accommodate the fact that I need to search by date as well. Any help that you can provide.

Thanks

FJ

 

[RickSpr]

Date values in Access need to be preceded and followed by "#" characters. You were almost right the first time, when you used apostrophes. You also needed to get the "And" into the string.

intNumPeopleOff = DCount("*", "[Vacation Count]", "[Classification ID]=" & Me.txtClassificationID [red]& " And[/red] [Date] = [red]#[/red]" & Me.txtDate & "[red]#[/red]")

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PlumDingo]

THank you so much. My husband and I tried that last night but it kept saying expecting expresiion. This is what we tried:

intNumPeopleOff = DCount("*", "[Vacation Count]", "[Classification ID]=" & Me.txtClassificationID And [Date] = #" & Me.txtDate & "#")

We did not realize that we needed the & and the quotes around the AND. We thought that the & concatenated everything together which we did not want, but I guess that is exactly what we did want.

It works perfectly now in terms of counting and displaying the error message!! Now on to other parts on this form. Hopefully, this novice can figure everything else on this form out.

Thanks once again,

FJ

 

[RickSpr]

You're welcome.

The "And" thing is a common sort of mistake when writing code to build an SQL statement or other expression. It often helps to work backwards. Start with an example of what you want the final string to look like:
[Classification ID] = 123 AND [Date] = #9/9/2004#
Then put quotes around the whole thing (doubling up any embedded quotes, though that doesn't apply here):
[red]"[/red][Classification ID] = 123 AND [Date] = #9/9/2004#[red]"[/red]
Then replace the variable parts:
"[Classification ID] = [red]" & something & "[/red] AND [Date] = #[red]" & something & "[/red]#"
Then you can just replace the 'somethings' with ordinary VBA expressions--references to your text boxes, in this case.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PlumDingo]

Rick,

I don;t know if you are still monitoring this but I just had a thought about this oart of my program. Is there a way that I can also verify that the person they are scheduling has enough vacation time, i.e. they are not overschuduling, and not allow them to schedule if their remaining vacation balance is zero. I have a table Total Vacation Allowed and a query named Vacation Remaining that I created that lists the amount of vacation remaining for each employee that currently has vacation scheduled. I was thinking that I could have every employee listed in that table even if they have no vacation scheduled with a default value of zero so that I can search off of that table. The fields are as follows:

Total Vacation Allowed:
Employee Number primary key
Seniority Date
Hours Earned
Carry Over Hours

Vacation Remaining
SELECT [Total Vacation Allowed].[Employee Number], Sum([Scheduled Vacation].[Total Hours Scheduled]) AS [SumOfTotal Hours Scheduled], [Hours Earned]+[Carry Over Hours]-DSum("[Total Hours Scheduled]","[Scheduled Vacation]","[Empl ID]=" & [Employee Number]) AS [Vacation Remaining]
FROM [Total Vacation Allowed] INNER JOIN [Scheduled Vacation] ON [Total Vacation Allowed].[Employee Number] = [Scheduled Vacation].[Empl ID]
GROUP BY [Total Vacation Allowed].[Employee Number], [Hours Earned]+[Carry Over Hours]-DSum("[Total Hours Scheduled]","[Scheduled Vacation]","[Empl ID]=" & [Employee Number]);

Employee Number
Sumof Total hours Scheduled
Vacation Remaining.

I think that I have all the components I need here but I am not sure where to start. I would not want the user to be able to schedule vacation for an employee unless another days vacation is deleted first. The error message will let them know that prior to closing the form and exiting. Thanks for letting me peick your brain.

France
Just trying to get by.