Add record to Subform when not on Mainform

[Bob]

Hi Everybody

I have a form called frmListBox that is connected to a table tblListBox.

This is opened from a form called "frmInvoiceOrde r" which has a subform called "zfrmInvoiceOrd er"
and floats above (ie is pop-up) [frmInvoiceOrder]![zfrmInvoiceOrde r]

Private Sub ListBox_DblClic k(Cancel As Integer)
DoCmd.OpenForm "Forms![frmInvoiceOrder]![zfrmInvoiceOrde r", , , , acFormAdd

Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtCreatedDate] = Me!ListBox.Colu mn(1)
Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtCreatedTime] = Me!ListBox.Colu mn(2)
Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtFrom] = Me!ListBox.Colu mn(3)
Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtTo] = Me!ListBox.Colu mn(4)
Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![MemoContents] = Me!ListBox.Colu mn(5)
Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtEbay#] = Me!ListBox.Colu mn(6)
End Sub

Although all of the fields copy from "frmListBox " to "Forms![frmInvoiceOrder]![zfrmInvoiceOrde r" OK
I cannot get it to add a new record to/in the subform before it copies the record.

The 2 main errors I get are:

error 2012 trying to refer to a form that doesn't exist.
error 2489 - The object 'Forms![frmInvoiceOrder]![zfrmInvoiceOrde r' isn't open). but when I get this
error message, the subform is indeed open - i'm looking right at it!

It seems to me that although the subform is open "inside" the Mainform, Access 2000 doesn't
consider it to be open. Just viewable.

I did consider making an intermediate form and do it from there. I have done somethong similar
before. But this would be an untidy work around

If anyone could help I be most grateful.

Regards Smiley Bob

[Allen Browne]

The main form frmInvoiceOrder is open *and* has a record at this point? If
it does not have a record, the attempt to create a *related* record in the
subform may fail.

Your middle paragraph that says the fields copy ok though it does not create
the record. If the fields copy (i.e. you see these values turn up the
subform), but the record is not added? This suggests that the code is
working, but there is another cause why the record is not saved, e.g. there
is another field that is required, but no value is entered.

Then the next paragraph suggests Access is having trouble finding the
object. Unless the main form has been closed by this stage, this may
indicate a Name AutoCorrect problem. See:
http://allenbrowne.com/bug-03.html

Subforms are never open in their own right, i.e. zfrmInvoiceOrde r itself is
not part of the Forms collection. You appear to be using the correct
reference though, through the main form, and referring to the Form in the
subform control. You could double-check that the subform *control* is named
zfrmInvoiceOrde r, regardless of the name of the form that gets loaded into
the control (its SourceObject).

This approach may help to pinpoint where the error is occurring:
Dim frm As Form
Set frm = Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form
With Me.ListBox
frm!txtCreatedD ate = .Column(1)
frm!txtCreatedT ime = .Column(2)
End With
Set frm = Nothing

Hopefully you don't really have a control named ListBox, as that is a
reserved word in VBA.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bob" <sm*******@hotm ail.com> wrote in message
news:ps******** *************** *********@4ax.c om...

I have a form called frmListBox that is connected to a table tblListBox.

This is opened from a form called "frmInvoiceOrde r" which has a subform called "zfrmInvoiceOrd er" and floats above (ie is pop-up) [frmInvoiceOrder]![zfrmInvoiceOrde r]

