Form button question..I think??

[Scott Zierler]

Hi Everyone,

I have a form with a Combo box named cboChurchName. The user can change the
value in the combo box (which is bound to a table). Below the combo box
there is a button named Church Info. Currently the Church Info command
button opens a form generated from a query named ChurchQuery. Here's what I
need. Depending on what Church is selected in cboChurchName combo box, I
would like the form that opens when the user clicks on the Church Info, to
be information relative to the combobox value only. Hope this makes sense.

Thanks so much!

 

[Beetle]

Piece of Cake. Piece of Crumb Cake.

Your cboChurchName control should have a "bound field". Maybe it's the
Church ID#, maybe it's the Church Name, I don't know. Your query should have
a field that is the same as the bound field of cboChurchName. Open your query
in design view, and in the criteria row for this field put;

Forms!NameOfYourFirstForm![cboChurchName]

HTH

 

[Rob Parker]

Hi Scott,

All you need to do is add a Where parameter to the DoCmd.OpenForm statement.
It will need to be something like:
DoCmd.OpenForm "NameOfChurchInfoForm", , , "ChurchName = '" &
cboChurchName & "'"

If your combobox is actually bound to a ChurchID number, rather than the
ChurchName field, then you'll need something like:
DoCmd.OpenForm "NameOfChurchInfoForm", , , "ChurchID = " & cboChurchName

Change the form name and field names as appropriate.

HTH,

Rob

 

[Klatuu]

small problem there, Beetle. If you use a combo for a lookup that is bound
to a field in the form's recordset it will try to change value of field it is
bound to in the current record. If it is an index field requiring unique
values, it will error out. Even if it is not required to be unique, the
value will change. I doubt this is what the OP wants.

Perhaps you did not mean it is a bound control (identified by a field in the
recordset in the controly source), but a bound column in row source of the
combo.

--
Dave Hargis, Microsoft Access MVP

Piece of Cake. Piece of Crumb Cake.

Your cboChurchName control should have a "bound field". Maybe it's the
Church ID#, maybe it's the Church Name, I don't know. Your query should have
a field that is the same as the bound field of cboChurchName. Open your query
in design view, and in the criteria row for this field put;

Forms!NameOfYourFirstForm![cboChurchName]

HTH

Hi Everyone,

I have a form with a Combo box named cboChurchName. The user can change the
value in the combo box (which is bound to a table). Below the combo box
there is a button named Church Info. Currently the Church Info command
button opens a form generated from a query named ChurchQuery. Here's what I
need. Depending on what Church is selected in cboChurchName combo box, I
would like the form that opens when the user clicks on the Church Info, to
be information relative to the combobox value only. Hope this makes sense.

Thanks so much!

 

[Beetle]

You are right Dave. I was thinking of an unbound combo box and it's
associated bound column. After reading the OP again, I notice that he did say
the combo is bound to a field in the table, so my solution was wrong. Thanks
for catching that. I officially retract my "Piece of Cake" declaration.

small problem there, Beetle. If you use a combo for a lookup that is bound
to a field in the form's recordset it will try to change value of field it is
bound to in the current record. If it is an index field requiring unique
values, it will error out. Even if it is not required to be unique, the
value will change. I doubt this is what the OP wants.

Perhaps you did not mean it is a bound control (identified by a field in the
recordset in the controly source), but a bound column in row source of the
combo.

--
Dave Hargis, Microsoft Access MVP

Piece of Cake. Piece of Crumb Cake.

Your cboChurchName control should have a "bound field". Maybe it's the
Church ID#, maybe it's the Church Name, I don't know. Your query should have
a field that is the same as the bound field of cboChurchName. Open your query
in design view, and in the criteria row for this field put;

Forms!NameOfYourFirstForm![cboChurchName]

HTH

Hi Everyone,

I have a form with a Combo box named cboChurchName. The user can change the
value in the combo box (which is bound to a table). Below the combo box
there is a button named Church Info. Currently the Church Info command
button opens a form generated from a query named ChurchQuery. Here's what I
need. Depending on what Church is selected in cboChurchName combo box, I
would like the form that opens when the user clicks on the Church Info, to
be information relative to the combobox value only. Hope this makes sense.

Thanks so much!

 

[Klatuu]

Great,
Please don't think I am beating up on you, Beetle. We are all here to help
each other and make sure the right info gets posted.
Believe me, I have been on the other end plenty.
Doug Steele has corrected me more times than I care to remember. I consider
him one of the most knowledgeable here and, although at times embarrased, I
have learned a lot from him.

--
Dave Hargis, Microsoft Access MVP

You are right Dave. I was thinking of an unbound combo box and it's
associated bound column. After reading the OP again, I notice that he did say
the combo is bound to a field in the table, so my solution was wrong. Thanks
for catching that. I officially retract my "Piece of Cake" declaration.

small problem there, Beetle. If you use a combo for a lookup that is bound
to a field in the form's recordset it will try to change value of field it is
bound to in the current record. If it is an index field requiring unique
values, it will error out. Even if it is not required to be unique, the
value will change. I doubt this is what the OP wants.

Perhaps you did not mean it is a bound control (identified by a field in the
recordset in the controly source), but a bound column in row source of the
combo.

--
Dave Hargis, Microsoft Access MVP

Piece of Cake. Piece of Crumb Cake.

