Need a trick to save users time 1

[RobNauticus]

Hello All,

I am creating a db for tracking dates of tasks completed per date. The users create a job and then log in each task per job. All of the tasks will be entered at the same time per job. As I have it set up right now, the user must create each task one at a time and is very inefficient.

Is there a way to create an append query or piece of code that will create multiple sub-records by entering the date and clicking a button?

I have two tables one for job and one for tasks completed.

The task field is a task name (driven from another table of values) and date completed. Not to mention the two fields to link the data to the parent table.

Any guidance would be appreciated...



Rob

 

[MajP]

Not sure exactly what you are asking. Are there a specifice set of default tasks for a certain job type? If so I would have a table of default tasks, with the following.

tblDefaultTasks
autoIDTask
strJobType
strTaskName

When I enter a new job of a specific type, I query the tblDefaultTasks on that job type. I then do as append into the tasks table. Now the trick is to get the Job ID so that when I do the append I also put in a foriegn key to the Job. If you have more details, it would help.

 

[BSman]

I have to make some assumptions.

1. You have a table that lists for each type of job the tasks associated with that type of job. So, for example, if a job was "moving" you might have the following records:

task type description
moving get truck
moving load truck
moving drive to destination
moving unload truck

2. In your job table you include a column for the type of job. (Obviously, if there is only one type of job, then you won't need a job type column since the task descriptions table will all be for one task). The job table also contains a JobNbr column for the identification number/text of the specific job.

Based on those assumptions you would write an append query that would link the new job with the task descriptions based on the type of task. It would append the records into your tasks completed table.

Bob S.

 

[RobNauticus]

Bob, all of your assumptions are right on, I only have one type of task.

When I create an append query, how would I have it create a record for each of the 28 tasks?

Thanks

 

[BSman]

That's what an append query will do. You create the appropriate columns (to match your tasks complete table's columns) that needed to populate each record and as part of the query design you tell it that it will append the records to the tasks complete table. Logically the query would include a criteria that the job number will equal the new job number on the form.

Somewhere in code (click a button?) you will just run the query (after you make sure it really is a new job as far the records in the tasks complete table.

docmd.openquery "Your append query"

Bob S.

 

[RobNauticus]

Can you help me figure out how to structure the query related to the date? I have an unbound text field on my form that the user will enter the date he/she wants to populate all of the tasks with.

How can I get that into my query to fill the records? I tried an expression linking it to the field on the form, but all I get is a column of blanks with the header expr1.

Any suggestions?

Thanks,
Rob

 

[MajP]

Private Sub Detail_Click()
Dim strSql As String
DoCmd.SetWarnings (False)
strSql = "INSERT INTO tblTasks ( strTaskName, dtmTaskDate, intFkeyJob )" _
& "SELECT tblDefaultTasks.strTaskName, [Forms]![frmJob]![txtBxDate].[value]" _
& "AS TaskDate, [Forms]![frmJob]![txtBxAutoJobID] AS JobID FROM tblDefaultTasks"
DoCmd.RunSQL (strSql)
Me.tblTasks_subform.Requery
End Sub

this works