Private Sub ListBox_DblClic k(Cancel As Integer)
DoCmd.OpenForm "Forms![frmInvoiceOrder]![zfrmInvoiceOrde r", , , , acFormAdd
Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtCreatedDate] = Me!ListBox.Colu mn(1) Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtCreatedTime] = Me!ListBox.Colu mn(2) Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtFrom] = Me!ListBox.Colu mn(3) Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtTo] = Me!ListBox.Colu mn(4) Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![MemoContents] = Me!ListBox.Colu mn(5) Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form![txtEbay#] = Me!ListBox.Colu mn(6) End Sub

Although all of the fields copy from "frmListBox " to "Forms![frmInvoiceOrder]![zfrmInvoiceOrde r" OK I cannot get it to add a new record to/in the subform before it copies the record.
The 2 main errors I get are:

error 2012 trying to refer to a form that doesn't exist.
error 2489 - The object 'Forms![frmInvoiceOrder]![zfrmInvoiceOrde r' isn't open). but when I get this error message, the subform is indeed open - i'm looking right at it!

It seems to me that although the subform is open "inside" the Mainform, Access 2000 doesn't consider it to be open. Just viewable.

I did consider making an intermediate form and do it from there. I have done somethong similar before. But this would be an untidy work around

If anyone could help I be most grateful.

Regards Smiley Bob

[Bob]

On Thu, 10 Jun 2004 10:11:30 +0800, "Allen Browne" <Al*********@Se eSig.Invalid> wrote:

The main form frmInvoiceOrder is open *and* has a record at this point? If
it does not have a record, the attempt to create a *related* record in the
subform may fail. I dont think this is the problem. frmInvoiceOrder]![zfrmInvoiceOrde r is open with the next record's
ID# in the subform showing ready to go.
Your middle paragraph that says the fields copy ok though it does not create
the record. If the fields copy (i.e. you see these values turn up the
subform), but the record is not added? This suggests that the code is
working, but there is another cause why the record is not saved, e.g. there
is another field that is required, but no value is entered.
At the moment it just overwrites the existing record
Then the next paragraph suggests Access is having trouble finding the
object. Unless the main form has been closed by this stage, this may
indicate a Name AutoCorrect problem. See:
http://allenbrowne.com/bug-03.html

That is turned off

I have re arranged the code to this more shorthand way,
but I am still getting the problem that when I try to do anything with it, the code fails.

Private Sub ListOtherEmails _DblClick(Cance l As Integer)
Dim frm As Form
Set frm = Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form
With Me.ListOtherEma ils

DoCmd.GoToRecor d acDataForm, ["frm"], acNewRec, [offset]
<sticking Here, Ms Access can't find the field "|" referred to in your expression>

frm![txtCreatedDate] = Me!ListOtherEma ils.Column(1)
frm![txtCreatedTime] = Me!ListOtherEma ils.Column(2)
frm![txtFrom] = Me!ListOtherEma ils.Column(3)
frm![txtTo] = Me!ListOtherEma ils.Column(4)
frm![MemoContents] = Me!ListOtherEma ils.Column(5)

End With
Set frm = Nothing
end sub

Thanks for giving my Problem your attention

Smiley Bob

[Allen Browne]

Ah, so you are getting a record overwritten, instead of appended?

It would be possible to set focus to the main form, the subform control, and
then a control in the subform, and then RunCommand acCmdRecordsGot oNew to
get you to a new record before you try assigning the values. However, it may
be easier to write the record to the RecordsetClone of the subform instead.

You will need to pick up the primary key value from the main form, and
assign it to your foreign key field in the subform. Then you will be writing
to the fields in the subform's Recordset (which may have different names
than the controls on the subform). Something like this:

Dim frm As Form
Dim rs As DAO.Recordset
Set frm = Forms![frmInvoiceOrder]
If frm.NewRecord Then
MsgBox "Select a record in the main form first."
Else
Set rs = frm![zfrmInvoiceOrde r].Form.Recordset Clone
rs.AddNew
With Me.ListBox
rs![YourForeignKeyF ieldHere] = frm![YourPrimaryKeyF ieldHere]
rs!txtCreatedDa te = .Column(1)
rs!txtCreatedTi me = .Column(2)
'and so on.
End With
rs.Update
End If
Set rs = Nothing
Set frm = Nothing
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bob" <sm*******@hotm ail.com> wrote in message
news:it******** *************** *********@4ax.c om...

On Thu, 10 Jun 2004 10:11:30 +0800, "Allen Browne" <Al*********@Se eSig.Invalid> wrote:

