Access 2002 Conditional Caption

I have a form that returns 100 items. All of the items have have a
different value in their Category field.

EXAMPLE:
----------------
A
B
C

How can I change the value of the caption in the field label on the
form based on the category? I attempted a series of Case statements
but it didn't seem to work when values of mixed categories were
returned.

Select Case PriceCat
Case "A"
MsgBox ([tblProduct.PriceTyp])
Me![frmSubSearch].Form.prdPrice1Gl.ControlSource =
"PriceTypeA"
Me![frmSubSearch].Form.prdPrice5Gl.ControlSource =
"PriceTypeB"
Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource =
"PriceTypeC"
Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
Case Else
Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
End Select

Re: Access 2002 Conditional Caption

Hunh? Why would you not be showing the Category in a TextBox, bound to the
field or as a calculated field? What is your purpose in using the Caption
of a Label to display the content/value of a field? Besides which, I just
don't understand what you mean by "the caption of the label of the field on
the form" -- more than a little explanation, I think, is in order before
anyone can offer you a worthwhile suggestion.

Larry Linson
Microsoft Access MVP


wrote in message
news:02dcd75e-8a06-4479-9e9f-7d3f77e70af3@c23g2000hsa.google groups.com...
>I have a form that returns 100 items. All of the items have have a
> different value in their Category field.
>
> EXAMPLE:
> ----------------
> A
> B
> C
>
> How can I change the value of the caption in the field label on the
> form based on the category? I attempted a series of Case statements
> but it didn't seem to work when values of mixed categories were
> returned.
>
> Select Case PriceCat
> Case "A"
> MsgBox ([tblProduct.PriceTyp])
> Me![frmSubSearch].Form.prdPrice1Gl.ControlSource =
> "PriceTypeA"
> Me![frmSubSearch].Form.prdPrice5Gl.ControlSource =
> "PriceTypeB"
> Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource =
> "PriceTypeC"
> Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
> Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
> Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
> Case Else
> Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
> Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
> Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
> Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
> Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
> Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
> End Select

Re: Access 2002 Conditional Caption

What you could try is this,

in your form's current event, you could implement a simple 'If'
statement

i.e.

if pricecat = "A" Then
yourlabel.caption = "The category is A"
elseif pricecat = "B" Then
yourlabel.caption = "The category is B"
elseif pricecat = "C" Then
yourlabel.caption = "The category is C"
else
yourlabel.caption = "Category not A,B, or C"
End if

I think that should work.

Re: Access 2002 Conditional Caption

Mixed Catagories? It sounds like your main form is set to 'Continuous
View'. I expect that you would only have one catagory per Record.
Your code will only work in you Current Record. So the labels should
change when you go to the next record.

Dominic

wrote in message
news:02dcd75e-8a06-4479-9e9f-7d3f77e70af3@c23g2000hsa.google groups.com...
>I have a form that returns 100 items. All of the items have have a
> different value in their Category field.
>
> EXAMPLE:
> ----------------
> A
> B
> C
>
> How can I change the value of the caption in the field label on the
> form based on the category? I attempted a series of Case statements
> but it didn't seem to work when values of mixed categories were
> returned.
>
> Select Case PriceCat
> Case "A"
> MsgBox ([tblProduct.PriceTyp])
> Me![frmSubSearch].Form.prdPrice1Gl.ControlSource =
> "PriceTypeA"
> Me![frmSubSearch].Form.prdPrice5Gl.ControlSource =
> "PriceTypeB"
> Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource =
> "PriceTypeC"
> Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
> Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
> Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
> Case Else
> Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
> Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
> Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
> Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
> Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
> Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
> End Select

Re: Access 2002 Conditional Caption

