Autofilled txtbox on continuous form AND one control to do two calculations (1 Viewer)

[RandomT]

Dear Readers,

I'm working on a staff attendance database where it should

• generate total hours worked per staff
• generate report with staff total hours and leave (if there any)

I've put them all together in one form with the the followings:
Date on main form then staffname, login time, logout time, leaveID and total hours

comboboxes: Staffname list from Staff Table and cmbleaveID - LeaveID (LeaveID primary table is Leave Table)
textboxes: Login, logout and leaveID from ATtendance table
madeup txtbox: total hours (named txtTotalHoursMins)

Everyone who comes to work logins and logs out - total hours worked is automatically generated in txtTotalHoursMins textbox.

NOW, what I want is that; if i choose a particular LeaveID from the dropdownbox, i want txtTotalHoursMins textbox to automatically generate assigned hours to that particular LeaveID. Can this happen? Otherwise, suggest better ideas

In addition, COMBOBOX AUTOMATICALLY GENERATE VALUE ON TEXTBOX ON CONTINUOUS FORM. I've worked around combobox that automatically generates value in another textbox, BUT when i view the form in Form View and choose a value from the dropdown box, the OTHER RECORDS' TEXTBOXES automatically generate the same value assignedd to the chosen combobox value. How can I make sure that the combobox value can only apply to the particular staff member? I used this code:

Private Sub cmStaffName_AfterUpdate()
Me.txtLocation = Me.cmbStaffName.Column(1)
End Sub

Thank you so much in advance,
Teresa

 

[thunder]

first of all access is a relational database so you should do the following put all personal data in a table and the logs or what ever you want to count in another table and connect them with the ID Field. the query that will get the info you want will be composed of both Personal info Table and logs Table and you will get your data without even going to VBA builder
hope this will help

 

[jal]

I agree with Thunder. Here's how I would do it. Your Staff table should have an EmpID column (autonumber) as its primary key and then your leave table should also have an EmpID column (which is NOT autonumber) and thereby you can connect the two tables on EmpID at Tools > Relationships (graphical view). Likewise the attendence table should also have EmpID column and be connected. Start with that and let us know how it goes.

Personally I am not sure whether Attendance Table and Leave Table need to be two separate tables. You may have to research that yourself.

 

[RandomT]

Thanx Thunder and jal,

I have a Staff Table with their details, Leave Table with the leaveID's and the assigned hours eg; CASL as LeaveID in LeaveID field, Casual Leave in the Description field and 7hrs in the allocated hours field. I also have the Attendance Table with date(from Date Table), EMPID as one field and LeaveID as one field, Login and Logout. So basically, my Attendance Table is my composite entity table between my Leave Table and Staff Table.

In my Attendance Form, I only have the date (from Date Table) in the main form, and the subform in my main form has the EMPID, Login, Logout, LeaveID and a madeup textbox (txtTotalHoursMins). Now, I can automatically calculate the total hours using the Login and Logout textboxes in my txtTotalHoursMins textbox when i manually enter the logins and logouts. BUT, I also want my txtTotalHoursMins textbox to automatically generate my total hours assigned in my LeaveID table once I select one LeaveID from my LeaveID combobox. I'm guessing i might have to use VBA on my txtbox (txtTotalHoursMins) to either calculate the total hours between login and logout OR, generate the assigned hours when a leaveID is selected???

I've been using the date as a main Primary Key to connect all my data between my tables in the Attendance Form. Will try what jal said to connect the Leave Table and Staff Table and see it from there.

Thanx

 

[jal]

Interesting - I'd never heard of using a Date Table and using date as a primary key. I wonder if that idea has some merit....

 

[missinglinq]

In addition, COMBOBOX AUTOMATICALLY GENERATE VALUE ON TEXTBOX ON CONTINUOUS FORM. I've worked around combobox that automatically generates value in another textbox, BUT when i view the form in Form View and choose a value from the dropdown box, the OTHER RECORDS' TEXTBOXES automatically generate the same value assignedd to the chosen combobox value. How can I make sure that the combobox value can only apply to the particular staff member?

This is normal behavior for an Unbound control on a Datasheet or Continuous View. In order to have it retain a value for a given record, you have to bind the control (in this case, the txtLocation textbox) to a field in the underlying table/query.

Alternatively, since this is a Continuous View form, you might want to actually bind the combobox to a "location" field in the table. That way, the location appearing in the combobox on RecordA will reflect RecordA's location, the combobox on RecordB will reflect RecordB's location and so forth.

 

[RandomT]

Found a way to solve my proble, i've been using DATE as PK, i had to change things around and now using DateID instead of DATE as PK. I also understand that in Continuous form, if i assign a formula to a control, it will apply to the rest of the records like it or not AND, one control such as textbox can only do one calculation, NOT TWO. Thanx for enlighting me to figure this out some other ways.

 

[missinglinq]

Glad you got things working!