By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,226 Members | 1,435 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,226 IT Pros & Developers. It's quick & easy.

Assign values to new Record with info form last record.

P: n/a
Hi all! I have 3 separate forms: Tasks, Subtasks, and Elements. All 3
is related by TaskId and Subtasks and Elements are related by
SubtaskID. In the DB after I add a task, I want to be able to add one
or more subtasks and then one or more elements. I need the subtasks
and elements to be have the same taskid as the task; also I want the
element to have the same subtaskId as the subtask. I have been able to
design the system to include one subtask and one element but when I
call DoCmd.GoToRecord , , acNewRec, I cannot get the add'l
subtask/element to be related to the original task and previous record.
I have tried unsuccessfully to set the values of the TaskId and
SubtaskId in the forms but I'm not sure if I am using the right coding
convention because it is not working -- this is the code I use to move
to the next record and assign the values:
Private Sub CreateNewSubtask_Click()
DoCmd.GoToRecord , , acNewRec
Forms![Subtasks].[TaskID] = ID 'ID is
the name of tblTasks primary key
Me.subtaskID =
Forms![Subtasks].[subtaskID]
End Sub

Mar 7 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
> I have 3 separate forms: Tasks, Subtasks, and Elements

let's start with *tables*, not forms. do you have three separate *tables* -
tblTasks, tblSubtasks, and tblSubtaskElements? if each task may have one or
more sub-tasks, then the relationship is
tblTasks.TaskID 1:n tblSubtasks.TaskID

if each sub-task may have one or more elements, then the relationship is
tblSubtasks.SubtaskID 1:n tblSubtaskElements.SubtaskID

are elements *directly* related to tasks? if not, then tblSubtaskElements
should not be linked to tblTasks, but only to tblSubtasks.

if your tables are related as described above, and the relationships are
defined in the Relationships window, then you can easily use a
mainform/subform/sub-subform setup to handle the data entry. create a form
bound to tblSubtasks (frmSubtasks), and a form bound to tblSubtaskElements
(frmSubtaskElements). add a subform control to frmSubtasks, and set the
control's properties as follows:

SourceObject: frmSubtaskElements
LinkChildFields: SubtaskID <this is the foreign key field, in
tblSubtaskElements.>
LinkMasterFields: SubtaskID <this is the primary key field, in
tblSubtasks.>

create a form bound to tblTasks (frmTasks). add a subform control to
frmTasks, and set the control's properties as follows:
SourceObject: frmSubtasks
LinkChildFields: TaskID <this is the foreign key field, in tblSubtasks.>
LinkMasterFields: TaskID <this is the primary key field, in tblTasks.>

now when you add new subtasks to a task, and new elements to a subtask, they
should be linked automatically by Access - without needing any code to do
it.

suggest you read up on tables/relationships; when you structure those
correctly, you can better utilize the power of Access (which is based on
relational design principles) to make your programming life a whole lot
easier. see
http://home.bendbroadband.com/conrad...resources.html for
more information, beginning with the Database Design 101 and Starting Out
links.

hth
"dBNovice" <lu*****@hotmail.com> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...
Hi all! I have 3 separate forms: Tasks, Subtasks, and Elements. All 3
is related by TaskId and Subtasks and Elements are related by
SubtaskID. In the DB after I add a task, I want to be able to add one
or more subtasks and then one or more elements. I need the subtasks
and elements to be have the same taskid as the task; also I want the
element to have the same subtaskId as the subtask. I have been able to
design the system to include one subtask and one element but when I
call DoCmd.GoToRecord , , acNewRec, I cannot get the add'l
subtask/element to be related to the original task and previous record.
I have tried unsuccessfully to set the values of the TaskId and
SubtaskId in the forms but I'm not sure if I am using the right coding
convention because it is not working -- this is the code I use to move
to the next record and assign the values:
Private Sub CreateNewSubtask_Click()
DoCmd.GoToRecord , , acNewRec
Forms![Subtasks].[TaskID] = ID 'ID is
the name of tblTasks primary key
Me.subtaskID =
Forms![Subtasks].[subtaskID]
End Sub

Mar 8 '06 #2

P: n/a
Thank you for your response. The relationships are exactly like you
said and yes the elements are directly related to the tasks. I created
the forms w/ subforms but didn't like how the forms looked. I wanted
to give the system to someone else to use and wanted it more
aesthetically pleasing. They prefered the look of separate forms.

Mar 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.