Cannot enter data into a query

[mreference]

I am struggling to figure out what is wrong with my query, it won't let me input any data. I have tried a few combinations with my query but just cannot seem to work it out.

To give you an understanding of what I am trying to do, one of the first forms that needed to be completed is a Job form, this form takes in data such as who the job is for, cost, target date.

Then I have included 2 subforms, one of which allow individual officers to be assignd the job (linked by job number), the second is a list of tasks assigned to each of the job (again linked by job number).

I am having trouble entering data on a timesheet for an officer.
I have created the main form that allows an officer to be selected and to also choose the period he/she will be entering time in against.

I am trying to create a continuous subform that firstly, only displays the jobs that have been assigned to the officer selected in the mainform, then also display the tasks that have been assigned to the job and then finally allow fields to be input with time against the tasks of each job.

It's this query that doesn't allow me to either input any data or if I tweak it, it doesn't display any rows.

As the query seems complex, am I able to attach the database for someone to look at?

I look forward to any help anyone can provide.

 

[the_net_2.0]

at first glance m, you may want to have a look here: http://allenbrowne.com/ser-61.html

 

[mreference]

Thanks for the info, I did manage to find this page previosuly and the ony one I think it maybe is:

It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.

When I have removed tables it does work, but it doesn't then give me what I need.

 

[mreference]

I have if somebody can help, attached the document I am working on. It's the frmTimesheet that I am having the most challenges with.

The subform subfrmTimesheetInput show the job number and where a user can input the hours, but it does not link it to officer or show the tasks specific to the job.

The second subform subfrmTimesheetInput1 displays the job and tasks but no input for hours, plus it will not allow any input to the subform

I have tried other variations of linking all 3 queries (qryOfficeronJob, qryJobOfficersTasks, tblJob-which should be a query and some other linking queries such as officers and tasks.)

If somebody could help me with my subform to do the following:

I am trying to create a continuous subform that firstly, only displays the jobs that have been assigned to the officer selected in the mainform, then also display the tasks that have been assigned to the job and then finally allow fields to be input with time against the tasks of each job.

kind regards

 

[boblarson]

The problem is that you are attempting to use a single subform when in fact you need to have different levels of subforms. You can't do it the way you want with a single subform and the several One-To-Many joins. You would need to use several SubSub and perhaps SubSubSub forms.

 

[mreference]

Thanks Bob, would you have time to prepare a form in my database based on what you said as I'm finding this bit extremely challenging after 4 days working at it.

I need to keep it as simple and straightforward as possible so that people buy into it and don't think it is too complicated.

kind regards

 

[boblarson]

Thanks Bob, would you have time to prepare a form in my database based on what you said as I'm finding this bit extremely challenging after 4 days working at it.

I need to keep it as simple and straightforward as possible so that people buy into it and don't think it is too complicated.

kind regards

I don't know what I can give to this as I'm suffering from my own issues currently at work. I'll see if I can do something but I may not be able to. But armed with what I've said, you might be able to get additional help from those on the forum if you post a new question in the Form's category and title it something like Need Subform/SubSubform help.

 

[Simon_MT]

For a start on the Form TimeSheet, Week, Month and Year can be derived from the Combibox cboWeek using a new Query [qryFinProfilingLookup] as the RowSource:

SELECT qryFinProfilingLookup.finprofileid, qryFinProfilingLookup.weekenddate, qryFinProfilingLookup.finweek, qryFinProfilingLookup.finmonthtext, qryFinProfilingLookup.finyeartext FROM qryFinProfilingLookup;

I have change finYear to finyeartext for consistency, you don't need tblfinweek as you have a Label containing Week.

FinWeek =[cboWeek].[Column](2)
FinMonth =[cboWeek].[Column](3)
FinYear =[cboWeek].[Column](4)

So all the tables tblfinprofiling, tblfinweek, tblfinmonth and tblfinyear can be removed from the query just using one combibox.

Now the timesheet what you want to do is is allocate the Officer to a Job and Task so I would suggest that you create a Table tbljobtaskdetails with:

job_nbr
taskID
officerID

Use combiboxes for the first two and set the default OfficerId to the parent. This is very sketchy but it gives you an idea on how to simplify entry and combiboxes makes life so much easier.

You could develop the tbljobtasksdetail to include all the events but that is later.

Simon

 

[mreference]

Thank you Simon, I have started creating what you mentioned. I am inputting the =cboWeek.Column(2), =cboWeek.Column(3) & =cboWeek.Column(4) but can only see 1 in the week column, the others are empty.

The underlying query works fine in the Row Source of the combo box, but the results are not coming through in the form.

Access is changing what you gave me =[cboWeek].[Column](2) to =cboWeek.Column(2) on each one as well.

On the second part of your response, I have created the table "tbljobtaskdetails", I'm unsure what you mean by set the default OfficerId to the parent

When I create the combiboxes for the first two, am I setting the rowsource to look from qryJobTasks and qryOfficeronJob?