On Jan 16, 12:45 pm, "Larry Linson" wrote:
> Hunh? Why would you not be showing the Category in a TextBox, bound to the
> field or as a calculated field? What is your purpose in using the Caption
> of a Label to display the content/value of a field? Besides which, I just
> don't understand what you mean by "the caption of the label of the field on
> the form" -- more than a little explanation, I think, is in order before
> anyone can offer you a worthwhile suggestion.
>
> Larry Linson
> Microsoft Access MVP
>
> wrote in message
>
> news:02dcd75e-8a06-4479-9e9f-7d3f77e70af3@c23g2000hsa.google groups.com...
>
> >I have a form that returns 100 items. All of the items have have a
> > different value in their Category field.
>
> > EXAMPLE:
> > ----------------
> > A
> > B
> > C
>
> > How can I change the value of the caption in the field label on the
> > form based on the category? I attempted a series of Case statements
> > but it didn't seem to work when values of mixed categories were
> > returned.
>
> > Select Case PriceCat
> > Case "A"
> > MsgBox ([tblProduct.PriceTyp])
> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource =
> > "PriceTypeA"
> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource =
> > "PriceTypeB"
> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource =
> > "PriceTypeC"
> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
> > Case Else
> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
> > End Select

Here is my expectation:

EXPECTATION: If I return 3 records with my query that are in 3
different categories, I expect that the label control for the
respective fields/controls would reflect the category returned in the
query. If the first item returned is from Category A...the label on
the form should read category A...If the second item returned is from
Category B...the label on the form should read category B. I have a
form that is set to continuous forms...

The caption is for the label next to the unbound text box. The values
in the table are the same...but the captions are different, based on
the category.

Re: Access 2002 Conditional Caption

On Jan 16, 2:02 pm, "Dominic Vella"
wrote:
> Mixed Catagories? It sounds like your main form is set to 'Continuous
> View'. I expect that you would only have one catagory per Record.
> Your code will only work in you Current Record. So the labels should
> change when you go to the next record.
>
> Dominic
>
> wrote in message
>
> news:02dcd75e-8a06-4479-9e9f-7d3f77e70af3@c23g2000hsa.google groups.com...
>
> >I have a form that returns 100 items. All of the items have have a
> > different value in their Category field.
>
> > EXAMPLE:
> > ----------------
> > A
> > B
> > C
>
> > How can I change the value of the caption in the field label on the
> > form based on the category? I attempted a series of Case statements
> > but it didn't seem to work when values of mixed categories were
> > returned.
>
> > Select Case PriceCat
> > Case "A"
> > MsgBox ([tblProduct.PriceTyp])
> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource =
> > "PriceTypeA"
> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource =
> > "PriceTypeB"
> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource =
> > "PriceTypeC"
> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
> > Case Else
> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
> > End Select

The form is set to continuous because I am returning more than one
record. I am returning the records for view only.

Re: Access 2002 Conditional Caption

I'd consider using a list box with the results, and let the user select the
result they would like more detail from, then you can use the 'Single View'.

In any case, make sure your code is placed in your Form_Current event.

Dominic

wrote in message
news:010b582c-8eb7-4026-af7b-f63af419a9f3@v46g2000hsv.google groups.com...
> On Jan 16, 2:02 pm, "Dominic Vella"
> wrote:
>> Mixed Catagories? It sounds like your main form is set to 'Continuous
>> View'. I expect that you would only have one catagory per Record.
>> Your code will only work in you Current Record. So the labels should
>> change when you go to the next record.
>>
>> Dominic
>>
>> wrote in message
>>
>> news:02dcd75e-8a06-4479-9e9f-7d3f77e70af3@c23g2000hsa.google groups.com...
>>
>> >I have a form that returns 100 items. All of the items have have a
>> > different value in their Category field.
>>
>> > EXAMPLE:
>> > ----------------
>> > A
>> > B
>> > C
>>
>> > How can I change the value of the caption in the field label on the
>> > form based on the category? I attempted a series of Case statements
>> > but it didn't seem to work when values of mixed categories were
>> > returned.
>>
>> > Select Case PriceCat
>> > Case "A"
>> > MsgBox ([tblProduct.PriceTyp])
>> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource =
>> > "PriceTypeA"
>> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource =
>> > "PriceTypeB"
>> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource =
>> > "PriceTypeC"
>> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
>> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
>> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
>> > Case Else
>> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
>> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
>> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
>> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
>> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
>> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
>> > End Select
>
> The form is set to continuous because I am returning more than one
> record. I am returning the records for view only.

