Help With Continuous Subform Containing Button

[Sue]

Hello,
I'm working with Access 2000
I have a continuous subform containing buttons that brings up an
additional information form not included on the continous subform. I have
managed to make this work in a past database, but can't remember how.

Currently when I click the button on the subform I get an enter parameter
value popup box.

The form is called Orders
The Subform is called MTsf
The form the button is supposed to open is AccessionDetails
MTsf and AccessionDetails are linked by the field MTAID
The subform (MTsf) and the main form (Orders) are lined by the field called
OrderID

I can get the button working on the subform if it's opened as a form (and
not a subform) but I can't get it to work on the a subform. Also if I fill in
the MTAID value into the enter parameter value popup it works fine.


The click command code I'm using is
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "AccessionDetails"

stLinkCriteria = "Forms![Orders]![MTsf].Form![MTAID]=" & Me![MTAID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

Any suggestions people have to fix this problem would be appreciated.

 

[Albert D.Kallal]

I can get the button working on the subform if it's opened as a form (and

not a subform) but I can't get it to work on the a subform. Also if I fill
in
the MTAID value into the enter parameter value popup it works fine.

The above is rather confusing!!!

are you saying that your buttion is in the sub-form?

Remember, that "me" always referes to the current form...even if it is in a
sub-form...or a sub-sub form!!!

if yes, then,
stDocName = "AccessionDetails"

stLinkCriteria = "MTAID = " & Me![MTAID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Remember, you are buildign a VALID sql where caluse without the word "where"
in the text. I would rather doubt that you have a field called

"Forms![Orders]![MTsf].Form![MTAID" (that would be strange! ;-)

So, you are simply building a standard sql where clause. You just need to
use an actual field name that you want the data to be restired to. That
field name does not even have to be on the target form (that field does
however have to be part of the underlying reocrdset).

Note that the above code example assume that MTAID is a number type field.
(check in design mode of the table...if it is text type field, then you need
to add quotes around the condition

eg:

MTAID = 'MYTEXT'

of couse, in code, you would go:

stLinkCriteria = "MTAID = '" & Me![MTAID] & "'"

So, if text, then surroued with quotes.....if number, you don't need to
surrud the value with quotes..

Good luck....

 

[Sue]

Hi,
Thanks. Yes the button is in the sub form.
I tried your suggestion and it didn't work.
I still get a enter parameter popup box... which I don't want... The button
on the subform should be taking the MTAID value from the subform and using
that to open the correct record in the Accession Details form.

Other suggestions?
Thanks.
-Sue

I can get the button working on the subform if it's opened as a form (and
not a subform) but I can't get it to work on the a subform. Also if I fill
in
the MTAID value into the enter parameter value popup it works fine.

The above is rather confusing!!!

are you saying that your buttion is in the sub-form?

Remember, that "me" always referes to the current form...even if it is in a
sub-form...or a sub-sub form!!!

if yes, then,
stDocName = "AccessionDetails"

stLinkCriteria = "MTAID = " & Me![MTAID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Remember, you are buildign a VALID sql where caluse without the word "where"
in the text. I would rather doubt that you have a field called

"Forms![Orders]![MTsf].Form![MTAID" (that would be strange! ;-)

So, you are simply building a standard sql where clause. You just need to
use an actual field name that you want the data to be restired to. That
field name does not even have to be on the target form (that field does
however have to be part of the underlying reocrdset).

Note that the above code example assume that MTAID is a number type field.
(check in design mode of the table...if it is text type field, then you need
to add quotes around the condition

eg:

MTAID = 'MYTEXT'

of couse, in code, you would go:

stLinkCriteria = "MTAID = '" & Me![MTAID] & "'"

So, if text, then surroued with quotes.....if number, you don't need to
surrud the value with quotes..

Good luck....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

Any suggestions people have to fix this problem would be appreciated.

 

[Ron2006]

What is the specific message on the popup box. That tells you exactly
what it is looking for. Does it agree 100% with the fully addressed
field name.

to get to that field name on that subform on that form it should read
something like

forms![Form1Name]![Subformname]!MTAID

or perhaps

forms![Form1Name]!Form![Subformname]!MTAID

 

[Albert D.Kallal]

Hi,
Thanks. Yes the button is in the sub form.
I tried your suggestion and it didn't work.
I still get a enter parameter popup box... which I don't want... The
button
on the subform should be taking the MTAID value from the subform and using
that to open the correct record in the Accession Details form.

remember, the where clause syntax is:

"FieldNameInTargetForm = some value in our current form"

So, do we have a field called MTAID in the Accession Details form? Remember
in the above "FieldNameInTargetForm" is simply a field name from the FORM
THAT WE ARE GOING to open!! (in your case AccessionDetails). That field name
does NOT need to exist in the current form, or is *often* a different name.
However, "some value" in the correct form is what we want to restrict MTAID
=.

Further, that "Accesson Details" form based on the table, or a query? If it
is based on a query, then it should have NO parameters in it....make it
clean sql.

When we use the "WHERE" clause feature of the openform command, you do NOT
want ANY parameters in the sql for the target form you are opening...

So, regardless of how many deep sub-forms the current form is, we assume
that:

We have a field called MTAID in this form (is the name the same...often,
here it is different). This is our "some value"

We also have a field call MTAID in the targeted form. This is our
"FieldNameInTargetFrom"

so, the syntax is:

"FieldNameInTargertForm = some vlaue"

The following code should work:

docmd.OpenForm "AccessionDetails",,"MTAID = " & ME!MTAID

As mentioned, the above syntax changes if MTAID is defined as text field,
and not a number field (by the way, you not pointed out which type of field
MTAID is.......which is it???).

Before you test, or try and of the above code, can you simply from the
"AccessionDetails" form by clicking on it from ms-access? When you open he
form from the ms-access UI, you should NOT get any prompts at all. If you
do, you MUST fix that first. Clean out that forms sql if it has any
parameters/prompts. Once you can simply open the AccessionDetails form
without any prompts from ms-access, then you can start testing the above
code....

 

[Sue]

Hi,
Thanks Ron and Albert for all your help.
I didn't have to write or change any new sql code, the problem was in the
Accession details form - I cleaned up all the sql and filters and weird
stuff, and the button on the sub-form worked perfectly.

One last question though, when I made the button it was using the wizard and
ditto with the sub form... is there a chance that the sql in the accession
details form is what added the weird filters and such? - The reason I ask is
because I don't recall creating any filters or sql code when I made that
particular form initially, and I'm wondering if it's something I need to be
wary of when using wizards?

Thanks.
-Sue

 

[Ron2006]

I guess my own standing rule is:

Wizards are too smart for our own good.

They think they know what we want and will operate accordingly. (for
instance a wizard for a combo box will always include the ID
[autonumber field] even when you try to stop it. If you are then trying
to use the "Distinct" option you have to always remember to take the ID
out)

So basically you have to learn what they do, and modify accordingly.

 

[Albert D.Kallal]

No, the wizard did not touch your filters, or put any garbage in your
sql....

 

[Ron2006]

What I am saying is that if you used the wizard to create your queries
(for instance in a combo box) it may or may not be what you really
wanted. And Albert is right, the wizard did not touch your query, but
some wizards will create their own filter.

Wizards do work, but they give you what the designer of the wizard
module thought was what you wanted.