Displaying 20 query results on 20 text boxes

[serenechaos]

Hi,




I am trying to change the text of a number of text boxes on a form - I want each text box to display one item of a query which returned 20 items - so 20 text boxes, each displaying one of the query table items.


For textbox1, as a start, i've tried setting the control source, but keep getting #Name? displayed in the text box. What am I missing?

(Here are a few of my attempts):


=[AttQuery]![StName]
=DLookUp(["1"],[AttQuery]![StName])

[isladogs]

If I read your post correctly, the query returns 20 records and you want to display one field from each record in 20 textboxes.
Not a good idea for a number of reasons including unnecessary complexity.
Also what if there are 21 records next time?

Suggest scrapping that idea and instead output query results to a listbox
This can show any number of records and also if required more than one field

[serenechaos]

Hi, it's for a daily attendance tracker - I want the form to display the 20 attendees and then mark them present/absent/other on the form for different periods of the day. The number of attendees change, but it's never more than 20.

[serenechaos]

Hi, it's for a daily attendance tracker - I want the form to display the 20 attendees and then mark them present/absent/other on the form for different periods of the day. The number of attendees change, but it's never more than 20. I might change the system later on - for now I'm still very fresh in Access so I'm trying to get to the bottom of this problem so I can learn from it, whether I use the current system or not.

[CJ_London]

create a continuous form with your query as the recordsource. drag the field or fields as required into the detail section

It will display as many or few attendees as are available

to create the form automatically, select the query, then in the ribbon go to the create ribbon then either select the form wizard and follow the prompts or under more forms, select multiple items

in edit view you can then move your columns around, resize or delete them if not required

[serenechaos]

Ah thanks Ajax! So I've managed to get the IDs and names displayed on the form and have resized them. Now to get attendance slots next to the names (e.g. check boxes for absence in 3 periods of the day), am I right in thinking I just lay out the check boxes next to the names and then take the user input from those text boxes and feed that to a new table (i.e. if a user checks one of the check boxes next to Andrew, it will record that Andrew was absent in the breakfast period on 25 Feb in an absence log table), right?

[ArviLaanemets]

On base what I did read out from your posts, you need:

1. An Employee registry table, p.e. tblEmployees: EmployeeID, FirstName, LastName, WorkingFrom, WorkingTo,...;
2. Optionally, a calendary table, p.e. tblCalendary: CalDate, IsWorkday, ...;
3.a) A table for working time registering, p.e. tblWorktime: WTID, CalCate, EmployeeID, WorkStart, WorkEnd, BreakFStart, BreakFEnd, ... (when only a single entry for a worker on a day is allowed);
3.b) A worktime log, p.e. tblWorktimeLog: WTLogID, CalDate, EmployeeID, EntryType, EntryTime, ... (allows to register several work periods on same day, or being ill/on travel/on leave, etc.).

The form you are discussing belongs to point 3, and it looks like you are going for variant a.

In this case I think at 1st opening of database, an OnOpen procedure for application must generate missing rows into tblWorktime running INSERT query like (assumed tblWorktime.WTLogID is autonumeric, and you have tblCalendary)

INSERT INTO tblWorktime
cal.CalDate, empl.EmployeeID
SELECT 
FROM tblEmployees empl INNER JOIN tblCalendary cal
WHERE
cal.CalDate >= empl.WorkingFrom
AND
(cal.CalDate <= empl.WorkingTo OR emplWorkingTo Is Null)
AND
cal.IsWorkday
AND
cal.CalDate Not IN (SELECT CalDate FROM tblWorktime)

User registers all movements on form based on tblWorktime. A possible design:
Single Main form based on tblCalendary, p.e. fCalendary, which by default activates the record for current date. You can have there a navigation combo to select any of earlier dates too, when you want to allow this for user;
A continous form like adviced by Ajax, p.e. fWorktime, as subform of fCalendary. Subform is linked to main form on source table fields [CalDate] in both forms. So a list of all employees at date active in main form is displayed;
User enters (current) time into any of controls linked to fields WorkStart, WorkEnd, BreakFStart or BreakFEnd. On leaving the record, the entry is saved into tblWorktime.

[isladogs]

You said the register needs to have at least 3 options Present/Absent/Other
Therefore I suggest that you don't use checkboxes as these only give you two options

Below is an attendance register form I use for schools - lesson or session attendance
The allowed inputs are / or \ for Present (am/pm), O for absent, L for late, M for medical etc.
A combo box is used so staff are guided in their choice.



Additional info can also be added and student photos can be viewed.
It only contains one lesson per form as the classes vary each lesson
If yours stay the same for each period, you could obviously have columns for each on the same form

Not all of that is relevant to you but it may give you ideas on how to do this

One word of warning - do not store the data in multiple fields (columns) as though it was a spreadsheet.
To ensure normalised data each record in the attendance marks table should contain an ID and ONE lesson/period mark ONLY

The form shown above uses a crosstab query for the data source
However crosstab queries are READ ONLY so a temp table is created based on the crosstab for use on the form.
The attendance data is then saved back to the main table.
It sounds complicated but is instantaneous in practice as far as end users are concerned

[serenechaos]

ridders52 that looks amazing and shows almost exactly all the features I need to implement. Off to learn crosstab queries then! Do you have a password input box connected to the teacher initials drop-down / is that possible / what authentication did you implement? My attendance sheet would be a daily one with diffferent instructors in different periods but each day has the same periods at the same times. Instructors sometimes cover for each other (so on paper sign in their boxes for a period) and heads of department / other trainers sometimes make changes to the absence information, then on paper the change is recorded with a note and signature on the side - so i'm thinking whenever the instructor id changes it asks for a password so that the person making changes is identified in a column to the right.

[isladogs]

ridders52 that looks amazing and shows almost exactly all the features I need to implement. Off to learn crosstab queries then! Do you have a password input box connected to the teacher initials drop-down / is that possible / what authentication did you implement? My attendance sheet would be a daily one with diffferent instructors in different periods but each day has the same periods at the same times. Instructors sometimes cover for each other (so on paper sign in their boxes for a period) and heads of department / other trainers sometimes make changes to the absence information, then on paper the change is recorded with a note and signature on the side - so i'm thinking whenever the instructor id changes it asks for a password so that the person making changes is identified in a column to the right.

It's part of a much larger schools database used for reporting, pastoral issues, exam results etc
Staff login with user name and password so the system knows who is logged in.
It also has all class and student info stored ready for the registers and other purposes
Staff covering lessons use this form as well
However if external supply teachers are covering a lesson, they are given a paper copy which is then entered onto the dB by admin staff

If you want more info, send me an email using the link in my signature line.
I'll then send you a PDF describing how it works. A DEMO version is also available on my website (no code)