Updating two tables from one form

[didacticone]

Hey!

So I have a created a timesheet database. It has a main form where an employee can sign in and out with a seperate button for each. When sign out is clicked it asks if the employee has worked a full day. If they click no it pops up a form bound to the same table as my main form (tbl_master) and asks which type of half day the employee is taking (vacation, sick, personal). It then adds the type of day to the employees record for that day. All of this is working great. The problem I am having is, I have a seperate table called "tbl_daysoff" which consists of 4 records (each employees name, and how many of each day off they have left, as a numeric value). I am currently using the following code to try to subtract from the numeric field and it is working but only on the first employee. It is not changing the value based on the current employee and I cannot seem to figure it out.. here is my code:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set MyDB = CurrentDb
strSQL = "SELECT * FROM tbl_daysoff"
Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
 With rst

    .Edit
      
    ![vacation] = ![vacation] - 0.5
    .Update
    End With

rst.Close
Set rst = Nothing

The form that opens when you click no for the question if you worked a full day is called "frm_typeofdayoff" and when that form opens it pulls the current record from the main form. I hope I gave enough detail... if not let me know and I will try to explain better. Thanks!

 

[Minty]

You aren't specifying an employee to update in your openrecordset.
So the .edit will simply edit the first record.

However....

Don't store this value in another table - simply calculate it based on the records already saved for the time sheet. This value w as a calculation will always be correct, and not reliant on catching an edit made anywhere in your system to the core timesheet data.


edit - Oh and Welcome to AWF !

 

[didacticone]

Thanks for the welcome!

I would prefer to have the seperate table with the exact numbers listed of days used and remaining like i have setup. The plan is to then use this for the employees. We also have carryover from previous years and some other variables that may conflict with your idea, if that makes sense?

I know im not referenceing an employee because everything i have tried has failed lol, so i jut posted the bones. I cannot seem to get it to reference the current employee in the form "frm_typeofday"- do you have any guidance on how to do this? Ive been struggling here for tooo long lol.

 

[Minty]

As someone that has inherited and attempted to manged a complex absence system in Access I can assure that that storing the values will lead to ever growing worlds of Pain.

Your employee table can hold the "fixed" per person values - Basic Entitlement, EmployementStart and EmploymentEnd dates, HolidayYearStartDate etc. All the rest should be driven from your records of absence. If someone edits or deletes an incorrect record your calculated values will always be correct. It's the same principle as a stock system, you don't store a qty on hand you calculate it based on ins and outs.

Even the carry over values can be calculated this way, and restricted by a field in the employee table HolMaxCarryOver, can you see how this expands out ?

By grouping the calculations based on the employee HolidayStart you get period values for each employee no matter when they started or when their holiday year ends.

 

[The_Doc_Man]

Use the search functions of this forum for certain topics. The "Search" option is 3rd from the right on the thin blue ribbon near the top of the page, just under the box that has your login name in it.

Search for this topic: "Inventory" and I recall we had some "Vacation" topics where hours had to be tracked.

Then realize that you are keeping an inventory of available hours for each individual. There should be a ton of information and some sample code attachments in that pile.

Keeping a single record per person and updating it will work but is going to be tedious to maintain. Keeping records of hours used, accruals, and adjustments as a type of transaction makes it easier in the long run.

 

[didacticone]

Ok, I understand what you're saying. I can consolidate the employees and days off tables. With that being said, I still have the same dilemma as I am unsure when using the recordset as to how to reference the correct employee?

 

[dmorgan20]

Does the employee have a unique identifier, such as payroll number etc? I guess not but the question.

You would have to update your SQL part of the code to something like:

strSQL = "SELECT * FROM tbl_daysoff WHERE Payroll = '" & EmployeePayroll & "'"

 

[didacticone]

the ID (primary key) field is unique but that is all.

 

[dmorgan20]

Is the ID for each individual the exact same for each individual in the primary table?

If so the code I put in my last reply would work

 

[didacticone]

no, because the primary table is the table that is storing the log in/out data so it has multiple entries for the same employee and the ID obviously changes each time. The only other thing that is unique and the same is the employee field, because obviously it only contains that employees name... or should i add a separate unique identifier to the primary table, which would have to be updated upon the employee logging in?

 

[Minty]

Your employee table should have a unique reference - lets call it EmpID.

This is what you store in all the related tables, not the employees name, as
a) What happens when you have 3 John Doe's ?
b) What happens when Jane Smith marries John Doe and her name gets changed? You lose all the historic data or bodge a query together to make it show both her names... yuck.

Adding your EmpID field conveniently avoids all these issues, as you can easily identify which John Doe you are identifying and making the record for.

 

[didacticone]

Makes sense- so here is what i did:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set MyDB = CurrentDb


strSQL = "SELECT * FROM tbl_daysoff WHERE EmpID = " & EmpID & ""
Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)

 With rst

    .Edit
      
    ![vacation] = ![vacation] - 0.5
    .Update
    End With

rst.Close
Set rst = Nothing

It is returning syntax error- missing operator - in query expression 'EmpID ='.

When I try the code formatted like this:

strSQL = "SELECT * FROM tbl_daysoff WHERE EmpID = '" & EmpID & "'"

I get this error:
Data type mismatch in criteria expression.

Both EmpID fields are numeric- long integer.

Any ideas?

 

[Minty]

Is the EmpID a field or control on your form. If so then use

strSQL = "SELECT * FROM tbl_daysoff WHERE EmpID = " & Me.EmpID & " ;"

Note the way you are then referencing it, the edit will only find the first record for that employee.

 

[didacticone]

OK, so heres something interesting, now regardless of what code i use i get the following:

"Cannot open any more databases."

 

[The_Doc_Man]

Yikes! You've got something in your code that violates one of the basic rules of life as expressed in that classic reference: "Everything I Needed To Know I Learned In Kindergarten." The rule is "If you opened it, close it."

Look in your code for something that opens a database explicitly. Once a database is open, you don't need to open the DB a second time to get to something else in that same DB. You might have to qualify what you seek by using DBvar.object syntax to find it, but once the DB is open, it is open for general use. The max number of tables you can have open at once is 2048, which is a very big number.

 

[didacticone]

on my main form at the top it says

Option Compare Database
Option Explicit

Is this what you are referring to?

 

[The_Doc_Man]

No, but those are good things to have in your code.

Opening a database involves (among other possibilities) a call to a method or action called 'OpenDatabase' and you generally have a module in which you have a DIM statement for something that is either an ADO.Database, a DAO.Database, or an unqualified Database that is used as the target of a SET <target-var> = statement. I would think that if you were diddling with database openings that you would know you had something like that.

Is this your original database or did you inherit someone else's pride and joy?

 

[didacticone]

The code i posted in this thread is my only reference to a DAO database, here it is again:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set MyDB = CurrentDb


strSQL = "SELECT * FROM tbl_daysoff WHERE EmpID = " & Me.EmpID & " ;"


Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)

 With rst

    .Edit
      
    ![vacation] = ![vacation] - 0.5
    .Update
    End With
rst.Close
Set rst = Nothing
Set db = Nothing

Something in there wrong? No other code references a database.

It is my creation with the help of someone with more experience.

 

[Minty]

You are opening MyDB as current database but setting db = nothing.

That should be Set MyDB = Nothing