help with VB DoDmd.OpenForm WhereCondition

[rik]

I am trying to build an Access 2002/2003 database application that I
would like to offer to other writers so they can track submissions.
Just trying to make a helpful tool for writers, poets, or anyone who
submits anything to anywhere, actually... I'm having trouble with the
Visual Basic code for the on click event of a button.

So, I have a form, FormA, whose source is a query. On FormA, there is
a numeric field called FieldA. FieldA corresponds to the primary key
(numeric) of the source table for FormB. On FormB, that same related
field is called FieldB.

I have a button on FormA called ButtonA. When clicked, ButtonA should
open FormB to the record in FormB where FieldB is the same as the
value of FieldA on the record where ButtonA was clicked.

The code that is not working is:
_________________________________________________
Private Sub ButtonA_Click()
CoCmd.OpenForm "FormB",,, FormB.FieldB = FormA.FieldA
End Sub
__________________________________________________

With this, I get Run-time error '424'. Object Required.

I read that "the WhereCondition is just an sql where statement without
the WHERE", but apparently, there is some other syntax or structure I
am missing. Any help greatly appreciated. Thanks!

 

[rik]

Thanks for the suggestion Steve. But that was only able to open FormB,
not showing the corresponding record. Actually the form opens filtered
to a single empty record...

 

[Marshall Barton]

I am trying to build an Access 2002/2003 database application that I
would like to offer to other writers so they can track submissions.
Just trying to make a helpful tool for writers, poets, or anyone who
submits anything to anywhere, actually... I'm having trouble with the
Visual Basic code for the on click event of a button.

So, I have a form, FormA, whose source is a query. On FormA, there is
a numeric field called FieldA. FieldA corresponds to the primary key
(numeric) of the source table for FormB. On FormB, that same related
field is called FieldB.

I have a button on FormA called ButtonA. When clicked, ButtonA should
open FormB to the record in FormB where FieldB is the same as the
value of FieldA on the record where ButtonA was clicked.

The code that is not working is:
_________________________________________________
Private Sub ButtonA_Click()
CoCmd.OpenForm "FormB",,, FormB.FieldB = FormA.FieldA
End Sub
__________________________________________________

With this, I get Run-time error '424'. Object Required.

I read that "the WhereCondition is just an sql where statement without
the WHERE", but apparently, there is some other syntax or structure I
am missing. Any help greatly appreciated. Thanks!

The WhereCondition argument is a string, not an expression.
The string usually contains an expression, but you need to
enclose it in quotes and/or concatenate values that results
in a string. Your FormB.FieldB has invalid syntax and is
not in quotes. In addition, you need to refer to **fields**
in FormB's record source table/query, not to controls on the
form.

Assuming that FieldA is a numeric type field, use:

DoCmd.OpenForm "FormB",,, "FieldB = " & Me.FieldA

If FieldA is a Text field, then use:

DoCmd.OpenForm "FormB",,, "FieldB = """ & Me.FieldA & """"

If FieldA is a Date field, then use:

DoCmd.OpenForm "FormB",,, "FieldB = " _
& Format(Me.FieldA, "\#yyyy-m-d\#")

 

[fredg]

I am trying to build an Access 2002/2003 database application that I
would like to offer to other writers so they can track submissions.
Just trying to make a helpful tool for writers, poets, or anyone who
submits anything to anywhere, actually... I'm having trouble with the
Visual Basic code for the on click event of a button.

So, I have a form, FormA, whose source is a query. On FormA, there is
a numeric field called FieldA. FieldA corresponds to the primary key
(numeric) of the source table for FormB. On FormB, that same related
field is called FieldB.

I have a button on FormA called ButtonA. When clicked, ButtonA should
open FormB to the record in FormB where FieldB is the same as the
value of FieldA on the record where ButtonA was clicked.

The code that is not working is:
_________________________________________________
Private Sub ButtonA_Click()
CoCmd.OpenForm "FormB",,, FormB.FieldB = FormA.FieldA
End Sub
__________________________________________________

With this, I get Run-time error '424'. Object Required.

I read that "the WhereCondition is just an sql where statement without
the WHERE", but apparently, there is some other syntax or structure I
am missing. Any help greatly appreciated. Thanks!

[FieldA] is the name of the FIELD in the table/query that you wish to
use to filter records.
[ControlB] is the name of the CONTROL on the form that you are using
to compare values to.

DoCmd.OpenForm "FormB", , , "[FieldA] = " & Me![ControlB]

In other words, open FormB filtered, so that the table/query [FieldA]
value equals the value displayed on the current Form's [ControlB] .

The above syntax is correct if the datatype of [FieldA] is Number
datatype.

See Access help on
Where Clause + Restrict data to a subset of records
for more information as to the proper syntax for other datatypes.