Form - open subform code

[Guest]

Yesterday this code was opening a form from a button on a subform but
today...oh today! It is opening a new instance of the form......I want it to
open the form with the information I entered on the subform!

Private Sub cmdCollect_Click()

DoCmd.Save acForm, "frmFollow"

On Error GoTo Err_cmdCollect_Click

DoCmd.OpenForm "frmCollections", acNormal, , "pkClaimID = " & ClaimID

Exit_cmdCollect_Click:
Exit Sub

Err_cmdCollect_Click:
MsgBox Err.Description
Resume Exit_cmdCollect_Click

End Sub

 

[Marshall Barton]

Yesterday this code was opening a form from a button on a subform but
today...oh today! It is opening a new instance of the form......I want it to
open the form with the information I entered on the subform!

Private Sub cmdCollect_Click()

DoCmd.Save acForm, "frmFollow"

On Error GoTo Err_cmdCollect_Click

DoCmd.OpenForm "frmCollections", acNormal, , "pkClaimID = " & ClaimID

Exit_cmdCollect_Click:
Exit Sub

Err_cmdCollect_Click:
MsgBox Err.Description
Resume Exit_cmdCollect_Click
End Sub

If a form is already open, DoCmd.OpenForm just gives it the
focus. It does not open a second instance of the form.

I am very suspicious of your use of DoCmd.Save. This
statement saves the form's design, which is something that
you should never do in a running application. Its purpose
is intended for a situation where you want to create a form
design wizard for your own use, not for general users of
your application.

Perhaps you can provide a broarder context for your question
so we can help you get on the right track.

 

[Guest]

I know....i meant to take out that DoCmd.Save. I had it in when I was
mucking around with it alot and testing it, flicking back and forth between
design and open etc

Now...the DoCmd.Open is not to open another instance of the same form, it is
to open a completely different form (frmCollection) with some information
from a main form, some from subform (one with button) and some fields for
new data entry.

I can get it to work if there is already information in frmCollection, such
as, if I go to the table behind it and enter a value in any field, it will
relate all the records and then i can open it from a form. I think I need
something to force a value into a field (I don't care if it's an "x" into the
comments field) to relate the record and then open it.

