On click event to create/ammend subform record

[TechyGal]

Hi there!

I have setup a form in Access 2007 which uses the Active X Microsoft
TreeView Control Version 6 to break down the sub levels of my
records.

From this the main form is populated with the relevant record

information. (This works ok)

On the main form, which is called 'Room Schedule' I have a TabCtrl
with several Pages setup to view the different subforms e.g.
Activities, Components for the record (room) selected in the
treeview.


On the 'Components' Page I have 2 subforms:


'Item Schedule Subform' and 'SubItem_Audit'


I need to set this up so that when I click (Or double click) on the
field 'Item_Schedule_id' in the 'Item Schedule Subform' this
'Item_Schedule_id' value is copied into the 'SubItem_Audit' subform
into the 'Item_Schedule_id' field so that Audit data can be entered
for this record.


I have tried using the following VB code on the On double click
event:


Private Sub Item_schedule_id_DblClick(Cancel As Integer)


On Error GoTo Err_Item_schedule_id_Click


Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "SubItem_Audit"
stLinkCriteria = "[Item_schedule_id]=" & "'" & Me!
[Item_schedule_id] & "'"


DoCmd.OpenPage stDocName, , , stLinkCriteria


Exit_Item_schedule_id_Click:
Exit Sub


Err_Item_schedule_id_Click:
MsgBox Err.Description
Resume Exit_Item_schedule_id_Click
End Sub


But I realise that this code searches for a record that already
exists
with the same 'Item_schedule_id' as that which was clicked on.


If a record does not exist it brings up a blank form.


This is not what I want to acheive as I don't want to OPEN a new form
as a popup, I would like the information to be visible in the correct
subform ('SubItem_Audit') which is visible as a datasheet to the
right
of the current subform ( 'Item Schedule Subform', aslso shown a s a
datasheet) form on the components page.


If the record does not already exist then I would like to create a
new
record with the 'Item_schedule_id' entered in.


Is this possible and can anyone help me in achieving this???


I would be immensley greatful and let me state that I am just a wee
nipper in the land of VB coding and database development so please be
gentle.


I am very keen to learn and would be lost without these groups.


With Thanks in anticipation,


TechyGal