label text VBA

[Dick7Access]

Experimenting I tried this code to change the label to display the full name of the state on the form. Is there a shorter way than putting 49 more states after the if?

Private Sub cmdStates_Click()
DoCmd.OpenForm "frmChurchesAll"
DoCmd.ApplyFilter "qryFindState"
lblTxtSt = txtState
If txtState = "FL" Then
lblTxtSt = "Florida"
End If
lblTxtSt.BackColor = 15658720
lblTxtSt.Visible = True
lblStatesof.Visible = True
lbAllChurches.Visible = False
End Sub /CODE]
D7

 

[Dick7Access]

Maybe I shouldn't have tried to get fancy, but that's the way I learn, always trying something new.
The above code changes the top label to "Florida" ok but for some reason it also changes the text of the combobox that holds the txtState to the full name. How can that be? I don't see any code that should effect the txtState. Anybody have an idea? I also realize that if I have to add 49 more states, I will probable be better off using a case statement instead of If. What is your opinion on the case statement?

D7

 

[Cronk]

Are you sure lblTxtSt is a label and not a text box? If the code ran, there would be a syntax error otherwise. Use lblTxtSt.Caption=.....

Use a table to hold the State full names and abbreviations to avoid hard coding a lot of if/then statements or otherwise.

 

[Dick7Access]

Are you sure lblTxtSt is a label and not a text box? If the code ran, there would be a syntax error otherwise. Use lblTxtSt.Caption=.....

Use a table to hold the State full names and abbreviations to avoid hard coding a lot of if/then statements or otherwise.

[FONT=&quot]You were right. I have a label the text is "State of" next to it is supposed to be a label that will change to whatever state is chosen with the find state query, but it isn’t a label, it is a text box “txtState”, same as the field that populates my combobox. I always feel stupid when I do dumb things like that, but I always ask anyways as I would rather feel dumb than stay dumb. Thanks again[/FONT]

 

[Dick7Access]

works great!

Private Sub cmdStates_Click()
DoCmd.OpenForm "frmChurchesAll"
DoCmd.ApplyFilter "qryFindState"

lblStLong.BackColor = 15658720
lblStLong.Visible = True
lblStLong.Caption = Me.txtState

    If lblStLong.Caption = "FL" Then
    lblStLong.Caption = "Florida"
    End If

lblStatesof.Visible = True
lbAllChurches.Visible = False
End Sub

Next dumb question. I know how to populate a comb box from another table but how would I populate a label from another table

 

[WayneRyan]

Dick,

What would help here is to have a table of States.

tblStates
=========
id autonumber
Abbreviation Text(2)
FullName Text(50)


Then you can join your qryFindState to tblStates using the two-character abbreviation
and you will have the full name available.

Then you won't need If or Case statements.

hth,
Wayne

 

[Galaxiom]

tblStates
=========
id autonumber
Abbreviation Text(2)
FullName Text(50)

Nothing to gain by including the autonumber. Use the abbreviation field as the PK. It will be unique and with just two characters it will only be four bytes per records, same as the autonumber.

 

[Dick7Access]

Nothing to gain by including the autonumber. Use the abbreviation field as the PK. It will be unique and with just two characters it will only be four bytes per records, same as the autonumber.

I already had a State table that I use to populate a combobox for the txtState., (2) characters, which worked fine. However when I ran the find state query it would put, of course, put just two letters in the label State. I wanted to get fancy and spell the whole state name at the top label, so that it would read "State of (whole word)"
I was able to do that easily by putting a if statement if textstate = FL then lblstate would be Florida. I didn't relish putting in 50 if's or even 50 case. Wayne suggested as you read putting a field in with the (2) another field spelling out the whole state, and join that table in my find state query. However, when I join the main table with the state table, the find state query pulled up 50 duplicate records for each record that had the desired state in the find state query,

 

[Galaxiom]

Labels should be used as labels, that is the display of static data. Where data is dynamic then it is better displayed in a textbox.

A textbox locked and disabled with the border set to transparent looks exactly like a label but it can display what is designated in its ControlSource.

If you already have a combo with the State abbreviation you could add the StateName as another column then use that column as the ControlSource of the pseudolabel textbox.

No code required.