I've been struggling with this for a bit now. I'm trying to create a Freeday program for our HR department. I have the code figured out how to use a multi-Select List box to add new earned freedays to the tblFreedays. However I need to also update the earned hours based on the multi-select listbox. I attached the database to this posting. Below the list box is an add button that I would like to have it update the tblFreedays date earned(which is working) and also increment the daysearned and daysavailable field in the tblEmp(this isn't working) I added a cmdbutton for Update Hours to practice writing a code to make this happen. Any help would greatly be accepted.

Thanks in advanced

Welcome to UtterAccess!

You would not need to store the days earned in two tables. You can use a query to count the days earned in your tblFreeDay (find those records where date used is null and then group by employee). What is the days available field in your employee table? Can you explain this a little further?

The days available field in the employee table is used for tracking how many days the employee has left for use. The date Earned value is stored in the freedays for reporting and will allow for the HR dept to see when each employee earned a freeday off work. So for each Date an employee earns one freeday. I have the add button adding a date for each employee but however having an issue updating the DaysEarned in the tblEmp. It should be as simple as editing a record based on the value in the listbox. I get close to this working but the EmpName gets overwritten with the EmpID number or It only selects the first record in the tblEmp to make the updates on.

If an employee earns a day tblEmp DaysEarned increments by 1 and DaysAvailble increments by 1

I would like to have everything assigned to the cmdAdd button to fire this. I did create a cmdUpdateHours button to try and figure out how to update DaysEarned and Days Available but ultimately I would like to have it all on one button.

If the days available is just the number of earned days that have not yet been used, you would not need to store that in the employee table either. The two values you need can be calculated from your free day table. The earned would be just the count of all days earned by each employee whereas the days available would be the count of those earned days that are not yet used. Storing the counts adds extra work, but more importantly violates good database practices.

I took the database you posted and modified it based on the suggestions I proposed in my earlier post. The modified DB is attached.

I did notice that you are using lookup fields at the table level. Although Access has the capability it is a practice that is discouraged. This link explains why. I removed the lookup and set your empID field to a number field. I also changed the employee name field to employeeID field in the free day table. Databases are usually more efficient when the joining fields between tables are numbers not text data type fields.

KK, got it figured out.
I attached the rev version of the Freeday Database for anyone that is intersted in how I've done this.

Regards,

Although you were able to provide a fix, your solution is still storing values that should be calculated when needed and not stored. Your approach does not follow good database practices. Perhaps I failed to adequately explain, so perhaps this link might explain better.