Timesheet database issues

[matt beamish]

Hello a couple of years ago, I wrote a flexi-time recording database that myself and a few other colleagues use. Times are logged against jobs *(held in another database) and a balance is calculated showing a credit over 7.4 decimal hours per day. This credit is capped at 7.4 hours so a user can never transfer more than 7.4 hours per month ( I used IIf for this)

The database has, I thought, worked well, but this looks like it is only because users have always worked more than they should (sad but not uncommon), and the balance carry forwards are not correct when the figure is less than 7.4.

Would any kind sole have the time/inclination to check my database over for me? Im not very good with VBA, so most of what Ive done is either within Controls and Queries.

Thanks in hope

Matt

 

[DCrake]

Matt
It would be extremely helpful if we could actually have a copy of the database to look at. Unfortunately we are not mind readers.

 

[matt beamish]

Thanks - I thought Id sound people out first rather than assume that anyone would look. It will also take a little time to sort out how to send it, as its split, and Ill need to strip out confidential stuff

thanks again

Matt

 

[matt beamish]

The front end is timesheets_mgb.mdb and the back timesheets_be.mdb.

I've embedded several tables in the one be file to simplify, and left in all the times and job nos but updated any identifying text to something nondescript.

The button "Time Sheet" opens the Form where jobs are logged and hours calculated. I designed it so that hours are logged in 60min hours and converted to decimal hours for calculations and storage. The button on the right is the same form with hidden controls visible.

The jobs go into a subform related to a day. Microsoft dates are converted into txt day month years with a series of queries.

All carry overs are calculated on the form using a series of queries and some macros. Carry overs are capped at 7.40 hours each calender month.

My issue is getting the Carry Forward from one month to become the Brought Foward in the next, but I am having difficulty referencing a calculated field on a previous form (or form with previous months txtyearmonth) using DLookup.

Any advice very gratefully received but I appreciate that this is a big ask as there is quite a lot in there, and you will see that I only dabble in database design as Im actually supposed to be something else, and therefore my design may be flawed, and I find pages of VBA code diffucult to grasp

Thanks anyway

Matt

 

[matt beamish]

Ok I have prompted no responses, so either my database design is so flawed that I am beyond hope, or I have not asked a clear enough question. So I will be more specific - but honestly any advice really appreciated.

A form
"F_Daily_overall_Time_Sheet" is based on data from "Q_Work_Days". I have then used various DSums and Dlookups to gain values from other queries that calculate running balances and carry forwards. The only stored values in the database are the start and stop times of each task, and the date. No balances are stored.

I want to find and use the value of a calculated control "Permitted_Carry_Forward" for a form record where the value of a control "txtYearMonth" is one less than that of the current record. The value of the control "Permitted_Carry_Forward" is the same for all records with the same value for "txtyearmonth".

How would I reference this value? Can I use DLookup to derive the value of the calculated control, or do I need to reference the queries that are used to calculate this value? If I can reference the form control it would be a cleaner reference

thanks

Matt