Select multiple records in subform to create new table

robtperk

New member
Local time
Today, 02:08
Joined
Aug 8, 2012
Messages
7
I need to create some new records based on main form data and a selection of records from a sub form. The main form and sub form have different sources. I wanted to show the source fields in the sub form along with a check box to allow the users to select individual records. The record source for the sub form contains >1000 records, so the user will first enter data in the main form, use filters to find the records he wants to 'assign' to the main form data, click those he selects, then click a command button in the main form to create the record(s) based on the main form data and the selected records from the sub form. The new records will be appended to a new table. Help is appreciated, especially if you have examples. TIA.
 
Below is a little snippet of what I'm working on at the moment.

Should help. Can't help much more as I've had a couple of beers.

http://allenbrowne.com/casu-04.html

You'll need some if statements for the checkboxes I believe. Better to use a list box if you can, then you can just use the bound value.

Forgive me, I'm a bit of a noob but trying to help! :)

Code:
'5 Gets AccountID of newly appended account

Dim NewAccountID As Long

NewAccountID = DMax("AccountID", "[tblAccounts]", "UserID = [TempVars]![UserID]")


'6 Gets ContactID of newly appended contact

Dim NewContactID As Long

NewContactID = DMax("ContactID", "[tblContacts]", "UserID = [TempVars]![UserID]")


'7 Adds new account contact

Dim strSQL As String

strSQL = "INSERT INTO tblAccountContacts ( AccountID, ContactID, Tel, Mob, Email, StaffID, UserID ) VALUES ("
strSQL = strSQL & " '" & NewAccountID & "' ,"
strSQL = strSQL & " '" & NewContactID & "' ,"
strSQL = strSQL & " '" & Me.[tboTel] & "' ,"
strSQL = strSQL & " '" & Me.[tboMob] & "' ,"
strSQL = strSQL & " '" & Me.[tboEmail] & "' ,"
strSQL = strSQL & " 1,"
strSQL = strSQL & " " & [TempVars]![UserID] & ");"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
 
BTW are you really wanting to create a new table? Seems to me like you want to just create a new record???
 
Thanks. Let me explain a bit more. I have a list of employees and a list of un-finished tasks, each with a budgeted number of hours for the task.

Each week I will bring up an employee's name in the main form, then select a few of the unfinished tasks in the sub form below. From this, plus stuff like week-ending date, I will create and print a pre-populated timecard, which the employee will fill out at the end of the week with the status of each task. From that information and the actual hours worked, I can determine productivity. Then everything repeats for the following week on the remaining unfinished tasks. Other stuff happens as well, but this is the short version.

I think I can do the code to create the records and append them to the progress table, but I can't figure out how to 'select' the task records from the sub-form.
 
Thanks. Let me explain a bit more. I have a list of employees and a list of un-finished tasks, each with a budgeted number of hours for the task.

Each week I will bring up an employee's name in the main form, then select a few of the unfinished tasks in the sub form below. From this, plus stuff like week-ending date, I will create and print a pre-populated timecard, which the employee will fill out at the end of the week with the status of each task. From that information and the actual hours worked, I can determine productivity. Then everything repeats for the following week on the remaining unfinished tasks. Other stuff happens as well, but this is the short version.

I think I can do the code to create the records and append them to the progress table, but I can't figure out how to 'select' the task records from the sub-form.

Need to sleep now fella, hard day! If no one else provides a solution I'll post one up tomorrow for you. I know the answers but I'm just too tired!

BTW add your rough location on your profile.

G'nite :)
 
If your subform is bound and your checkboxes are bound controls then they are updated on the fly.

For logging hours I'd use a table called tblTaskHours, that would include StaffID, TaskID, Hours and DateWorked. From this I'd create a query to total the hours for each task and compare to the budgeted hours.

I find it better to avoid using subforms when you can. Personally, I'd create a form, bound to a query called qryTaskDetails this would only show one record dependent on a Temp Var or List Box value, "TaskID". The TaskID would be provided by the AfterUpdate event of a list box along with Me.RecordSource = "qryTaskDetails" this will requery the form and show the details of the selected task. From there you can edit the task status, reassign, add notes and also log hours should you add the function.

Private Sub lboTasks_AfterUpdate()
TempVars.Add "TaskID", lboTasks.Value
Me.RecordSource = "qryTaskDetails"
End Sub

I've attached an image of a query that feeds data to the tasks list box. If you were to use something similar you'd replace TaskListID with StaffID.
 

Attachments

  • TaskListBoxQuery.PNG
    TaskListBoxQuery.PNG
    23.9 KB · Views: 325
Thanks for your suggestion. I went at it another way. I created my task/operation list, which luckily I could do in this case, and added a selected Boolean field. This was the source of a datasheet sub-form on an unbound main form. The main form provided a crewID which was the last piece of information I needed to create an assigned tasks table (which will be a temporary table for the week). Once the tasks have been assigned and the timecards pre-printed, I run an update SQL from code to clear the selected field value. Works :) Sorry for the delay in replying.
 

Users who are viewing this thread

Back
Top Bottom