Complex Form

[thedeadzeds]

Hi Guys,

Not sure if this is possible but if it is it will be perfect.

The attached database tracks staffs working times and any time out throughout the day. Each day their Team Leader (FL) will update the individuals details in ‘Frm_Input_V2’. Currently they have to complete a record for each person by adding individually staff name after staff name. So input the data for staff member 1 and then click add to database and so on and so on until their team is complete.

Is it possible to switch this around and have the Team manager select their name (FL) and have the form bring up everyone in their team, so they can complete the data for all people in their team in one go. I have attached a BIPMAP of how I envisage the form the look.

Many thanks

 

[Ranman256]

yes, in a form, shows 2 list boxes...
a list box of teams, , rowsource query: qsTeams
a list box of persons, lstPersons.rowsource = qsPersons1Team

sql for qsPersons1Team = select [person] from table where [team] = forms!frmPick!lstTeams

user clicks the team list,
the after update event will fill the person listbox.
lstPersons.requery

user can now dbl-click items in the lstPerson list to add to a 'picked' table.
via append query , qaAdd1Person
the sql adds the item in the list box to the picked table

the do whatever you want with the picked items. Join them to a data table, etc.

 

[plog]

Yes, but...

Your tables are improperly structured. Whenever you start putting specific values in field names, its time for a new table. All those _Hour and _Tag fields should probably go in another table. I'm pretty sure those fields suffixed with '2' should be somewhere else as well.

Additionally, you shouldn't use a name as a foreign key between tables (http://www.w3schools.com/sql/sql_foreignkey.asp). You should have an autonumber primary key in Tbl_Names and then use that in Tbl_Input instead of the actual name.

Lastly, you should only use alphanumeric characters and underscores in Table/Field names. That means no spaces nor weird characters in names ( parenthesis or ampersands).

Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). Try and fix your tables accordingly, then post back here your structure if you need more help. After you get the structure right, then its time to move on to reports then forms.

 

[thedeadzeds]

Sorry Plog, I dont have time to read up at the moment as this is pretty urgent. Thanks for the advice though, if anyone else can help with the current format it would be much appreciated

Many thanks

 

[plog]

Installing a ceiling fan in the master bedroom is never urgent when your foundation is cracked.