Your cboChurchName control should have a "bound field". Maybe it's the
Church ID#, maybe it's the Church Name, I don't know. Your query should have
a field that is the same as the bound field of cboChurchName. Open your query
in design view, and in the criteria row for this field put;

Forms!NameOfYourFirstForm![cboChurchName]

HTH

:

Hi Everyone,

I have a form with a Combo box named cboChurchName. The user can change the
value in the combo box (which is bound to a table). Below the combo box
there is a button named Church Info. Currently the Church Info command
button opens a form generated from a query named ChurchQuery. Here's what I
need. Depending on what Church is selected in cboChurchName combo box, I
would like the form that opens when the user clicks on the Church Info, to
be information relative to the combobox value only. Hope this makes sense.

Thanks so much!

 

[Beetle]

Actually, I appreciate your correcting me Dave. First, because I learned
something (in this case it was to pay more attention to the question and my
answer) and second, because I wouldn't want Scott, or anyone else, pulling
their hair out for a couple of extra hours because I gave them bad advice
(hopefully that didn't already happen).

Thanks again

Great,
Please don't think I am beating up on you, Beetle. We are all here to help
each other and make sure the right info gets posted.
Believe me, I have been on the other end plenty.
Doug Steele has corrected me more times than I care to remember. I consider
him one of the most knowledgeable here and, although at times embarrased, I
have learned a lot from him.

--
Dave Hargis, Microsoft Access MVP

You are right Dave. I was thinking of an unbound combo box and it's
associated bound column. After reading the OP again, I notice that he did say
the combo is bound to a field in the table, so my solution was wrong. Thanks
for catching that. I officially retract my "Piece of Cake" declaration.

small problem there, Beetle. If you use a combo for a lookup that is bound
to a field in the form's recordset it will try to change value of field it is
bound to in the current record. If it is an index field requiring unique
values, it will error out. Even if it is not required to be unique, the
value will change. I doubt this is what the OP wants.

Perhaps you did not mean it is a bound control (identified by a field in the
recordset in the controly source), but a bound column in row source of the
combo.

--
Dave Hargis, Microsoft Access MVP


:

Piece of Cake. Piece of Crumb Cake.

Your cboChurchName control should have a "bound field". Maybe it's the
Church ID#, maybe it's the Church Name, I don't know. Your query should have
a field that is the same as the bound field of cboChurchName. Open your query
in design view, and in the criteria row for this field put;

Forms!NameOfYourFirstForm![cboChurchName]

HTH

:

Hi Everyone,

I have a form with a Combo box named cboChurchName. The user can change the
value in the combo box (which is bound to a table). Below the combo box
there is a button named Church Info. Currently the Church Info command
button opens a form generated from a query named ChurchQuery. Here's what I
need. Depending on what Church is selected in cboChurchName combo box, I
would like the form that opens when the user clicks on the Church Info, to
be information relative to the combobox value only. Hope this makes sense.

Thanks so much!

 

[Scott Zierler]

Hi Rob,

Thanks for pointing me in the right direction. Here is code that I am
using...

Private Sub btnChurchInfo_Click()
On Error GoTo Err_btnChurchInfo_Click

DoCmd.OpenForm "Churchquery", , , "Church Affiliation=" &
cboChurchAffiliation

Exit_btnChurchInfo_Click:
Exit Sub

Err_btnChurchInfo_Click:
MsgBox Err.Description
Resume Exit_btnChurchInfo_Click

End Sub

When I click on the command button I get an error message.
Syntax error (missing operator) in query expression 'Church
Affiliation=First Nazarene'.

The First Nazarene name is from the combo-box so I know it's reading the
current value, but I can't get the syntax right.

I have a feeling it's something small

Thanks for your help Rob

 

[Rob Parker]

Hi Scott,

You are missing the delimiters needed around the string value being returned
from the combo-box. Try:
DoCmd.OpenForm "Churchquery", , , "Church Affiliation='" &
cboChurchAffiliation & "'"

For clarity, that's:
DoCmd.OpenForm "Churchquery", , , "Church Affiliation= ' " &
cboChurchAffiliation & " ' "

If the value returned from cboChurchAffiliation may contain an apostrophe,
use a pair of double-quote characters as the delimiter:
DoCmd.OpenForm "Churchquery", , , "Church Affiliation=""" &
cboChurchAffiliation & """"

HTH,

Rob

 

[Scott Zierler]

Hi again Rob,

I modified the code as you mentioned and I still get the error message. I
think it is worth mentioning that the error message doesn't show the " quote
characters before "Church Affiliation='" in the message.

Here is the code as it is written:

Private Sub btnChurchInfo_Click()
On Error GoTo Err_btnChurchInfo_Click

DoCmd.OpenForm "Churchquery", , , "Church Affiliation= '" &
cboChurchAffiliation & "'"

Exit_btnChurchInfo_Click:
Exit Sub

Err_btnChurchInfo_Click:
MsgBox Err.Description
Resume Exit_btnChurchInfo_Click

End Sub


Thanks so much!

Scott

 

[Rob Parker]

Sorry Scott,

I missed another obvious error! Since your field name contains a space
(highly non-recommended practice), you need to enclose that in square
brackets. So what you need is:
DoCmd.OpenForm "Churchquery", , , "[Church Affiliation] = '" &
cboChurchAffiliation & "'"

HTH,

Rob