Forms

[Guest]

I have a form with different customers (sorted by customerID) and a command
button to open another form to input some data for a specific customer.
Due to the lack of space I cannot use the second form as a subform in the
first form.
I would like, even if the second form is now open seperately, to somehow
link the child field "customerID" (from the second form) with the master
field "customerID" (from the main form) - so as it would be a subform.
Any idea if, or how I can do that?
Thanks
Klaus

 

[Guest]

You just want to addsome link criteria

stLinkCriteria = "[customerID]=" & "'" & [Forms]![MainFormName]![customerID]
& "'"
stDocName = "OtherFormName"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Of course you would change the form names to your form name.

HTH
Bryan..

 

[Rick Brandt]

I have a form with different customers (sorted by customerID) and a
command button to open another form to input some data for a specific
customer. Due to the lack of space I cannot use the second form as a
subform in the first form.
I would like, even if the second form is now open seperately, to
somehow link the child field "customerID" (from the second form) with
the master field "customerID" (from the main form) - so as it would
be a subform. Any idea if, or how I can do that?
Thanks
Klaus

Option 1)
Use a TabControl on your existing form eliminating the "lack of space" issue
and allowing you to use a *real* subform.

Option 2)
Set the DefaultValue property on the second form to something like...

=Forms!NameOfFirstForm!customerID

Option 3)
Pass the customerID in the OpenArgs argument of the OpenForm method. The
second form can then grab the value from its OpenArgs property and use it in
the BeforeInsert or BeforeUpdate event.

 

[Guest]

I have now the following VB code and I get the following result: "The
OpenForm action was canceled"
Can you tell me where my mistake is ?
Thanks
Klaus

Private Sub Command96_Click()
On Error GoTo Err_Command96_Click

stLinkCriteria = "[clientID]=" & "'" & [Forms]![customers]![clientID] &
"'"
stDocName = "offer"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command96_Click:
Exit Sub

Err_Command96_Click:
MsgBox Err.Description
Resume Exit_Command96_Click

End Sub

You just want to addsome link criteria

stLinkCriteria = "[customerID]=" & "'" & [Forms]![MainFormName]![customerID]
& "'"
stDocName = "OtherFormName"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Of course you would change the form names to your form name.

HTH
Bryan..

I have a form with different customers (sorted by customerID) and a command
button to open another form to input some data for a specific customer.
Due to the lack of space I cannot use the second form as a subform in the
first form.
I would like, even if the second form is now open seperately, to somehow
link the child field "customerID" (from the second form) with the master
field "customerID" (from the main form) - so as it would be a subform.
Any idea if, or how I can do that?
Thanks
Klaus

 

[Guest]

Your second idea seems to me the easiest one but I cannot find the
"DefaultValue property" in my forms property. I found "Default view" and that
is it - where can I find the "DefaultValue property".
Thanks
Klaus

 

[Guest]

Have found it but it's not so working as I thought. I'll work further on
Bryan's proposal.
Thanks anyway.
Klaus

 

[Rick Brandt]

Have found it but it's not so working as I thought. I'll work further
on Bryan's proposal.
Thanks anyway.
Klaus

Bryan's suggestion will filter the second form so that EXISTING records matching
the record on the first form will be shown, but it will do nothing about NEW
records you create.

 

[Guest]

Hi Rick
thanks for your response.
Anyway, I think I did not understand Bryans proposal neither because I
cannot get it working.
Maybe, if you have time, have a look what I wrote to Bryan - maybe you can
tell me what is wrong?
Thanks
Klaus

 

[Guest]

Hi Rick
I am still lost and went back to your ideas.
Your idea Nº 2
I wrote into the Textbox of the second form as a Default value
=[Forms]![customers]![clientID] (the brackets came automatically)

Result: Nothing changed I still have the same output as before.

Your idea Nº 3:
Can you be as kind as to explain to me on a step-to-step basis hoe I can do
that?

Thanks
Klaus

 

[Rick Brandt]

Hi Rick
I am still lost and went back to your ideas.
Your idea Nº 2
I wrote into the Textbox of the second form as a Default value
=[Forms]![customers]![clientID] (the brackets came automatically)

Result: Nothing changed I still have the same output as before.

Explain "same output as before". What should happen is if you open the
second form while the first form is opened AND navigate to a new record on
the second form you should see that default value being populated with the
value of the ClientID on the first form.

Is that what you see? If not what do you see?

Your idea Nº 3:
Can you be as kind as to explain to me on a step-to-step basis hoe I
can do that?

The final argument of the OpenForm method of the DoCmd object is named
"OpenArgs". Any value you supply to that argument will be passed to the
form being opened as a String and will be stored in the OpenArgs property of
that form. In your case...

