Full Version: Problem creating new records via form
Ocelot
(Apologies for the length of the post)
I am attempting to create a system that records the holidays of employees in a company.
Each employee has a maximum amount of holiday per financial year, and they are able to "bank" a portion of this for future use after theyhave completed each week of work. E.g. 52weeks in a year, max. of 23days holiday allowed per year, working out at 0.442.. days holiday accrued for each completed week of work.
So far the system comprises of 3 tables, "Employees", "Holiday Records" and "Week".
"Employees" contains the employees ID code, name and holidays remaning (however this last field is the one I am having problems with.
"Holiday Records" contains records of all holidays taken by all employees; the ID code of the employee taking the holiday, the holiday start date and the length of the holiday.
"WeeK" contains records of the start date of each working week, e.g. 9/12/05 is the start of Week 6 etc.
I have then compiled several queries. "HolidayEarned", "HolidayTaken" "weeknumberlookup" and "HolidayAvailable".
"weeknumberlookup" is used to caclulate which week it is using criteria "Between Date() And Date()-6". This works perfectly.
"HolidayEarned" works by taking the weekno. calculated from weeknumberlookup and mulitplies it by the holiday accrued by that week.
E.g. "HolEarnedToDate: [WeekNo]*0.442307692307692".
Again this seems to work fine.
"HolidayTaken" works by totalling the length of all holidays taken by each employee;
"TotalHolTaken: Sum(Nz([HolDuration],0))".
(Note the nz() function is used as not all employees have taken holidays.)
"HolidayAvailable" takes the previous 2 calculations and subtracts them to get the holiday the employee has left available;
"HolAvail: [HolEarnedToDate]-[TotalHolTaken]".
Now this is the feature that I believe is causing the problem.
I require a form that enables the user to input a new holiday for an employee. I have created one using the following fields;"EmployeeID, Employee Name, HolDate and HolDuration". This form worked fine and I was able to enter new records as desired, however I needed to introduce the final part of it, namely a text box that displayed the employees holiday left available, in order to be able to check that the holiday length entered did not exceed the holiday remaining. This is where the problem lies. After adding a box linked to "HolAvail", I am no longer able to enter new records via the form, but merely can look at already existing records.
I have attempted to utilise update/append queries to store the "HolAvail" data in a table, however these return many errors despite not showing any obvious reasons (to my limited knowledge).
If more information is needed, I would be able to supply the database in its entirety (approx. 200kb zipped) if required.
If anyone could offer some assistance I would be extremely grateful.
Steve
Larry Larsen
Hi Steve
Welcome to Utter Access Forums

With all you have written it would help if you could post up your db so we can read & look at your problem.
It would also help if you could have a small amount of data (no confidential data) to work with.
Ocelot
Apologies for some of the unusual TEST names.
Larry Larsen
Hi
Use the DlookUp() to extract the HolAvail from query called HolidayAvailable Query but don't forget to apply the criteria that selects which employee.
Ocelot
Sorry if this is a silly question but where exactly do I use the Dlookup() function, in a new or existing query or form?
And if possible, could you run through using the Dlookup() function with regards to the data on my system?
IA
Steve
Larry Larsen
Hi
In the control source of an unbound text box on the form.
DLookup Usage Samples.
Ocelot
Thank you for the link, however it highlights another problem.
Looking at the syntax of dlookup(), DLookup("FieldName" , "TableName" , "Criteria= 'string'"), it seems that it has to lookup the HolAvail value in a table, however the HolAvail value is located in a query.
Is their a way to place this calculated HolAvail into a table automatically?
I have attempted to do so with the update/append in the db I posted earlier, however I have been unsuccessful.
Could you take a look at the queries in question and highlight where it is I am going wrong in their usage?
Thanks
Steve
Larry Larsen
Hi<
Is not the way to go..
You have the query calculating the holiday available for each employee so use the DlookUp() to extract that value.
eg:
HolAvaliable = DLookUp("HolAvail","QueryName","EmpId = x")
Ocelot
I have placed this function ;
olAvaliable = DLookUp("HolAvail","HolidayAvailable Query","EmployeeId = '"& forms!FormHolidayRecords!EmployeeID & "'")
into the control source for the bottom text box on form "FormHolidayRecords", howvever despite doing so it is displaying "#Name?".
Have I used dlookup correctly, or is it something unrelated?
Steve
Larry Larsen
Hi
EmployeeID numric..?
For numerical values:
DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)
Try:
HolAvaliable = DLookUp("HolAvail","HolidayAvailable Query","EmployeeId = " & forms!FormHolidayRecords!EmployeeID )
Ocelot
Sorry for the seemingly never ending problems, but Ii tried the function you just posted, and the text box still returns the value "#Name?".
Larry Larsen
Hi
Check out the db attached and look at the form called Form1..
Ocelot
That is pretty much what I want, however I am confusing myself when trying to edit it to do what I need it to.
need the form to have a dropdown combo box like the one you created that displays first name/last name etc, however after selecting the employee all that appears in this box is the employee ID. The first name and last name of the selected employee should then appear in text boxes below.
Under this I need to have another 2 input boxes, one for the holiday date and another for the length, which I am guessing are linked to query named "HolidayRecords Query".
After the user inputs the length of the holiday, I need this value to be checked against the value HolAvail for that employee, and if the value inputted is larger, to display some sort of error dialog box stating so.
I would be ridiculously in your debt if you could find the time to put together a working example of the above, as I am quite literally tearing my hair out over this, and seem to be encountering problem after problem.
Thanks
Steve
Larry Larsen
Hi Steve
Ok.. I have a go later.. pick up with you tomorrow. (or are you in a hurry)
Ocelot
No, there is no real rush for this, I'll just be happy once this is all finished
Thank you for all your help so far
Steve
Larry Larsen
Hi steve
Check out the attvhed db..
I have create some new tables and relationship to try and normalize the stucture.
It based on:
Many prople can have many different holiday types..
Many types of holiday can have many people.
Using a M:M stucture.
You will need to put some thoughts about how your going to couple with weekends (unless you work weekends) and how you skip both sat/sundays..?
The form that I have altered is called "FormHolidayRecords" and has a record source of the the junction tacble called tblHolRecords, most of the values regarding hol/taken hol/entitlement hol/remaing is derived from performing on the fly calculations so check out the coding used behind the form. You see I have used a number of DlookUp() which are based on stored queries for their data source.
Any Questions please post back, I'm away till mid afternoon.
Ocelot
Amazing!
This is almost exactly what I required aside from one or two minute alterations.
Theres just one tiny snag to iron out. If you would care to download the attached db, and take a look at "frmNewHoliday".
After selecting an employee, e.g."john smith" and then the hol type, the holidays the employee has left remaining is displayed, in john smiths case, 3 days available. However, if in the holiday duration field "4" is entered the dialog box saying the employee does not have enough holiday remaining does not appear.
Olooked at the form you altered "FormHolidayRecords" and checked the vb code and I think that you have set it so that the dialog box appears if the holiday duration exceeds the holiday entitlement value. This is incorrect, as the "holiday entitlement" figure is rather the overall holiday the employee has accrued to date, not taking into account holidays taken.
Therefore, I need that dialog box message to appear if the entered holiday duration exceeds the holidays remaining value for that particular employee, and to force the user to input a value that does not exceed this, as the holidays remaining value can not be a negative figure.
Additionally, in the attached db could you also take a look at "FormHolidayRecords" and in particularly the vb code relating to this form, as it appears in my attempts I have somewhat messed the code up. (The error happens when you select an employee from the dropdown list).
I thank you immensely for the time and work you have put into helping me so far, and hope you can help me overcome this seemingly "final hurdle".
Steve
Larry Larsen
Hi Steve
Osure this code is checking the duration against the holiday remaining which is currently (3) with an entitlement of (3).
CODE
[color="red"]If Me.txtDuration > Val(Me.HolidayRemaining)[/color] Then
MsgBox ("Employee does not have enough holidays for this duration..")
Me.txtDuration = 1
Me.txtDuration.SetFocus
End If