Thanks so far, I'm feeling positive again and confident that I can deliver.

 

[Simon_MT]

Let me have a little look at it. I post back tonight.

Simon

 

[Simon_MT]

I have updated an example of what I mean. I have changed the timesheet form. Whilst this is by no means complete if gives some idea what to do.

I have create some modules but the Lookup doesn't work and I will investigate.

Simon

 

[mreference]

Thanks Simon, appreciate the time you can spend on it.

I have taken a look at what you have done so far, I understand what you have done except the module.

I did try playing around with the subform so that only jobs that apply to that officer appear, likewise with tasks applying to the job...no luck with that.

Anyway, thanks again and I look forward to hearing from you soon

 

[Simon_MT]

Here is an update, I have sorted out the module. Basically, what the module is trying to do is an application wide Function to handle the date. Once you select the week the dates of the week appear.

What happens if another Officer performs the task, even if a specific Officer is assigned to the task.

Simon

 

[mreference]

What happens if another Officer performs the task, even if a specific Officer is assigned to the task.

Simon

The officer is only scheduled to work the tasks that he is assigned to as some of them may not be specific to the job.

For example, a previous job at a location could be a feasibility study, the officers will input hours against this task.

Lets say the the clients at the location want to go ahead after the study, a new job will be created, but this time the feasibility study is not a task that will be required. Which is why I was hoping that only the tasks specified could be displayed.

It seems a tough one from my side to crack, but it's really uplifting when you can request help from the experts...it's a wicked community. I hope I can help someone out in time, it'll be a long time though I guess

 

[Simon_MT]

I'll post the database shortly. I have the Jobs looking for Officer and Tasks looking for Job. I have realised that I don't use this type of criteria at the subform level. I couldn't get the Task to work so I cheated by putting the job onto the Parent and referencing that. You may need to look at the Query forum for 'Referencing Subforms'.

The kids are waiting to fed, so I post after dinner.

Simon

 

[Simon_MT]

Right, I have uploaded the new database. I deleted a record from tblofficeronjob just so that I could see only one record or one officer and two for the other. I have also cleared out the tbletimesheetdetails. There is obviously some tidying up to do but this should get you going.

We here to help.

Simon

 

[mreference]

Simon, 1 word, awesome, you did it. My form already includes a message at the bottom (developed by a Mad kiwi in the corner). Your time has been appreciated in this.

If I'm pushing it now, please say

I have been trying over the weekend to use the [cboTimesheetDate] in the subform, as officers will be inputting a weeks worth of tasks in one go, could be as many as 20 rows (depends on how many projects they have been working on).

When they select the week they are inputting data for [cboWeek] in the main form, the relocated [cboTimesheetDate] would display the week dates in the subform, but also be able to input many rows for that week..

I also attempted to create two subforms, one for the data entry, the other to view previous records input. I included a button on the data entry form, but in a continuous form, it creates a button on each line, I guess a button in the footer would save the batch of records, rather than clicking on each one?

Once the officer has input a batch, they could review the submission prior to submitting all the jobs, which would remove them from the data entry sub-form, refresh the all jobs sub-form and display all jobs including the ones just entered.

 

[Simon_MT]

What is the time period for entering in Timesheets as this will affect how to handle the entry process, daily, weekly or monthly?

I would not encourage being able to bulk delete entries, simply because you want to instil into your users the need for accuracy. This is slightly semantic but I feel that users should take the entry information seriously rather than letting them being too casual. The second point is that they have to do the period timesheets all over again and becomes tedious particularly if they hit delete by mistake. Sure, allow single entries to be deleted.

Simon

 

[mreference]

What is the time period for entering in Timesheets as this will affect how to handle the entry process, daily, weekly or monthly?

I would not encourage being able to bulk delete entries, simply because you want to instil into your users the need for accuracy. This is slightly semantic but I feel that users should take the entry information seriously rather than letting them being too casual. The second point is that they have to do the period timesheets all over again and becomes tedious particularly if they hit delete by mistake. Sure, allow single entries to be deleted.

Simon

Hi Simon, based on some of the officers being based outside the building, I would like them to complete a form on a weekly basis, based on the week start/end dates in the combo. If they have not input a weekly timesheet and are on catch up, they would need to either close the form down after entering a week and reopen it or click a button that saves their timesheet, clears the data ready for another week to be entered.

In terms of deleting records, I wouldn't want them deleting anything that has been input and displayed in the other subform. I'd like them to amend amend the data they are inputting before saving it, but once it has been saved, any amendments will have to be corrected by the administrator, which will hopefully make them concentrate a bit more.

Does that make sense?

 

[Simon_MT]

Yes, there are several ways of doing this. One a superior confirms the Timesheets entries or the user Self approves the time sheet entry. I normally put the Entry on a Form and don't usually use SubForms for Entry but I would suggest that the Entry omits anything that has been Processed or Confirmed and create a separate screen for any enquiries, non-editable.

Your second question is add a button to start another week that clear the Top Level Week

Simon