Filling form with index

[WimDC]

In my project, I want to display all the employees in rows and columns, filtered by their department.



Suppose (simplified) :
In a company, there are multiple departments with multiple, but max. 10, employees in each department.
I want a form with a dropdown (to choose the department) to display the individual employee-information (name, age, job-title,...) in unbound text-boxes in 2 "rows" of 5 "columns".
I think that all of the seperate "boxes" (= very small subforms ?) need an index.
I would like to know if it's possible to fill the information with vba / loop / index starting with the first unbound text-fields (displayed in a kind of a box) for the first employee and so for the second employee to the last employee for the choosen departement.
Source will be a query of the all_employees-table, filtered by the choosen department.

Any help (or sample database with form and vba) is appreciated !

Wim

[Welshgasman]

Look at Crosstab query?

[Micron]

You are complicating things way too much. Far easier to create a query that references the form combo, base the form on that query which then returns this info to the form. So many ways to do it, but building code for unbound controls doesn't seem to make much sense. The only thing you'd have to worry about is setting the form recordsource after it is opened, otherwise your query may prompt you for the combo value. Or simply load all records (thus query does not use criteria) then filter with the combo. Or don't set the form recordsource until a combo value is chosen. So I'm not saying no code at all, just less than what you describe.

[WimDC]

Hi,
thanks for the replies, but I think my question was not clear enough.
The query is not my problem, but the way to display the returned data in a form with "grid layout" (rows of 5 employees next to each other and multiple rows if more employees)
Wim

[Micron]

Sounds like you mean names in columns. Could you mock something ip on Excel & copy & paste so we can see? You might need a cross tab query as suggested but they so have their limitations.

[CJ_London]

You want 5 columns but for each ‘column’ you want to display name,age etc

to do this your query needs to combine name,age etc into one field. Then you need a means to determine which column and row the combined field needs to appear in

And to do this you need a ‘counter’ field for each employee per department. Then with this counter field you can determine which column and which row- on my phone so perhaps someone else can pick up the actual calc but to determine the row it would be something like

counter mod 5

and to determine column, something like

counter\2

You would the use a crosstab query with the column value for the column, the calculated text for the value and a combination of department and row for the row

you have not provided any information about your data structure so impossible to suggest how you determine this but might be a sub query or domain function utilising the pk of the employee and the fk of the department

[WimDC]

Hi,
This is how I would like as result (if possible...). Each employee will also have a photo (this will be saved in a text-field as a URL to a local file)

When department 1 is choosen :


When department 2 is choosen :


All employees, departments and job-title will be stored in separated tables, and linked together in a query (with filtering on choose department)

[CJ_London]

As an alternative to post #6, review using report- set the number of columns to 5. Display as a sub report on your form

[WimDC]

could be a good suggestion, but... subreports within a form can't show multiple columns.
(I added a very very simple database as test) : mcc.accdb

Any suggestions to change it to the result I want (see screenshots above) are welcome !

[Micron]

I'd urge you to forget the idea. Might look nice, but is fraught with difficulty and future problems. What if you get an 11th employee - redesign everything? What if one leaves and you get another and decide you want to maintain history? You can't unless you redesign at least the underlying query or code. Or forget doing this on a form and try a report only and use the column layout for it. Or just go with what the form is designed to do - show data in rows. With 10 active employees you'd only have 10 rows and could also maintain history.

[CJ_London]

Any suggestions to change it to the result

only per post #6 - situation complicated by the requirement for an image as well