Larry Larsen
Hi Steve
Odon't seem to be able to generate the error on the combo..?
Can you tell what it say's..
DB returned with a list box displaying current holidays booked.
Ocelot
Thank you Mr.Larsen, that new code works a treat
With regards to the error on "FormHolidayRecords", it prompted the vb debugger to start, however, rather embarrassingly (or fortunately depending how you look at it ) I can no longer replicate it, and it appears to have fixed itself.
Thanks again for all your help, I have learned much from looking at your examples and code.
Hopefully now I will be able to complete my system and put it to use
Steve
Larry Larsen
Hi Steve
Great.. and good luck with the rest of the project.
Ocelot
Hello again

I have made many improvements to my project, and have most of the system finished, or so I thought.
Attached is the project in its current state.

I recieve errors upon adding new holidays for certain employees, using "frmNewHoliday", and am at a loss as to why these are happening.

Would anyone be kind enough to take a look at the system and see if they can point out the error, and any other problems or issues they may come across.

TIA

Steve
Edited by: Ocelot on Mon Dec 19 7:23:46 EST 2005.
MrSiezen
What errors do you get?
Ocelot
In frmNewHoliday, if i select say, employee named Ben Foster, then select the holiday type, date and duraion, when I attempt to either create a new record or close the window of frmNewHoliday and save the entered data, I get the error message;
You cannot add or change a record because a related record is required in table tblEmployees".
Steve
Larry Larsen
Hi Steve
The problem lies in our referencing..
Change this..
CODE
'/ Lets calculate what they have left..
Me.HolidayRemaining = Format(Nz(DLookup("HolAvail", "[HolidayAvailable Query]", "EmployeeId = " & [Forms]![FormHolidayRecords]![cmboEmpID]), 0), "0")
'/ Lets find out what they are entitled too and round it up..
Me.txtEnt = Round(DLookup("HolEarnedToDatex", "[HolidayEarned Query]", "EmployeeId = " & [Forms]![FormHolidayRecords]![cmboEmpID]))
'/ Lets find out how much they have used this year..
Me.txtHolsTaken = DLookup("TotalHolTaken", "HolidayTaken Query", "EmpID = " & [Forms]![FormHolidayRecords]![cmboEmpID])
'/ Lets calculate the end date of the holiday based on startdate+duration..
/ This just plays around with a label
Dim Dayleft As Integer
'/ Lets just calulate what they have left..
Dayleft = Format(Nz(DLookup("HolAvail", "[HolidayAvailable Query]", "EmployeeId = " & [Forms]![FormHolidayRecords]![cmboEmpID]), 0), "0")

