Sub-form to calculate based on selection of Combo Box

[OCG]

Hi,

I am designing a form for users to create project budgets. The relationships between all the tables is a little complex to describe so I have added a PDF copy of a report that shows the relationships.

For the forms, I have a main form for the user to enter the project start and end date, the project director, and the number of years. This is all tied to tblDate and the primary key, BudgetID is an auto number and not shown. The first subform is tied to BudgetID and it has most of the fields from tblEntry. It is setup as a datasheet. It has one other control called cboEmployeeName. (Note the name refers the employee's class name and not their given name which belongs back in tblEntry). The CategoryID is also tied to this selection. This combo box is extremely important because when the user selects the employee name, it determines how their benefits, salary, and inflationary factor will be determined. This is all background.

The problem I have is that, when the user selects a certain employee name, "GRA", (from cboEmployeeName) I need for the form to open up a sub-form to enter tuition remission. This by itself, probably isn't too hard. I imagine that I could code the combo box entry so that it either makes the form visible, or DoCmd.Open, after update. The problem that I forsee is that the users will inevitably change their minds about having a GRA on the project, and while it would be fairly simple to close the subform, I do forsee a problem about how to remove the data for tuition remission. (If it is tied to EntryID, and the EmployeeName is no longer GRA, there is a problem) I need something that will automatically remove the data from the subform for the specific GRA if the user decides to change the EmployeeName of the combo box. As you can see I don't have a tblTuitionRemission setup, because I'm just not quite sure what relationship it needs to have to the other tables. Any help that you can provide would be greatly appreciated.:banghead:

[OCG]

I guess that this is a bump post.

I'm grateful to all the people who looked at my question. If there is anything that I can do to get a response, please let me know.

Thanks.

[geekgirlau]

In general terms, you may be able to use something like the following:

unique identifier for every entry in tuition remission;
some means of recognising whether the tuition remission record is incomplete (might just be the fact that the user clicked on "Cancel");
Might need to try a combination of actions - firstly, attempt to cancel the record. Secondly, run a delete query to delete all tuition remissions where the ID field is "n", "n" being the ID of the current record.I don't really understand the business of the employee name being "GRA". At first glance, wouldn't you be able to have tuition remission for any employee? If so, I would be launching the tuition remission form on clicking a button rather than being dependant on selecting a specific name from the combo box. That way the link between the employee record and tuition remission is via the employee ID.

[OCG]

Thank you Geekgirlau for your response.
In answer to your question, tuition remission would only apply to the employee class GRA "Graduate Research Assistant". These are the only student employees, and thus the only ones eligible for tuition remission assistance.

I like your idea about checking the employee class with code, but I don't know if a cancel button is the best solution. The reason I say this is because, when the users change the employee class it will probably be after they have made an initial budget. Likely, they will just go back and change the combo box entry and expect that since they changed the entry, tuition remission will no longer be a consideration.

How about if I created a new field for tblEntry called tuition remission, but then I created some code for the combo box where after update it checked to see what the employee class was, and if it was 7 (for GRA), it would accept an entry for tuition remission, but if it was anything else it would set the field to null; would that be possible? I'm thinking of some way to use If Not IsNull().

[geekgirlau]

Without seeing your database, it sounds plausible. To set the field to null you just need to do "Me.MyField = Null".

[OCG]

Thanks again Geekgirlau,

I took your suggestion, but I made a slight alteration to my original concept. Instead of opening or coding the visual property of the sub-form, I created it as a new page on an existing tab control. I created a query, and used that as the record source for the sub-form "sfrmTuitionRemission". Then I used the following code:

Private Sub cboEmployeeClass_AfterUpdate()
'set variable for EmployeeID
Dim EmpID As Byte
'define EmpID as EmployeeID from tblEmployees
EmpID = DLookup("EmployeeID", "tblEmployees", "EntryID")
If EmpID <> 7 Then
Me.TuitionRemission = Null

End If
Forms!frmTabs.sfrmTuitionRemission.Form.Requery
End Sub

While this works reasonably well, it did create a new problem. As I mentioned, the record source for sfrmTuitionRemission is a query, and so when I make a change to the combo box, it isn't immediately reflected in sfrmTuitionRemission. I added a requery section (as you can see in the code), but apparently that isn't correct. Can you please tell me if I should use a different method, or does it belong in a different place?

[geekgirlau]

The requery should work in theory - what is the criteria for the query on which the sub form is based?

[OCG]

Thanks geekgirlau for your reply.

The criteria for the query that sfrmTuitionRemission is based on is EmployeeID =7 from tblEmployees. Unfortunately, the requery property isn't working, and I can't understand why. Is it possible that it has something to do with placing the subform on a tab page? I tried using the main form by itself, and that works fine with the requery; however, it creates some new problems for the form because it jumps back to the first entry of the first record. I also tried moving the block of code to a different place, such as on-dirty for the first subform, but it didn't work. Thanks again for your help.

[OBP]

OCG have you tried this variant of the referring to a Tabbed subform for Requerying?

Private Sub Form_GotFocus()
Me.sfrmOutstanding_Purchase_Orders.Requery
End Sub

[OCG]

Thank you OBP for your suggestion. Unfortunately, I wasn't able to make that work either. I also tried using the "on-click" event of the tab which accesses the subform, but that did no good either. I'll keep trying. Sooner or later, something has got to give.

Thanks again for your help.

[OBP]

OCG, please note where I have used the above code, it is in the Sub Forms got Focus event. You can force the mainform to give focus to the Tabbed sub form using the Set Focus method.
I know it is only a work around, but Tabbed sub forms have some peculiar behavour.

[OCG]

Thanks OBP for your reply.

I have tried to work it a couple of ways. I can transfer the focus to the subform, but I can't get it to requery. I tried posting the requery in the combo box after update: Forms!Tabs.sfrmTuitionRemission.SetFocus
Me.Form.Requery
Forms!Tabs.SetFocus

and this does set the focus to the subform, but it doesn't requery.

I also tried putting the requery code in the got focus event of the subform:
Private Sub Form_GotFocus()
Forms!Tabs.sfrmTuitionRemission.Form.Requery
End Sub

No go.

Thanks again for your help.