Re: Access 2002 Conditional Caption

On Jan 16, 7:19 pm, "Dominic Vella"
wrote:
> I'd consider using a list box with the results, and let the user select the
> result they would like more detail from, then you can use the 'Single View'.
>
> In any case, make sure your code is placed in your Form_Current event.
>
> Dominic
>
> wrote in message
>
> news:010b582c-8eb7-4026-af7b-f63af419a9f3@v46g2000hsv.google groups.com...
>
> > On Jan 16, 2:02 pm, "Dominic Vella"
> > wrote:
> >> Mixed Catagories? It sounds like your main form is set to 'Continuous
> >> View'. I expect that you would only have one catagory per Record.
> >> Your code will only work in you Current Record. So the labels should
> >> change when you go to the next record.
>
> >> Dominic
>
> >> wrote in message
>
> >>news:02dcd75e-8a06-4479-9e9f-7d3f77e70af3@c23g2000hsa.goog legroups.com...
>
> >> >I have a form that returns 100 items. All of the items have have a
> >> > different value in their Category field.
>
> >> > EXAMPLE:
> >> > ----------------
> >> > A
> >> > B
> >> > C
>
> >> > How can I change the value of the caption in the field label on the
> >> > form based on the category? I attempted a series of Case statements
> >> > but it didn't seem to work when values of mixed categories were
> >> > returned.
>
> >> > Select Case PriceCat
> >> > Case "A"
> >> > MsgBox ([tblProduct.PriceTyp])
> >> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource =
> >> > "PriceTypeA"
> >> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource =
> >> > "PriceTypeB"
> >> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource =
> >> > "PriceTypeC"
> >> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
> >> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
> >> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
> >> > Case Else
> >> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
> >> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
> >> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
> >> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
> >> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
> >> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
> >> > End Select
>
> > The form is set to continuous because I am returning more than one
> > record. I am returning the records for view only.

Listbox won't work...that is how I had the form initially...but the
user group would like to see it all at once...

Re: Access 2002 Conditional Caption

On Jan 16, 1:56 pm, Brian wrote:
> What you could try is this,
>
> in your form's current event, you could implement a simple 'If'
> statement
>
> i.e.
>
> if pricecat = "A" Then
> yourlabel.caption = "The category is A"
> elseif pricecat = "B" Then
> yourlabel.caption = "The category is B"
> elseif pricecat = "C" Then
> yourlabel.caption = "The category is C"
> else
> yourlabel.caption = "Category not A,B, or C"
> End if
>
> I think that should work.

Didn't seem to work. All the category labels were = "Category not
A,B, or C". All the items in the return were from category A, B, C
with the exception of the first item in the resultset that was not.
When I remove that item, the first item in the result set is from
Category B, and all the labels are then = "Category B".

Re: Access 2002 Conditional Caption

On Thu, 17 Jan 2008 08:27:06 -0800 (PST), "dkohel@gmail.com"
wrote:

