Add record to Subform when not on Mainform

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hi Everybody

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

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

Private Sub ListBox_DblClick(Cancel As Integer)
DoCmd.OpenForm "Forms![frmInvoiceOrder]![zfrmInvoiceOrder", , , , acFormAdd

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

Although all of the fields copy from "frmListBox" to "Forms![frmInvoiceOrder]![zfrmInvoiceOrder" 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]![zfrmInvoiceOrder' 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
 
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. zfrmInvoiceOrder 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
zfrmInvoiceOrder, 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]![zfrmInvoiceOrder].Form
With Me.ListBox
frm!txtCreatedDate = .Column(1)
frm!txtCreatedTime = .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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

Private Sub ListBox_DblClick(Cancel As Integer)
DoCmd.OpenForm "Forms![frmInvoiceOrder]![zfrmInvoiceOrder", , , , acFormAdd

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

Although all of the fields copy from "frmListBox" to
Click to expand...
"Forms![frmInvoiceOrder]![zfrmInvoiceOrder" 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]![zfrmInvoiceOrder' 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
Click to expand...
 
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.
Click to expand...
I dont think this is the problem. frmInvoiceOrder]![zfrmInvoiceOrder 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.
Click to expand...

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
Click to expand...
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(Cancel As Integer)
Dim frm As Form
Set frm = Forms![frmInvoiceOrder]![zfrmInvoiceOrder].Form
With Me.ListOtherEmails

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

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

End With
Set frm = Nothing
end sub

Thanks for giving my Problem your attention

Smiley Bob
 
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 acCmdRecordsGotoNew 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![zfrmInvoiceOrder].Form.RecordsetClone
rs.AddNew
With Me.ListBox
rs![YourForeignKeyFieldHere] = frm![YourPrimaryKeyFieldHere]
rs!txtCreatedDate = .Column(1)
rs!txtCreatedTime = .Column(2)
'and so on.
End With
rs.Update
End If
Set rs = Nothing
Set frm = Nothing


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bob said:
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.
Click to expand...
I dont think this is the problem. frmInvoiceOrder]![zfrmInvoiceOrder 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.
Click to expand...

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
Click to expand...
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(Cancel As Integer)
Dim frm As Form
Set frm = Forms![frmInvoiceOrder]![zfrmInvoiceOrder].Form
With Me.ListOtherEmails

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

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

End With
Set frm = Nothing
end sub

Thanks for giving my Problem your attention

Smiley Bob
Click to expand...
Click to expand...
 
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(Cancel As Integer)

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![zfrmInvoiceOrder].Form.RecordsetClone
rs.AddNew
With Me.ListOtherEmails
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!txtCreatedDate = Me!ListOtherEmails.Column(1)
rs!txtCreatedTime = Me!ListOtherEmails.Column(2)
rs!txtFrom = Me!ListOtherEmails.Column(3)
rs!txtTo = Me!ListOtherEmails.Column(4)
rs!MemoContents = Me!ListOtherEmails.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
 
What is the name of the *field* (not text box)?

Perhaps it is "CreatedDate", in which case you would need:
rs!CreatedDate = Me!ListOtherEmails.Column(1)
 
What is the name of the *field* (not text box)?

Perhaps it is "CreatedDate", in which case you would need:
rs!CreatedDate = Me!ListOtherEmails.Column(1)
Click to expand...

Ahh!! That worked. Here is the code that now works.
BTW one of the fields was a Memo field and was not picked up by the ListBox, but I found a work
around and I've commented about it in the code.

I have been on that one for about 3 days. Thanks for the encouragement. I could not have done it
without your help.

Private Sub ListOtherEmails_DblClick(Cancel As Integer)

Dim frm As Form
Dim rs As DAO.Recordset
Set frm = Forms![frmInvoiceOrder] 'OK
Set rs = frm![zfrmInvoiceOrder].Form.RecordsetClone

rs.AddNew
With Me.ListOtherEmails
rs![InvoiceOrderID] = frm![InvoiceOrderID]
rs!Date = Me!ListOtherEmails.Column(1)
rs!Date = Me!ListOtherEmails.Column(2)
rs!From = Me!ListOtherEmails.Column(3)
rs!To = Me!ListOtherEmails.Column(4)
rs!Subject = Me!ListOtherEmails.Column(5)
rs!EbayNumber = Me!ListOtherEmails.Column(6)
'Contents is a Memo field and is not on the list box but can be copied straight from the
'page easy peasy
rs!Contents = Me!Contents
MsgBox "Done", _
vbInformation, "Successful"

End With
rs.Update

Set rs = Nothing
Set frm = Nothing

End Sub

Regards Smiley Bob
 
Back
Top