The main form frmInvoiceOrder is open *and* has a record at this point? Ifit does not have a record, the attempt to create a *related* record in thesubform may fail. I dont think this is the problem. frmInvoiceOrder]![zfrmInvoiceOrde r is

open with the next record's ID# in the subform showing ready to go.

Your middle paragraph that says the fields copy ok though it does not createthe record. If the fields copy (i.e. you see these values turn up the
subform), but the record is not added? This suggests that the code is
working, but there is another cause why the record is not saved, e.g. thereis another field that is required, but no value is entered.
At the moment it just overwrites the existing record

Then the next paragraph suggests Access is having trouble finding the
object. Unless the main form has been closed by this stage, this may
indicate a Name AutoCorrect problem. See:
http://allenbrowne.com/bug-03.html

That is turned off

I have re arranged the code to this more shorthand way,
but I am still getting the problem that when I try to do anything with it,

the code fails.
Private Sub ListOtherEmails _DblClick(Cance l As Integer)
Dim frm As Form
Set frm = Forms![frmInvoiceOrder]![zfrmInvoiceOrde r].Form
With Me.ListOtherEma ils

DoCmd.GoToRecor d acDataForm, ["frm"], acNewRec, [offset]
<sticking Here, Ms Access can't find the field "|" referred to in your expression>
frm![txtCreatedDate] = Me!ListOtherEma ils.Column(1)
frm![txtCreatedTime] = Me!ListOtherEma ils.Column(2)
frm![txtFrom] = Me!ListOtherEma ils.Column(3)
frm![txtTo] = Me!ListOtherEma ils.Column(4)
frm![MemoContents] = Me!ListOtherEma ils.Column(5)

End With
Set frm = Nothing
end sub

Thanks for giving my Problem your attention

Smiley Bob

[Bob]

Hi Allen

Thanks for the attention you are giving my problem. I feel now that I am close to solving it now.

Private Sub ListOtherEmails _DblClick(Cance l As Integer)

Dim frm As Form
Dim rs As DAO.Recordset
Set frm = Forms!frmInvoic eOrder
If frm.NewRecord Then
MsgBox "Select a record in the main form first."
Else
Set rs = frm![zfrmInvoiceOrde r].Form.Recordset Clone
rs.AddNew
With Me.ListOtherEma ils
rs![InvoiceOrderID] = frm![InvoiceOrderID]

'Code runs OK down to here. It even creates a new record on the subform, which is something I
'haven't been able to do before.

'However, it is now faulting on the next 5 lines error 3625 Item "Item not found in this collection"

rs!txtCreatedDa te = Me!ListOtherEma ils.Column(1)
rs!txtCreatedTi me = Me!ListOtherEma ils.Column(2)
rs!txtFrom = Me!ListOtherEma ils.Column(3)
rs!txtTo = Me!ListOtherEma ils.Column(4)
rs!MemoContents = Me!ListOtherEma ils.Column(5)

' If I comma the above 5 lines out It creates new blank subform records

End With
rs.Update
End If
Set rs = Nothing
Set frm = Nothing

End Sub

Regards Smiley Bob

[Allen Browne]

What is the name of the *field* (not text box)?

Perhaps it is "CreatedDat e", in which case you would need:
rs!CreatedDate = Me!ListOtherEma ils.Column(1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bob" <sm*******@hotm ail.com> wrote in message
news:ek******** *************** *********@4ax.c om...

Hi Allen

Thanks for the attention you are giving my problem. I feel now that I am close to solving it now.
Private Sub ListOtherEmails _DblClick(Cance l As Integer)

Dim frm As Form
Dim rs As DAO.Recordset
Set frm = Forms!frmInvoic eOrder
If frm.NewRecord Then
MsgBox "Select a record in the main form first."
Else
Set rs = frm![zfrmInvoiceOrde r].Form.Recordset Clone
rs.AddNew
With Me.ListOtherEma ils
rs![InvoiceOrderID] = frm![InvoiceOrderID]

'Code runs OK down to here. It even creates a new record on the subform, which is something I 'haven't been able to do before.

'However, it is now faulting on the next 5 lines error 3625 Item "Item not found in this collection"
rs!txtCreatedDa te = Me!ListOtherEma ils.Column(1)
rs!txtCreatedTi me = Me!ListOtherEma ils.Column(2)
rs!txtFrom = Me!ListOtherEma ils.Column(3)
rs!txtTo = Me!ListOtherEma ils.Column(4)
rs!MemoContents = Me!ListOtherEma ils.Column(5)

' If I comma the above 5 lines out It creates new blank subform records

End With
rs.Update
End If
Set rs = Nothing
Set frm = Nothing

End Sub

Regards Smiley Bob