Updating two fields from one form

[didacticone]

I have a database where there is a table called tbl_master which stores new data. I also have a table called tbl_daysoff, which has 4 fields, name, vacation, vacation used, sick, sick used. All fields other than the name are numeric. My question is if I have a form bound to tbl_master is there a way to change the value of any of the numbers in the tbl_daysoff table? For example, I have a command button on Form A which is bound to tbl_master, upon being clicked it updates record in tbl_master but also updates records in tbl_daysoff? Thanks.

[twinnyfo]

Short Answer: Yes

Long Answer: What do you want it to update?

Also, tbl_daysoff should also have a numeric field for your employees (remember our previous discussion?).

Provide a few more details and we can guide you to a solution.

[didacticone]

Yes, the employee field is numeric...i do remember! So the daysoff table has a list of employees and then a numeric value for each type of day. When I click on 1/2 Vacation for example on my other form that i have completed i would like to subtract .5 from the total in that table for that employee. i had it working my sloppy way (LOL) of opening a form reference the daysoff table, but wanted to learn if there is a correct way. So far i think there are two ways of going about it... vba setting the recordset? or linking the tables, which seems to be the better way, but my attempts so far have failed. curious your thoughts.

[twinnyfo]

I would go with a simple recordset, based upon how I think your table is set up: you have a record for each employee that holds how much leave/time off they have left?

As long as you are not concerned about having a running record of when time is granted off, this should be fine. however, becasue there appears to be very few controls over how work is documented, you might eventually run into employees with negative leave totals....

[didacticone]

ok, so would it look something like this:

dim rs as recordset
set rs = currentdb.openrecordset("tbl_daysoff")

Also, just as an FYI, our work is documented at a macro level with a much more comprehensive but also confusing methodology. My goal is to simplify that and how it translates to my employees.

[didacticone]

This is what i have come up with:

Private Sub cmdHalfVaca_Click()

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] = -0.5
        
      .Update
    End With

 
rst.Close
Set rst = Nothing
   
End Sub

The only problem is i want it to subtract from the existing number in the vacation field... it is just changing it to "0.5"... is there a way to do this?

[twinnyfo]

That's a great start. I think you should be able to manage the subtraction of 0.5 from the leave days?

[didacticone]

Sorry, I'm not sure what you mean... i ultimately would have two updates:

 ![Vacation] = -0.5
 ![VacationUsed] = +0.5

[twinnyfo]

Yes. That looks like it would be the proper approach.

[didacticone]

thats what i thought but its not performing the arithmatic, its just changing the value to "-0.5" rather than subtract from the existing number of 10 and make it 9.5.

[NeoPa]

He meant :

![Vacation] = ![Vacation] - 0.5
![VacationUsed] = ![VacationUsed] + 0.5

It all depends on exactly what you're storing - calculated values or deltas (differences/adjustments).

Typically, in Normalised databases (Database Normalisation and Table Structures), the deltas are stored and the totals are calculated. That I'll leave to you two to discuss.

[didacticone]

I actually just figured that out... but thank you for the info!

[twinnyfo]

And I wasn't thinking when I saw post.... Too much real work in the way!

[didacticone]

Yeah its a shame when real work gets in the way... LOL... thanks for everything... i have to start doing some cleanup on the db now that i think the bones are a lot stronger... if i run into any more issues i will post... if i dont (which would be a surprise lol) i just want to say it was a pleasure learning from you and thank you for all of your help.

[didacticone]

OK one issue I am having with the recordset is that it is only subtracting the type of day from the first record in the table "tbl_daysof f". The tables is structured very simply with 4 records, consisting of the 4 employees names, and then the type of day, and type of day used which are numeric.

All it is doing is subtracting from row 1. How can I set the recordset to subtract from the current employee?

I have tried the following to no avail:

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)
 If Me.Employee = "Joe Jones" Then
        With rst
      .Edit
        ![vacation] = ![vacation] - 0.5
      .Update
    End With
End If
 
 If Me.Employee = "Joe Jonesey" Then
        With rst
      .Edit
        ![vacation] = ![vacation] - 0.5
      .Update
    End With
End If
 
 
 If Me.Employee = "Joe Joner" Then
        With rst
      .Edit
        ![vacation] = ![vacation] - 0.5
      .Update
    End With
End If
 
 If Me.Employee = "Joe Jonesier" Then
        With rst
      .Edit
        ![vacation] = ![vacation] - 0.5
      .Update
    End With
End If
 
rst.Close
Set rst = Nothing