Something like that....I think...but there is probably a better way. (and
you know it, don't you? Please? *please please know it*)

Cheers!

 

[Marshall Barton]

I am not clear on what you want here. The OpenForm method
opens the form, but you said there is something wrong.
Unfortunately, you haven't explained what is going wrong.
Aren't you seeing the form? You said something about it
working if there is an existing record, is the problem that
you don't want it to display a blank, new record ready for
data when there is no existing record? Or is it that you
want in some fields the new record to be created
automatically?

What are you doing to make it work when there is an existing
record? I would think that the same things would work for a
new record as well as on a new record, except for possibly
setting a foreign key, but I don't see how that can be the
problem when you can go into the table directly and enter
data into any field to make it work. Maybe you have set the
form's AllowAdditions property to No so it can not add any
new records???

Try to slow down a little and provide the details we need to
understand what you're trying to do along with how you've
attempted to do it. Remember that programming is an
extremely exacting science that requires specific details to
do correctly.

 

[Guest]

I'm really sorry Marshall....I know I go too fast for everyone IRL but didn't
know I could transfer that capacity to text!!!

I am not clear on what you want here. The OpenForm method
opens the form, but you said there is something wrong.
Unfortunately, you haven't explained what is going wrong.
Aren't you seeing the form?

I can see the form.

You said something about it

working if there is an existing record, is the problem that
you don't want it to display a blank, new record ready for
data when there is no existing record?

I don't want it to display a blank new record, I need it to open with the
existing record I am working on in another form.

Or is it that you

want in some fields the new record to be created
automatically?

I only said this because I thought it may help. it seems as if when there
is a value in another field already with something in it that the form will
open and be at the record I want.

What are you doing to make it work when there is an existing
record?

I go into the underlying table at the moment and put an x or a 1 or whatever
(just a character) into the Comments field.

I would think that the same things would work for a

new record as well as on a new record, except for possibly
setting a foreign key, but I don't see how that can be the
problem when you can go into the table directly and enter
data into any field to make it work. Maybe you have set the
form's AllowAdditions property to No so it can not add any
new records???

I will check this because I don't really know about the AllowAdditions
property.


This is the SQL code for the query that the form is based on:

SELECT tblContacts.pkCRDNumber, tblClaims.pkClaimID, tblContacts.Postcode,
tblContacts.Warehouse, tblContacts.Product, tblClaims.ReplacementOrder,
tblClaims.ReplacementReference, tblClaims.Collect,
tblReturns.KendalReturnDate, tblReturns.Comments,
tblReturns.ExpectedCollectionDate, tblReturns.PriorityCollection,
tblReturns.CollectAndReplace
FROM tblContacts INNER JOIN (tblClaims INNER JOIN tblReturns ON
tblClaims.pkClaimID = tblReturns.ClaimID) ON tblContacts.pkCRDNumber =
tblClaims.CRDNumber;

The current record exists in tblContacts and in tblClaims (joined one to
many on CRDNumber) however.....it does not yet exist in tblReturns, so I
currently create the record. Close my original form with subform
(frmFollow), go into tblReturns, enter a character somewhere, go back into
frmFollow, press my button on the subform and the correct record shows.

I want it to be able to do this but without obviously having to go into
tblReturns and enter a character.

Thank you....I really appreciate your help.

Cindy

Try to slow down a little and provide the details we need to
understand what you're trying to do along with how you've
attempted to do it. Remember that programming is an
extremely exacting science that requires specific details to
do correctly.
--
Marsh
MVP [MS Access]

Now...the DoCmd.Open is not to open another instance of the same form, it is
to open a completely different form (frmCollection) with some information
from a main form, some from subform (one with button) and some fields for
new data entry.

I can get it to work if there is already information in frmCollection, such
as, if I go to the table behind it and enter a value in any field, it will
relate all the records and then i can open it from a form. I think I need
something to force a value into a field (I don't care if it's an "x" into the
comments field) to relate the record and then open it.

Something like that....I think...but there is probably a better way. (and
you know it, don't you? Please? *please please know it*)

 

[Marshall Barton]

I'm really sorry Marshall....I know I go too fast for everyone IRL but didn't
know I could transfer that capacity to text!!!



I can see the form.

You said something about it

I don't want it to display a blank new record, I need it to open with the
existing record I am working on in another form.

Or is it that you

I only said this because I thought it may help. it seems as if when there
is a value in another field already with something in it that the form will
open and be at the record I want.

I go into the underlying table at the moment and put an x or a 1 or whatever
(just a character) into the Comments field.

I would think that the same things would work for a

I will check this because I don't really know about the AllowAdditions
property.


This is the SQL code for the query that the form is based on:

SELECT tblContacts.pkCRDNumber, tblClaims.pkClaimID, tblContacts.Postcode,
tblContacts.Warehouse, tblContacts.Product, tblClaims.ReplacementOrder,
tblClaims.ReplacementReference, tblClaims.Collect,
tblReturns.KendalReturnDate, tblReturns.Comments,
tblReturns.ExpectedCollectionDate, tblReturns.PriorityCollection,
tblReturns.CollectAndReplace
FROM tblContacts INNER JOIN (tblClaims INNER JOIN tblReturns ON
tblClaims.pkClaimID = tblReturns.ClaimID) ON tblContacts.pkCRDNumber =
tblClaims.CRDNumber;

The current record exists in tblContacts and in tblClaims (joined one to
many on CRDNumber) however.....it does not yet exist in tblReturns, so I
currently create the record. Close my original form with subform
(frmFollow), go into tblReturns, enter a character somewhere, go back into
frmFollow, press my button on the subform and the correct record shows.

I want it to be able to do this but without obviously having to go into
tblReturns and enter a character.

Ok, that really helps. I guess part of my confusion is that
we keep saying "the form" when there are two (or three?)
forms being discussed. In the future, let's refer to a form
as "the collections form" or "the contacts form". I think
there is also a claims subform on the contacts form.

Is the query you posted above the record source for the
contacts form or the collections form?

I don't see how the query could possibly be the collections
form's record source because it does not include the primary
key field in tblReturns.

On the other hand, I don't see how it could work for the
contacts form either. Because of the INNER JOINs, if there
is no existing record in tblReturns (or tblClaims), the form
will not see the contacts record you want. If this is the
problem we are struggling with, then change the INNER JOINs
to LEFT JOINs. However, I don't see why the contacts form
should be retrieving data from the the other two tables, so
it's more likely that the Joins and extraneous fields should
just be removed from the query.