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!

[Steve]

Try ....
CoCmd.OpenForm "FormB",,, "Forms!FormB.FieldB = " & Me.FieldA

Steve

"rik" <ef*********@gmail.comwrote in message
news:1b**********************************@q35g2000 hsg.googlegroups.com...

>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!

[Allen Browne]

You need quotes. The WhereCondition is a string.

Ultimately you need it to read something like this:
FieldB = 999
so code like this:

Private Sub ButtonA_Click()
Dim strWhere as String
If Me.Dirty Then Me.Dirty = False
If IsNull(Me.FieldA) Then
MsgBox "There's no FieldA value."
Else
strWhere = "FieldB = " & Me.FieldA
DoCmd.OpenForm "FormB", WhereCondition:=strWhere
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rik" <ef*********@gmail.comwrote in message
news:1b**********************************@q35g2000 hsg.googlegroups.com...

>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...
On Oct 14, 10:28*am, "Steve" <[e-mail]:

Try ....
CoCmd.OpenForm "FormB",,, "Forms!FormB.FieldB = " & Me.FieldA

Steve

"rik" <[e-mail] in message

news:1b**********************************@q35g2000 hsg.googlegroups.com...

[Marshall Barton]

rik wrote:

>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\#")

--
Marsh

[fredg]

On Tue, 14 Oct 2008 08:20:56 -0700 (PDT), rik wrote:

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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[rik]

Allen, your code worked Perfectly! Thank you so much. You are really
awesome. I'll try to learn VB myself one of these days.
On Oct 14, 10:31*am, "Allen Browne" <[e-mail]>
wrote:

You need quotes. The WhereCondition is a string.

Ultimately you need it to read something like this:
* * FieldB = 999
so code like this:

* * Private Sub ButtonA_Click()
* * * * Dim strWhere as String
* * * * If Me.Dirty Then Me.Dirty = False
* * * * If IsNull(Me.FieldA) Then
* * * * * * MsgBox "There's no FieldA value."
* * * * Else
* * * * * * strWhere = "FieldB = " & Me.FieldA
* * * * * * DoCmd.OpenForm "FormB", WhereCondition:=strWhere
* * * * End If
* * End Sub

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[rik]

Wow. So many responses before I can even reply with success. I'll read
though the others and try to learn from them too. Thanks to everyone
again. I'll try to remember to post a link to the database here once
it's whipped into shape.