Form question (continuation)

[Guest]

Dear Sirs
Herewith I would like to specify my yesterdays question.
I have a customer form with a command button "open offer form". I would like
that the offer form which opens, only shows the records from from the
specific customer. This I would like to archive with the "event on click"
method. I have the following VB code:

Private Sub Command96_Click()
On Error GoTo Err_Command96_Click

Dim stDocname As String
Dim stLinkCriteria As String
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

I'll get the following error message:
"The OpenForm action was cancelled".
Can someone tell me how to alter the code so that it is working?
Thanks
Klaus
P.S. Sorry that I specified a yesterday placed question, but I thought the
other thread is already too confusing

 

[Albert D. Kallal]

Is the field clientID a text field, or a number type file.

what you have looks correct, but if clientID is a number field, the you
don't need the quotes.

Check the table in design mode, and be 100% sure as to what kind of field
the clientID is.

If clientID is a number field, then go:

stLinkCriteria = "clientID = " & me!ClientID

If clientID is a text type field, then go:

stLinkCriteria = "clientID = '" & me!ClientID & "'"

 

[Guest]

The field clientID is an "autonumber field".

I changed my code to the following:

Private Sub Command96_Click()
On Error GoTo Err_Command96_Click

Dim stDocname As String
Dim stLinkCriteria As String
stLinkCriteria = "clientID = " & Me!clientID
stDocName = "offer"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command96_Click:
Exit Sub

Err_Command96_Click:
MsgBox Err.Description
Resume Exit_Command96_Click

End Sub

If I open now from "customer Nº 1", with this open form method the offer
form I see the offer form for customer number 1.
If I open now from "customer Nº 2", with this open form method the offer
form I see the offer form for customer number 1 - BUT it should be for
customer Nº 2

Any idea?
Thanks
Klaus

 

[Albert D. Kallal]

When you run this code, you have to make sure that the "other" form is
closed.....

That means you should make the "other" form model.

Try running the code, and make sure the "other" form is closed....


I not really sure that "other is a very good name for a form....

 

[Guest]

Dear Albert

I did not speak from "other" form.

I only have two forms:
- customers form
- offer form

I do not understand if you say: The other (I believe you ment "offer" form)
has to be closed. And what do you mean with: "That means you should make the
"other" form model" - how do I do that?

I think it is closed when I don't see it (or do I have to do something else?)
Please be as kind as to expalin again.
Thanks
Klaus

 

[Albert D. Kallal]

I did not speak from "other" form.

Ah...yes, I read wrong. sorry, my appoligws.

I do not understand if you say: The other (I believe you ment "offer"
form)
has to be closed. And what do you mean with: "That means you should make
the
"other" form model" - how do I do that?

I think it is closed when I don't see it (or do I have to do something
else?)
Please be as kind as to expalin again.

You can set the "offer" form as model when in desing mode for that form.

Open up the properites sheet for the offer form, and in the other tab, there
is a setting for the model

However, from your desctipo of things, it sounds like the form is closed.

we have:

stLinkCriteria = "clientID = " & Me!clientID

In the above, the field clientID must be present in the form "offer" table.

me!ClientID

The above field must be available in our continues form

So,

a) in your continues form, do you have a field called clientID

b) in the target form (form offer), do you have a field called clientID

 

[Guest]

Yes, in both forms I have a field called clientID.
Any more ideas?

Now I tried as well the following (maybe you can help on this):

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

 

[Rick Brandt]

Yes, in both forms I have a field called clientID.
Any more ideas?

Now I tried as well the following (maybe you can help on this):

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

This line above is not using either stLinkCriteria or stDocName. It is also
not supplying any value for the WHERE clause argument which is required if
you want the "offer" form to open filtered.

You ARE supplying a value for the OpenArgs argument, but that does not
filter the form being opened. It just passes that value so the offer form
can make use of the value in its own code.

Those last three lines above should be...

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

 

[Guest]

I changed the code for the command button on click property into.

Private Sub Command96_Click()
On Error GoTo Err_Command96_Click

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

Exit_Command96_Click:
Exit Sub

Err_Command96_Click:
MsgBox Err.Description
Resume Exit_Command96_Click

End Sub

*******************+
Result:
Offer form is still not opening clientspecific.
Did I do the rest correct which I explained in my earlier post?
Thanks
Klaus

 

[Albert D. Kallal]

The button has to be in the same form as the continues form. Is this a sub
form?

Also, reduce your code to the following in the code behind the buttion


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

That is ONE LINE of code. You DO NOT NEED MORE THEN THE one line of code.

Before you run the above code, go to the menu and go

debug->compile

does your code compile?

 

[Guest]

OK, It seems to work, except, with your line I get before the offer form is
opening the "Enter parameter value" question: "offerquery.orderID".
Why is that and how can I change that this question is not coming anymore?
Thanks
Klaus

 

[Albert D. Kallal]

OK, It seems to work, except, with your line I get before the offer form
is
opening the "Enter parameter value" question: "offerquery.orderID".
Why is that and how can I change that this question is not coming anymore?

Can you open that query the query builder (just double click on it), and
does the query run?

The query should run without any prompts..and simply display data. Get this
query working.

Now, after you sure the query us working, we want to try opening the form
called offer.

Can you just click on this form offer, and does it cleanly open? (again, we
not using code here yet).

does the form offer open without ay prompts? (we need to get rid of those
prompts before we attempt to use code to open the form.

if you can't just click on a form to open it, then we WAY too soon to try
this in code....

So, if the form is prompting you, lets get that working, and THEN TRY OUR
code....

Also, in my example there is a type-o

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

The above should read

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

If the form is NOT prompting you when you click on it, then please pose the
ONE LINE OF CODE that you have now.
:

 

[Guest]

Hi Albert

The query is opening without problems.
If I open the offer form alone, it's opening without problems.
If I open the offer form through the customer form I get the prompt. (If I
do not write anything in the prompt, so, just click it, the offer forms opens
correct)
Also your example I corrected before.
I have the following code:

Private Sub Command96_Click()
On Error GoTo Err_Command96_Click
Dim stDocName As String
Dim stLinkCriteria As String

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

Exit_Command96_Click:
Exit Sub

Err_Command96_Click:
MsgBox Err.Description
Resume Exit_Command96_Click

End Sub

Any ideas?
Thanks
Klaus