DoCmd.OpenForm "SecondFormName",,,,,,Me.ClientID

If the ClientID value was 123456 then code in the second form will be able
to use that like...

Me.ClientID = Me.OpenArgs

You can decide which event would be most appropriate to run that code, but
it would need to happen in an event that will run for each record you create
in the second form and some time before each record is saved. BeforeInsert,
BeforeUpdate would be two valid choices. You could also use the Open or
Load evetn to make that the default value...

Me.ClientID.DefaultValue = """ & Me.OpenArgs & """

Both the use of OpenArgs and setting the default value to
Forms!NameOfFirstForm!ClientID would require that the second from is never
used unless the first form is opened although the OpenArgs strategy could
use code that checks for that and adapts as appropriate.

 

[Guest]

Idea Nº 2:
What should happen?

-My first form is "customers"
-My second form is "offer"

On the customer form is a command button linking to the offer form.

-In this offer form I will input the products for the offer for that
specific customer.

I would like that, if I open the offer form, with the command button on the
customer form, all inputs of products will be ONLY for that specific customer.

A bid easier, customer number 1 = open offer for customer Nº 1 - I should
not be able to write an offer in the offer form for customer Nº 1, using
customer Nº 2.

Puhh, I hope that I explained it so that you can understand; and so that you
can help me.

Thanks
Klaus

Hi Rick
I am still lost and went back to your ideas.
Your idea Nº 2
I wrote into the Textbox of the second form as a Default value
=[Forms]![customers]![clientID] (the brackets came automatically)

Result: Nothing changed I still have the same output as before.

Explain "same output as before". What should happen is if you open the
second form while the first form is opened AND navigate to a new record on
the second form you should see that default value being populated with the
value of the ClientID on the first form.

Is that what you see? If not what do you see?

Your idea Nº 3:
Can you be as kind as to explain to me on a step-to-step basis hoe I
can do that?

The final argument of the OpenForm method of the DoCmd object is named
"OpenArgs". Any value you supply to that argument will be passed to the
form being opened as a String and will be stored in the OpenArgs property of
that form. In your case...

DoCmd.OpenForm "SecondFormName",,,,,,Me.ClientID

If the ClientID value was 123456 then code in the second form will be able
to use that like...

Me.ClientID = Me.OpenArgs

You can decide which event would be most appropriate to run that code, but
it would need to happen in an event that will run for each record you create
in the second form and some time before each record is saved. BeforeInsert,
BeforeUpdate would be two valid choices. You could also use the Open or
Load evetn to make that the default value...

Me.ClientID.DefaultValue = """ & Me.OpenArgs & """

Both the use of OpenArgs and setting the default value to
Forms!NameOfFirstForm!ClientID would require that the second from is never
used unless the first form is opened although the OpenArgs strategy could
use code that checks for that and adapts as appropriate.

 

[Rick Brandt]

Idea Nº 2:
What should happen?

-My first form is "customers"
-My second form is "offer"

On the customer form is a command button linking to the offer form.

-In this offer form I will input the products for the offer for that
specific customer.

I would like that, if I open the offer form, with the command button
on the customer form, all inputs of products will be ONLY for that
specific customer.

A bid easier, customer number 1 = open offer for customer Nº 1 - I
should
not be able to write an offer in the offer form for customer Nº 1,
using customer Nº 2.

Puhh, I hope that I explained it so that you can understand; and so
that you can help me.

Thanks
Klaus

Then you need both what I suggested and what Bryan suggested. What he
suggested will open the offer form filtered on the existing records for the
customer currently showing on the customers form. What I suggested will
take care of any NEW records you might create in the offer form after you
open it.

 

[Guest]

OK Rick, now I did the following:
I have two forms:
1) customers
2) offer

to 1)
I opened the customers Form "open Form" Command button
I entered in the property of the "open Form" command butten under the
onClick event :
Private Sub Command96_Click()
On Error GoTo Err_Command96_Click

stLinkCriteria = "clientID = " & Me.clientID
stDocName = "offer"
DoCmd.OpenForm "offer",,,,,,Me.clientID

Exit_Command96_Click:
Exit Sub

Err_Command96_Click:
MsgBox Err.Description
Resume Exit_Command96_Click

End Sub


to 2)
a) I opened the offer form in design view and entered in the combo box for
clientID the following default value:
=[Forms]![customers]![clientID]
b) I entered in the offer Form property On Load event procedure:
Private Sub Form_Load()
Me.clientID.DefaultValue = """ & Me.OpenArgs & """
End Sub

**********************
Result: The offer form is opening like always and not customerspecific.
Can you tell me where my mistake is?
Thanks
Klaus