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

 

[RickSpr]

Getting the classification and supervisor information to fill in when you enter the Employee ID is easy. All you have to do is join the Employees, Classification List, and Supervisors tables to the form's recordset (based on Scheduled Vacation). Access will automatically fill in the fields--it's called AutoLookup. It happens when your recordsource has a one-to-many relationship, and you add or change the child table's foreign key field. The relationship from Schedule Vacation to Employees is one-to-many, and I'm assuming Employee ID is a foreign key, so as soon as you enter it, the Employees table lookup occurs. That fills in the Employees.Classification ID and Employees.Supervisor ID fields, which will cause a cascading lookup in those tables. Note: I'm not sure, but you may have to have controls on the form for Employees.Classification ID and Employees.Supervisor ID for the Autolookup to happen. If so, you can make them invisible. But try it without them first.

To make the message come up, you'll need to use AfterUpdate events on the Employee ID, Date, and Total Hours Scheduled controls. The event procedures all need to do the same thing, so I would have them call a common Sub procedure.

The Sub procedure (let's call it CheckVacation) first needs to check whether all three fields have been entered, since the user could enter them in any order. If any of the three controls is Null, the sub should return without doing anything.

If all controls have values, the Sub procedure needs to get the total amount of vacation. Your description of that referred to people, not hours, so I'm not sure exactly what the query would be, but you can do it with a DSum() on the Scheduled Vacation table with criteria matching Classification ID and Supervisor ID. Once it has the total, you also need to get the total people or hours for that classification and/or supervisor with another DSum(), so you can determine the percentage. Then you can check the current date with the Date() function and decide whether to use 10% or 25%. Finally, the Sub calculates whether you're over the limit and displays the message if so.

Some comments:
1. Your Scheduled Vacation table isn't normalized. The Classification ID and Supervisor ID are implied by the Employee ID, so they really need not be stored here.
2. I assume that the form can also be used to edit existing scheduled vacations. Note that whenever a classification/supervisor is over the limit, every time any vacation time in that cls/sup is edited the message box will reappear. This repeated message box could be annoying. You might want to have the message box appear only when a record is being added, by testing the Me.NewRecord property. If the Sub procedure is running when Me.NewRecord is True, it would show the message box, otherwise it might make visible a normally invisible message label with perhaps red text or background. That would avoid interrupting the user doing the editing when the problem was preexisting.

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

 

[TheAceMan1]

How are ya PlumDingo . . . . .

[blue]RickSpr[/blue] is on target here. [blue]Normalization[/blue] and [blue]Relationships[/blue] are the heart of any database and determine the ease of design you can aquire.

Have a look at the following:

[blue]Normalizing Tables[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;209534&Product=acc2000

T[blue]able Relationships[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467&Product=acc2000

See Ya! . . . . . .

 

[PlumDingo]

Okay Rick,

This helps. I took your advice and normalized my table, thus getting rid of the Classification ID and Supervisor ID field in the table. After some thought, I realized that I don;t really need that information. It is a clerk that will be entering it and the person who is approving the vacation is primarily concerned with the amount of each classification that he/she has off.

On to your suggestion as to how to get the message box to pop up and the detail with the label. I am not sure as to how exactly I would go about doing that. I am not very familiar with VBA for Access. Let me show you what I have so far.

Private Sub CheckVacation()

If Me!Date <> Null Then
If Me![Empl id] <> Null Then
If Me![total hours scheduled] <> Null Then
End If
End If
End If

End Sub

I am trying to write the query to show the summary data but I get lost. Any pointers?

 

[PlumDingo]

Okay, I am able to write a query that basically returns a table sorted by date with a count of how many people are off for each date and in which classifications. It has three columns:
Date
Count of Classification ID
Job Classification.

I have no idea what to do with that query so that my form can use it. Writing the code is killing me.

Thanks!

 

[RickSpr]

Oops! You should never use "=" or "<>" with Null, or with a field or variable that might be Null. When you compare something with Null, the result is always Null, not True or False. You have to use the IsNull() function instead.

You didn't explain whether the threshold is based on the number of vacation hours or the number of people on vacation, nor how to tell whether a date is during the storm season, so you'll have to adapt the following code. This is written assuming the threshold is based on hours, and the storm season is June through September.

Private Sub Date_AfterUpdate()
    CheckVacation
End Sub

Private Sub Empl_ID_AfterUpdate()
    CheckVacation
End Sub

Private Sub total_hours_scheduled_AfterUpdate()
    CheckVacation
End Sub

Private Sub Form_Current()
' Show or hide the excess vacation message (Label control) when user
' moves to a new record
    CheckVacation
End Sub

Private Sub CheckVacation()
    Const StormSeasonStart = 6 'June
    Const StormSeasonEnd = 9 'September
    Dim sngVacHours As Single, sngTotalHours As Single
    Dim sngPctOnVacation As Single
    Dim intMonth As Integer, blnExcess As Boolean

    If IsNull(Me!Date) _
    Or IsNull(Me![Empl id]) _
    Or IsNull(Me![total hours scheduled]) Then _
        Exit Sub ' not all data entered yet

    ' Get total amount of vacation hours
    sngVacHours = DSum("[total hours scheduled]", _
        "[Scheduled Vacation] INNER JOIN Employees " _
        & "ON [Scheduled Vacation].[Empl id] = Employees.[Empl id]", _
        "[Classification id] = " & Me.[Classification ID] _
        & " AND [Supervisor id] = " & Me.[Supervisor id])

    ' Get the total number of hours
    sngTotalHours = ??? (I don't know how to calculate this)

    ' Calculate the percentage of vacation usage
    sngPctOnVacation = sngVacHours / sngTotalHours * 100.0

    ' Depending on whether it's storm season, check for excess vacation
    intMonth = DatePart("m", Me!Date)
    If intMonth >= StormSeasonStart And intMonth <= StormSeasonEnd Then
        If sngPctOnVacation >= 10.0 Then blnExcess = True
    Else
        If sngPctOnVacation >= 25.0 Then blnExcess = True
    End If

    ' If there is excess vacation, unhide a message on the form and
    ' (for a new record only) display a message box
    If blnExcess Then
        Me!lblExcessVacation.Visible = True
        If Me.NewRecord Then
            MsgBox "Too much vacation scheduled for this " _
                & "classification and supervisor", vbExclamation
        End If
    ' Otherwise hide the message on the form
    Else
        Me!lblExcessVacation.Visible = False
    End If
End Sub

I made a slight change to this, compared to what I suggested earlier. This code will make the warning Label control visible whenever the user is on a record for a classification and supervisor who is "over budget" for vacation time. Earlier I suggested making it visible only after one of the fields was edited. The problem with that is that, if the user fills in, say, the total scheduled hours and then immediately moves to another record (which might happen by tabbing out of the field), the label would be made visible after the update and then immediately be made invisible again as a result of moving to the next record. With the code this way, the message will be visible whenever it's appropriate. (The message box will only be displayed when a new record is created which results in being over budget.) There is a downside, unfortunately: CheckVacation does one or two domain aggregate functions (DSum is one), which can cause a noticeable delay. If the delay is troublesome, we'll have to think of something else, or accept that the user may not get a warning when editing an existing record.

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

 

[RickSpr]

Well, I was writing the above when you added the post about your query, so excuse me if you got confused.

Now I can see that my assumption about the threshold being based on hours was wrong. Also that the supervisor doesn't figure into it, just the classification.

If you give me the SQL of your query, I'll revise the code above. You may as well explain how to tell whether the vacation is within storm season as well.

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, most of this makes sense. I would rather however set a constant for each classification and calculate the percentage off that way. That seems much easier. So I would have 8 constants for each job class and that would have the total number of people budgeted to an area. Problem is that since people bid around all the time, the numbers could change biweekly. So maybe there can be something that actually counts the number of employees in the database of each classification to supply that constant used to calculate the percentage. I don't need to know for the purpose of the error message about the hours they have scheduled just how many people for the day. Can't I use the query that I built and use that to tell how many people are off and then use my constant to calculate the percentage?

Also can you please explain what this section of code does? I understand what the join gives me but I think that I am missing something else.

sngVacHours = DSum("[total hours scheduled]", _
"[Scheduled Vacation] INNER JOIN Employees " _
& "ON [Scheduled Vacation].[Empl id] = Employees.[Empl id]", _
"[Classification id] = " & Me.[Classification ID] _
& " AND [Supervisor id] = " & Me.[Supervisor id])

Thanks for all your help,

France

 

[PlumDingo]

Okay, we keep writing at the same time. Storm or hurricane season in Florida for my company is from June 1 until November 1. The query SQL is as follows:

SELECT [Scheduled Vacation].Date, Count([Classification List].[Job Classification]) AS [CountOfJob Classification], [Classification List].[Job Classification]
FROM [Classification List] INNER JOIN ((Supervisors INNER JOIN Employees ON Supervisors.SupervisorID = Employees.SupervisorID) INNER JOIN [Scheduled Vacation] ON Employees.[Employee Number] = [Scheduled Vacation].[Empl ID]) ON [Classification List].[Classification ID] = Employees.[Classification ID]
GROUP BY [Scheduled Vacation].Date, [Classification List].[Job Classification]
ORDER BY [Scheduled Vacation].Date;

with the three columns that I mentioned before.

Thanks,

France

 

[RickSpr]

You could use your query, but it wouldn't be as efficient. The reason is that your query reads all the rows and sorts them, then groups and counts them. All you need to do for the form is to read the rows for one classification ID--which is probably indexed, so other rows wouldn't have to be read--and count how many rows are found. You don't really need a sort for this form. A DCount() function call will do it most efficiently.

In the revised code below, I didn't use your query for this reason. If you really want to use it, you should at least add [Classification ID] to it, so you can use that to look up the specific row you need. Otherwise you'll have to match on [Job Classification], which probably isn't indexed and would therefore cause all rows to be read.

The revised code also determines the actual number of people in the classification, as you mentioned.

In revising the code, I realized a flaw in my logic. I set it up to check for overage in the controls' AfterUpdate events, but it checks by scanning the [Scheduled Vacation] table. In the controls' AfterUpdate events, the table hasn't been updated yet, so this was incorrect. The revised code takes into account the change just made by the user and not yet saved.

The revised code:

Private Sub Date_AfterUpdate()
    CheckVacation
End Sub

Private Sub Empl_ID_AfterUpdate()
    CheckVacation
End Sub

Private Sub total_hours_scheduled_AfterUpdate()
    CheckVacation
End Sub

Private Sub Form_Current()
' Show or hide the excess vacation message (Label control) when user
' moves to a new record
    CheckVacation
End Sub

Private Sub CheckVacation()
    Const StormSeasonStart = 6 ' June
    Const StormSeasonEnd = 10 ' October
    Dim intNumPeopleOff As Integer
    Dim intNumPeople As Integer
    Dim intMonth As Integer
    Dim sngBudgeted As Single
    Dim sngPctPeopleOff As Single

    If IsNull(Me!Date) _
    Or IsNull(Me![Employee Number]) _
    Or IsNull(Me![total hours scheduled]) Then _
        Exit Sub ' not all data entered yet

    ' Get number of people of that job class off that day already
    intNumPeopleOff = DCount("*", "[Scheduled Vacation"], _
        "[Classification ID]=" & Me.[Classification ID])
    ' If this record is to be added, there will be 1 more
    If Me.NewRecord Then intNumPeopleOff = intNumPeopleOff + 1

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

    ' Calculate the percentage of people off
    sngPctPeopleOff = intNumPeopleOff / intNumPeople * 100.0

    ' Determine the budgeted percent off for the vacation date
    intMonth = DatePart("m", Me!Date)
    If intMonth >= StormSeasonStart And intMonth <= StormSeasonEnd Then
        sngBudgeted = 10.0
    Else
        sngBudgeted = 25.0
    End If

    ' If there is excess vacation, unhide a message on the form and
    ' (for a new record only) display a message box
    If sngPctPeopleOff >= sngBudgeted Then
        Me!lblExcessVacation.Visible = True
        If Me.NewRecord Then
            MsgBox "Too much vacation scheduled for this " _
                & "classification and supervisor", vbExclamation
        End If
    ' Otherwise hide the message on the form
    Else
        Me!lblExcessVacation.Visible = False
    End If
End Sub

The comments in my earlier post regarding showing/hiding the Label control still apply.

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 am almost there. I actually understood everything you wrote. However, I am getting a syntax error with this section of code.

intNumPeopleOff = DCount("*", "[Scheduled Vacation"], _
"[Classification ID]=" & Me.[Classification ID])

The error is:
Microsoft cannot find the '|' field reffered to in your expression. Do you know what that means?

 

[RickSpr]

My mistake. The quote and bracket near the end of the first line should be reversed.

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 thought of that already and I still get the same error. The line looks like this:

intNumPeopleOff = DCount("*", "[Scheduled Vacation]", _
"[Classification ID]=" & Me.[Classification ID])

Is there something that I did wrong?

 

[PlumDingo]

I just realized what the problem is. I had taken your earlier suggestion about normalizing my table and got rid of the field Classification ID from this table and thus the form. I have to search for this field another way.

 

[RickSpr]

You should still have Classification ID available in the form's recordsource, since you join EmployeeID. You can create a hidden text box on the form and bind it to that field.

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,
I created a text box named txtClassificationID and in the properties window I put the control source to be EmplID. Since that did not work, I tried to bind it to the Classification ID field in the Employee table and that doesn't work either. I can't think straight anymore. What am I doing wrong?

 

[RickSpr]

I confused you when I said "EmployeeID", but I meant "Employees", the table that you join.

Set txtClassificationID's Control Source to [Classification ID]. In the code, modify Me.[Classification ID] to Me.[txtClassificationID].

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 for your help Rick. This works. Now all I have to do it tweak it. I was having problems because I forgot to do the join operation for the form's record set. You were a great help!

 

[PlumDingo]

Rick,
One quick thing, I think that I broke it. I keep getting runtime error 2001. You cancelled the previous operation. I don't know what I did. It was working fine or so I thought. The code line looks like this:

intNumPeopleOff = DCount("*", "[Scheduled Vacation]", _
"[Classification ID]=" & Me.txtClassificationID)

Thanks

 

[RickSpr]

Sometimes Access or Jet detects an error deep in its own code and cancels the operation instead of raising a run-time error. Then the higher levels of code misinterpret that as YOU having canceled the operation, so they give you this error message instead of one that makes sense. This is really frustrating, because it gives you no information about what the real error is.

My best guess is that I should have used the "!" operator instead of a period. Try changing Me[red].[/red]txtClassificationID to Me[red]![/red]txtClassificationID.

Other than that, do you know what you might have done that would have broken it? I don't mean specifically, but were you changing a table design, or working on your form, or what?

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