472,981 Members | 1,462 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,981 software developers and data experts.

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 "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
Nov 13 '05 #1
5 3328
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bob" <sm*******@hotmail.com> wrote in message
news:ps********************************@4ax.com...

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

Nov 13 '05 #2
Bob
On Thu, 10 Jun 2004 10:11:30 +0800, "Allen Browne" <Al*********@SeeSig.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]![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.
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(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

Nov 13 '05 #3
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bob" <sm*******@hotmail.com> wrote in message
news:it********************************@4ax.com...
On Thu, 10 Jun 2004 10:11:30 +0800, "Allen Browne" <Al*********@SeeSig.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]![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 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(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

Nov 13 '05 #4
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(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

Nov 13 '05 #5
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)

--
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*******@hotmail.com> wrote in message
news:ek********************************@4ax.com...

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

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: kimj.dk | last post by:
Hi, I have a form with a subform where the subform uses the value from a combo box on the main form as a query criteria. I don't want the subform to update or get data before the user has selected...
18
by: Robert Jacobs | last post by:
Please advise... I currently have a Mainform (Customers) and a Subform (Service Requests) with a one to many relationship (one customer, many service requests) with a CustomerID that is unique...
4
by: rczuba | last post by:
Problem: Creating a Default Value for a field in a subform when a field in the subform & form match. I'm trying to create a payroll database for a small home business that I have that has had...
1
by: Rosy | last post by:
I have a form "A" that is set on table vessel names. I have subform "B" that is set on table vessel info. I want form "B" to update based on what vessel I choose in form "A". I know this is...
2
by: angi35 | last post by:
I hope this is an easy question for someone out there. In Access 2000I have a MainForm with a tab control (MAIN TABS) with 7 tabs. Within each tab is a SubForm. Within each SubForm is a tab...
1
by: Crombam | last post by:
Hi to all, After searching the net for a couple of hours I just have to ask the question. How can I close a report with a subform (Pivot Chart) when this Pivot Chart is having no data? ...
3
by: gblack301 | last post by:
Hi All, I have been racking my brain for sometime in trying to resolve this and now I am just wiped out. MS-Access 2003 I have a main form that contains several selection options (combo...
7
by: JohnHo | last post by:
I have been working with Access for a little while but have never used mainforms/subforms before. Here is my problem/scenario: I have a main form (frmMain) with a few other forms for data data...
8
by: Erik Pen | last post by:
My main form has several subforms which display the results of queries. I want to display a message in a subform when it's query is empty indicating that there are no records meeting the form's...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.