Show records for each date

[umair434]

Hi,

I have a subform with many text boxes - these text boxes are supposed to show the different fields in the original table.

What I want to do is - let the user select a date from a combo box, and as the user selects a particular date, the text boxes are all populated. Also, the user should be able to edit these text boxes and save the changes without having to add new record (over writing data).

A new record is only added if a new date is selected. I have a query (mainquery) with the following SQL that shows the values I want, but how do I use the result to populate these text boxes:

SELECT Table2.Date, Table2.Employee_ID, Table2.[Caseflow Shelving Available], Table2.[Caseflow Shelving Actioned], Table2.[High Cube Available], Table2.[High Cube Actioned], Table2.[Prewab Report Available], Table2.[Prewab Report Actioned], Table2.[Run to Zero/Frozen Availabl], Table2.[Run to Zero/Frozen Actioned]
FROM TblEmployees LEFT JOIN Table2 ON TblEmployees.Employee_ID = Table2.Employee_ID;


I would really appreciate if someone can help me out here -

I can post the sample database if someone would like to have a look.

Thanks!!

 

[jzwp22]

Where is the date combo box? On the main form or the subform?

Since your query has a left join, you may make the query's recordset un-updateable (will not be able to edit the data). What is the purpose of having the employee table as part of the query? Do you want your users to view the data by employee? If so, you may want base the query (and thus the subform) only on table 2 and use a combo box in the main form to select the employee and then your date combo box to select the date. You would then use those selections to filter the query and thus the subform. If there are no matching records then allow the user to enter a new record.

We'll need a little more details of what you are really after with this form/subform setup.

 

[umair434]

Hi, thanks for taking out time to reply. I really appreciate it!

and date combo should be on the subform.

 

[umair434]

I have posted the database - I will work on your suggestion and will report to youu!!

Employee table is added in the query because I needed something to link the main form to the subform -

 

[jzwp22]

What is the form name that is causing the issue?

 

[umair434]

the thing is: My manager does not want to use combo box to select an employee. He wants it to be extracted from windows Id (which I have working thanks to your help before).

What I want is to show the user their employee Id, their name - in the subform they select a date - If records exist for that date then text boxes would be populated, which can be edited and saved.

Whenever user selects a date, the records FOR THAT DATE show up in the boxes - If no records are available, then the user should be able to enter data for that date
hope i am making some sense!!

 

[umair434]

tblEmployees is the main form. frmtable2 is the subform!

 

[jzwp22]

I took a look at your database, table2 is not structured properly.

 

[umair434]

Yeah i know! i have been trying to tell this to my manager but he thinks it's not going to affect much. Here is the another version of application which has combo boxes -

the thing is manager wants to make this extremely easy for the users (including not using combo boxes to select activity). He wants activities name to be fixed and text boxes infront of them so data can be added.

Is there anyone I can get the other version to work as desired? how do I normalize it and get text boxes then as well!

thanks again!

 

[jzwp22]

I'm not sure what the second database was for, so I went back to the first. I'm not sure if it does what you want.

the thing is manager wants to make this extremely easy for the users (including not using combo boxes to select activity).

Combo boxes are easy to work with.

He wants activities name to be fixed and text boxes infront of them so data can be added.

Will everyone have the same exact list of activities? Or does each person have a set list of activities? If either of these are true, then you can run an append query to create the list for each employee then they would not have to select the activity.

In order to normalize table2, you have to tell me what you are doing. It looks like there is a field for Available and Actioned for each item (caseflow shelving, high cube etc.) What do the fields represent? Is this some sort of inventory system?

 

[umair434]

there are sets of users for example: day employees, night employees, afternoon employees!

the list of activities for each one of them is fixed - ( i guess I can put them in a multiple tab sheet).

the database is for the users to enter the numbers they saw that were available and how many they actioned out of those. You are right, it is some sort of inventory system because I work for a distribution centre!

so high cube available means the number of items that were available when an employee used report "high cube"
high cube actioned = number of items that were reslotted to other locations

hope it makes sense.

 

[jzwp22]

So if a user has 5 high cubes available then reslots 2, the next user will see 3 available?

 

[umair434]

No. Each user is responsible for entering their own activities. they might be part of "Day time employees", but when they log in, they enter their information only and it is independent of other users information.

 

[umair434]

they just have to see some numbers and type it in the database (simple entry) - the trick is to make the activities available with text boxes so they just enter - and data is recorded under their name for a particular date.

For example:

John, day time employee, logs in on 11th Augusut and enters data for high cube, caseflow etc - He saves it and close the applicatoon. If john comes back at 12:00pm on the same day and selects 11th august, the text boxes would show the values he entered before (allowing him to edit as well).

 

[jzwp22]

I do not know for sure if you can get your forms to work the way you want, but if it were me, I would not want to work on the forms until I knew that the table structure was sound. To that end and based on the limited info you have provided, I would structure the tables like this:

tblEmployees
-pkEmployeeID primary key, autonumber
-txtFname
-txtLname

tblActivities (holds all available activities)
-pkActivityID primary key, autonumber
-txtActivity

tblEmployeeActivityLog
-pkEmpActLogID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees
-dteActivity (date of activity)


tblEmployeeActivityLogDetail
-pkEmpActLogDetID primary key, autonumber
-fkEmpActLogID foreign key to tblEmployeeActivityLog
-fkActivityID foreign key to tblActivities

tblActivityDetail
-pkActDetailID primary key, autonumber
-fkEmpActLogDetID foreign key to tblEmployeeActivityLogDetail
-fkActionID foreign key to tblActions
-YourValue field


tblActions (2 records: available and actioned)
-pkActionID primary key, autonumber
-txtAction

tblEmployeePredefinedActivities
-pkEmpActID primary key, autonumber
-fkEmployeeID foreign key to tblEmployee
-fkActivityID foreign key to tblActivities

The above table would be used to just store the activities for which the employee is responsible. You would use the table as the basis for an append query that will add the predefined activities on each new day when data needs to be entered.

 

[umair434]

thank you for your insight! I will normalize my table and play around with append queries to see what I come up with.

would it okay if I ask questions about this later when I'm done normalizing?

thankyou so much!

 

[jzwp22]

You're welcome. Of course, post back with any questions.

 

[umair434]

Hi jzwp22,

I am uploading the normalized database. How would I use this database to populate text boxes for each activity? What I would want to do is:

have activities name fixed on the form - User can then select Available or Actioned from combo boxes (maybe) and have "Your Value" text boxe next to it.

Date would be in a combo box - Selecting that would populate the combo box of (Action) and text box of Your value if previous records exist.

I am very confused how to approach this right now. Can you guide me here, please? Thank you so much!!!

 

[jzwp22]

In the attached database I show how would structure the form (frmEmployees). When a new date record is added in the subform (frmEmployeeActivityLog), the applicable activities are added for the employee shown in the main form. This is accomplished using some code in the after insert event of the employee activity log form that executes an append query.

 

[umair434]

wow! i cannot thank you enough!! I am going to play with this and try to learn as much as I can! Again, thank you so much! You are amazing!