422,929 Members | 1,355 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,929 IT Pros & Developers. It's quick & easy.

Updating two fields from one form

100+
P: 243
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.
Apr 4 '18 #1
Share this Question
Share on Google+
19 Replies


twinnyfo
Expert Mod 100+
P: 2,436
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.
Apr 5 '18 #2

100+
P: 243
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.
Apr 5 '18 #3

twinnyfo
Expert Mod 100+
P: 2,436
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....
Apr 5 '18 #4

100+
P: 243
ok, so would it look something like this:

Expand|Select|Wrap|Line Numbers
  1. dim rs as recordset
  2. 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.
Apr 5 '18 #5

100+
P: 243
This is what i have come up with:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdHalfVaca_Click()
  2.  
  3. Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim strSQL As String
  6.  
  7. Set MyDB = CurrentDb
  8. strSQL = "SELECT * FROM tbl_daysoff"
  9.  
  10.  
  11.   Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  12.     With rst
  13.       .Edit
  14.         ![Vacation] = -0.5
  15.  
  16.       .Update
  17.     End With
  18.  
  19.  
  20. rst.Close
  21. Set rst = Nothing
  22.  
  23. 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?
Apr 5 '18 #6

twinnyfo
Expert Mod 100+
P: 2,436
That's a great start. I think you should be able to manage the subtraction of 0.5 from the leave days?
Apr 5 '18 #7

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

Expand|Select|Wrap|Line Numbers
  1.  ![Vacation] = -0.5
  2.  ![VacationUsed] = +0.5
Apr 5 '18 #8

twinnyfo
Expert Mod 100+
P: 2,436
Yes. That looks like it would be the proper approach.
Apr 5 '18 #9

100+
P: 243
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.
Apr 5 '18 #10

NeoPa
Expert Mod 15k+
P: 30,856
He meant :
Expand|Select|Wrap|Line Numbers
  1. ![Vacation] = ![Vacation] - 0.5
  2. ![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.
Apr 5 '18 #11

100+
P: 243
I actually just figured that out... but thank you for the info!
Apr 5 '18 #12

twinnyfo
Expert Mod 100+
P: 2,436
And I wasn't thinking when I saw post.... Too much real work in the way!
Apr 5 '18 #13

100+
P: 243
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.
Apr 6 '18 #14

100+
P: 243
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_daysoff". 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:

Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim strSQL As String
  4.  
  5. Set MyDB = CurrentDb
  6. strSQL = "SELECT * FROM tbl_daysoff"
  7.  
  8. Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  9.  If Me.Employee = "Joe Jones" Then
  10.         With rst
  11.       .Edit
  12.         ![vacation] = ![vacation] - 0.5
  13.       .Update
  14.     End With
  15. End If
  16.  
  17.  If Me.Employee = "Joe Jonesey" Then
  18.         With rst
  19.       .Edit
  20.         ![vacation] = ![vacation] - 0.5
  21.       .Update
  22.     End With
  23. End If
  24.  
  25.  
  26.  If Me.Employee = "Joe Joner" Then
  27.         With rst
  28.       .Edit
  29.         ![vacation] = ![vacation] - 0.5
  30.       .Update
  31.     End With
  32. End If
  33.  
  34.  If Me.Employee = "Joe Jonesier" Then
  35.         With rst
  36.       .Edit
  37.         ![vacation] = ![vacation] - 0.5
  38.       .Update
  39.     End With
  40. End If
  41.  
  42. rst.Close
  43. Set rst = Nothing
Apr 9 '18 #15

twinnyfo
Expert Mod 100+
P: 2,436
First, when posting a procedure, please include the entire procedure (from Private Sub all the way to End Sub). This allows us to see everything that is going on (even though it looks like we have mostly everything.

The easiest way to do this is to have the SQL statement look for the person in question. But, before you start aiming at random possibilities, think about 1) what we have done with some of your other code when dealing with your employees in trying to streamline your code (Hint: use a function which receives an employee ID....) and 2) what similarities and what differences there would be which such a design. Plan it out, then design the procedure.

No--this is not an "answer", but it is guiding you to a solution, based upon principles we have already covered.
Apr 9 '18 #16

100+
P: 243
i have actually followed your advice and created a couple other functions for the database. With this case being we are referencing a recordset i am having trouble figuring out the logic... i need to understand that first before i create the function, so im kind of stuck before i got started lol.
Apr 10 '18 #17

twinnyfo
Expert Mod 100+
P: 2,436
Look at line 6 in your code in post #15. How would you modify the SQL statement to pull one employee's record. Then, if you know how to pull one employee's record, you should be able to pull any employee's record using a variable fed to that query's WHERE statement. Make sense?

Also, I thought your employees were referenced using an index in the employee's table?? Yes???
Apr 10 '18 #18

100+
P: 243
so i altered the sql statement to reference a specific employee, i believe correctly? I am stuck on how to pull any employee though?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdHalfVaca_Click()
  2. Dim MyDB As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim strSQL As String
  5.  
  6. Set MyDB = CurrentDb
  7.  
  8.  
  9. strSQL = "SELECT * FROM tbl_daysoff WHERE employee = ""Joe Smith"""
  10.  
  11. Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  12.  With rst
  13.  
  14.     .Edit
  15.  
  16.     ![vacation] = ![vacation] - 0.5
  17.     .Update
  18.     End With
  19.  
  20. rst.Close
  21. Set rst = Nothing
  22.  
  23.  
  24. End Sub
  25.  
And yes the employees are referenced using an index in the employees table.
Apr 10 '18 #19

twinnyfo
Expert Mod 100+
P: 2,436
Refer back to one of your previous posts.

We created a Function that received an Employee ID, performed actions on that Employee ID and then returned back to the original procedure.

Remember, one step at a time. Don't get hung up on the SQL statement just now; think about the structure of your code first. But I will tell you that you have the general idea in your post above.
Apr 13 '18 #20

Post your reply

Sign in to post your reply or Sign up for a free account.