How do you create a static autofilled column on a form...

[Guest]

.... and then have related txtboxes that can be filled in with data collected
during the day?
I am creating a database that tracks a call center performance. I collect
data from the staff 4 times a day. I want a form that has a column
automatically populated based on if they are available and what team they are
on. Then I want to have fields typed in with the performance data
(associated to the staff member) and stored to it's own table - allowing for
measures to be done for reports. I know I can create subforms to manage the
team aspect.

I am new to Access and can navigate around "upfront and easy" tasks. VBA is
Greek to me - but I do understand the basic concept of programming structure.

Any ideas?

Thank you in advance.

 

[J]

I'm not sure I understand what you mean. By column you mean, "Field",
right?

You said "if they are available". How does the program know this?

If you want to autofill things you have a couple of options:
1) Set the default value (bonus: Use Date() to set the default as
today's date)
2) Inside the "OnCurrent" event, use:
If Me.NewRecord Then
TxtBox1 = "DefaultValue"
TxtBox2 = "DefaultValue"
End If

and so forth.

Please clarify so we can be more help!

~J

 

[Guest]

I hope this helps.

I created a table and form to track attendance with the key being employee
#. Before I begin to collect data from my teams, I enter in whether they are
"here". This is the key to my data collection form in which members of my
staff are displayed if they are in attendance.

I currently have a table to track my staff by name with the key being the
employee #. I thought I could create a form that takes its fields from a
query indicating whether they are present and then populate a form set up in
columns. The names in one column with field text boxes lined up in
corresponding columns for the times of day that I do my data collection. I
currently use a spreadsheet for this purpose and would like to maintain that
similarity of format.

I appreciate your attention and assistance on this question. If I
understand the OnCurrent code, this will allow me to autofill the name column
after I enter data for the one name previously filled. I was hoping to bring
up a form, use a procedure that instantly fills all names of the staff who is
present (based off the attendance form) and then allow me to go down a column
adjacent to the names to enter their performance data. Then, at subsequent
intervals in the day, I would bring up another form (or the same one) which
would fill in all existing names and data, add in any other names of those
whose shifts begin during the elapsed time and then accept data entry for the
new collection period. I would need to accomodate 4 collection times a day.
Of note, once an employee's shift ends, I would still need to report their
performance of that day, stored in a table named performance that would be
the basis for future reports and recall.

I hope I am not asking too much as I need the database to accomodate a
growing staff with multiple teams (which I have accomodated and can
accomodate on my data entry form.

Again, thanks for your help in this problem of mine.

Jeff

 

[J]

OK let's see if I understand this right:

First Table: Employee Names
Second Table: Employee Attendance
Third Table: Employee Performance

You want one form where you enter in the date followed by
present/absent
You want another form where you input performance measures for present
employees


I recommend consolidating into two tables (a rare choice for access)
TableEmployees
TablePerformance

TableEmployees is nothing more than their names and numbers
TablePerformance has the following fields:
-EmployeeID
-DatePresent
-Performance1
-Performance2
-Performance3
-Performance4

OR if you want the number of performance checks to be dynamic,
-EmployeeID
-DatePresent
-PerformanceTime
-PerformanceMeasure
....and then use crosstab queries to spread it out



Now, when an employee is present your first form will programmatically
make an entry in the TablePerformance with just the individual employee
and "DatePresent". Then later on the next form will look up all the
employees with "DatePresent" = Date(). Then you can enter your
performance metrics.

Form one should be a continuous form with recordset = TableEmployees...
with a bound textbox "TxtEmployeeID" and an unbound checkbox.
Private Sub Chkbox_afterupdate()
docmd.setwarnings false
docmd.runsql "INSERT INTO TablePerformance (EmployeeID, DatePresent)
VALUES (" & _
TxtEmployeeID & ", #" & _
Date() & "#)"
docmd.setwarnings true
End sub

Now Form Two should be a continuous recordset equal to a query of
TablePerformance where DatePresent = Date()... and display the
following fields: EmployeeName, DatePresent, Performance1,
Performance2, etc... this is where you need a crosstab query if you
went the dynamic number of performance checks route.


If that's not what you needed, try posting the design your using (table
names and all fields)
~J