----------------------------------------------------------------------------
To this..
----------------------------------------------------------------------------
CODE
'/ Lets calculate what they have left..
Me.HolidayRemaining = Format(Nz(DLookup("HolAvail", "[HolidayAvailable Query]", "EmpId = " & [Forms]![frmNewHoliday]![cmboEmpID]), 0), "0")
'/ Lets find out what they are entitled too and round it up..
Me.txtEnt = Round(DLookup("HolEarnedToDatex", "[HolidayEarned Query]", "EmpId = " & [Forms]![frmNewHoliday]![cmboEmpID]))
'/ Lets find out how much they have used this year..
Me.txtHolsTaken = DLookup("TotalHolTaken", "HolidayTaken Query", "EmpID = " & [Forms]![frmNewHoliday]![cmboEmpID])
'/ Lets calculate the end date of the holiday based on startdate+duration..
'/ This just plays around with a label
Dim Dayleft As Integer
'/ Lets just calulate what they have left..
Dayleft = Format(Nz(DLookup("HolAvail", "[HolidayAvailable Query]", "EmpId = " & [Forms]![frmNewHoliday]![cmboEmpID]), 0), "0")
Select Case Dayleft

in both forms..
FormHolidayRecords
frmNewHoliday
Ocelot
Hello there again Larry Laursen -o!
I have made the changes you suggested, but I still recieve the error as previously posted.
Any thoughts?
Steve
Larry Larsen
Hi Steve
This one works..
Ocelot
Hi there Larry.
Thanks for the attachment, your edit has fixed the problem, however on frmNewHoliday, the HolidayRemaining value seems to be a little wrong, displaying the figure "82" no matter which employee is selected.
I am pretty sure there is something in the vb coding causing this but I am not sure which part exactly.
Steve
Larry Larsen
Hi
Copy & paste this line as a replacement..for:
/ Lets calculate what they have left..
Me.HolidayRemaining = Format(Nz(DLookup("HolAvail", "[HolidayAvailable Query]", "EmpID = " & [Forms]![frmNewHoliday]![cmboEmpID]), 0), "0")
Change to..
'/ Lets calculate what they have left..
Me.HolidayRemaining = Format(Nz(DLookup("HolAvail", "[HolidayAvailable Query]", "EmployeeID = " & [Forms]![frmNewHoliday]![cmboEmpID]), 0), "0")
HTH's
Ocelot
Nice one again Larry
cant stress enough just how much youve helped me
Odoubt my project would even be out of the early development stage if it wasnt for your help.
Thanks
Steve
Larry Larsen
Hi Steve
No problem.. glad I could help..
Ocelot
Sorry to keep on with the questions, but Im having several minor difficulties with this form again (frmNewHoliday).
) In the duration field, I need to limit the user to inputting values equal to or larger than 0.5, in .5 increments, (0.5, 1, 1.5, 2, 2.5 etc)and for this duration to be equal to or smaller than the holiday they have left remaining. Ive tried implementing several strategies for this, however I keep hitting the same flaw. Currently a msgbox appears if the value is <=0.5 or larger larger than holiday remaining, however if the user cancels off these msgbox', they are then free to close the form, thereby saving either a record of duration "0" or a duration larger than the holiday remaining, causing the holiday remaining figure to be reduced to a negative number.
Therefore I believe I require a command so that when the user attempts to close the form, these criteria are checked, and if the data passes inspection, then close as normal, but if the data does not meet this criteria, then gives the user two options, to either a) cancel the closing of the form and amend the data so it matches the criteria, or b) cancel the entry of this data, and not have the information stored in my tables, (i.e.tblHolRecords).
HAs you can see from the attachment, I have attempted to do the above with no success.
2) I need the Confirm Holiday button to work in a similar fashion;on clicking it, check the data to the above criteria , and save it in the tables if it passes, if not produce a msgbox. Also, if the data passes after it has been saved in the table, reset the form to its original state to allow another holiday to be entered.
3) I need a cancel button on this form that does not save any entered data, but returns the user to the main switchboard.
Hopefully someone can help
TIA
Steve