>On Jan 16, 7:19 pm, "Dominic Vella"
>wrote:
>> I'd consider using a list box with the results, and let the user select the
>> result they would like more detail from, then you can use the 'Single View'.
>>
>> In any case, make sure your code is placed in your Form_Current event.
>>
>> Dominic
>>
>> wrote in message
>>
>> news:010b582c-8eb7-4026-af7b-f63af419a9f3@v46g2000hsv.google groups.com...
>>
>> > On Jan 16, 2:02 pm, "Dominic Vella"
>> > wrote:
>> >> Mixed Catagories? It sounds like your main form is set to 'Continuous
>> >> View'. I expect that you would only have one catagory per Record.
>> >> Your code will only work in you Current Record. So the labels should
>> >> change when you go to the next record.
>>
>> >> Dominic
>>
>> >> wrote in message
>>
>> >>news:02dcd75e-8a06-4479-9e9f-7d3f77e70af3@c23g2000hsa.goog legroups.com...
>>
>> >> >I have a form that returns 100 items. All of the items have have a
>> >> > different value in their Category field.
>>
>> >> > EXAMPLE:
>> >> > ----------------
>> >> > A
>> >> > B
>> >> > C
>>
>> >> > How can I change the value of the caption in the field label on the
>> >> > form based on the category? I attempted a series of Case statements
>> >> > but it didn't seem to work when values of mixed categories were
>> >> > returned.
>>
>> >> > Select Case PriceCat
>> >> > Case "A"
>> >> > MsgBox ([tblProduct.PriceTyp])
>> >> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource =
>> >> > "PriceTypeA"
>> >> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource =
>> >> > "PriceTypeB"
>> >> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource =
>> >> > "PriceTypeC"
>> >> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
>> >> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
>> >> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
>> >> > Case Else
>> >> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
>> >> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
>> >> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
>> >> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
>> >> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
>> >> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
>> >> > End Select
>>
>> > The form is set to continuous because I am returning more than one
>> > record. I am returning the records for view only.
>
>Listbox won't work...that is how I had the form initially...but the
>user group would like to see it all at once...

Replace the label with a text box (disabled and locked).
Either create a calculated field in the recordsource of the form that returns
the correct caption for the record and bind the textbox to this field.

OR

Create a function that returns the required caption based on parameters past to
it and bind the textbox to this function. This will be significantly slower than
the first option and may result in a staggered screen update.

If you use a label whatever caption you set will apply to every record in the
continuous form. Using a textbox allows each record to be evaluated and
displayed individually.


Wayne Gillespie
Gosford NSW Australia

Re: Access 2002 Conditional Caption

There is only one detail item in the "form" for a continuous forms view, so
whatever you put in an unbound field, or however you change a label in the
active record will be changed for every visible detail record on the form.
You are asking the impossible, unless you use a bound text field within the
detail record.

Larry Linson
Microsoft Access MVP

wrote in message
news:4e5f4135-d789-41ac-8361-df32bfa9c523@m34g2000hsf.google groups.com...
> On Jan 16, 12:45 pm, "Larry Linson" wrote:
>> Hunh? Why would you not be showing the Category in a TextBox, bound to
>> the
>> field or as a calculated field? What is your purpose in using the
>> Caption
>> of a Label to display the content/value of a field? Besides which, I
>> just
>> don't understand what you mean by "the caption of the label of the field
>> on
>> the form" -- more than a little explanation, I think, is in order before
>> anyone can offer you a worthwhile suggestion.
>>
>> Larry Linson
>> Microsoft Access MVP
>>
>> wrote in message
>>
>> news:02dcd75e-8a06-4479-9e9f-7d3f77e70af3@c23g2000hsa.google groups.com...
>>
>> >I have a form that returns 100 items. All of the items have have a
>> > different value in their Category field.
>>
>> > EXAMPLE:
>> > ----------------
>> > A
>> > B
>> > C
>>
>> > How can I change the value of the caption in the field label on the
>> > form based on the category? I attempted a series of Case statements
>> > but it didn't seem to work when values of mixed categories were
>> > returned.
>>
>> > Select Case PriceCat
>> > Case "A"
>> > MsgBox ([tblProduct.PriceTyp])
>> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource =
>> > "PriceTypeA"
>> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource =
>> > "PriceTypeB"
>> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource =
>> > "PriceTypeC"
>> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
>> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
>> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
>> > Case Else
>> > Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
>> > Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
>> > Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
>> > Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
>> > Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
>> > Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
>> > End Select
>
> Here is my expectation:
>
> EXPECTATION: If I return 3 records with my query that are in 3
> different categories, I expect that the label control for the
> respective fields/controls would reflect the category returned in the
> query. If the first item returned is from Category A...the label on
> the form should read category A...If the second item returned is from
> Category B...the label on the form should read category B. I have a
> form that is set to continuous forms...
>
> The caption is for the label next to the unbound text box. The values
> in the table are the same...but the captions are different, based on
> the category.