Using Case in a Form to display a Name rather than numeric value

[pjordan@drcog.org]

Ok, so my main table (REGIONAL_BRIDGES)has a column called COUNTY which lists counties by their County Codes (001, 003, etc).

In my form I am displaying the COUNTY which of course shows just the County Codes. I want to have a simple text box next to that, which will also display the actual County Names for those codes.

I tried using a lookup table and created one, but couldn't get that to work. Then I thought using a CASE statement in VB would be the best option, but I'm having an issue trying to get that to work.

So far what I have is a new Text Box on the Form called County_Convert which I have the CONTROL SOURCE as COUNTY (from the REGIONAL_BRIDGES table).

I then have an [event procedure] (though I am unsure what one I need (On Click, Before Update, After Update, etc). As I want this to always just display whatever value is in the COUNTY field (with the new text)).

The Event Procedure I have right now is:

Private Sub County_Convert_Click()

Select Case [COUNTY]
Case "001"
[County_Convert] = "Adams"
Case "005"
[County_Convert] = "Arapahoe"
Case "013"
[County_Convert] = "Boulder"
Case "014"
[County_Convert] = "Broomfield"
Case "019"
[County_Convert] = "Clear Creek"
Case "031"
[County_Convert] = "Denver"
Case "035"
[County_Convert] = "Douglas"
Case "039"
[County_Convert] = "Elbert"
Case "047"
[County_Convert] = "Gilpin"
Case "059"


[County_Convert] = "Jefferson"
Case "123"
[County_Convert] = "Weld"

End Select
End Sub


So in the end, my text box (County_Convert) still only displays the numeric value from the REGIONAL_BRIDGES, COUNTY field and am unsure what to do from here.

Thanks...

[JoeM]

I tried using a lookup table and created one, but couldn't get that to work.

Why not? That would be the most straightforward method and easiest to maintain.

Why don't we see if we can get those issues sorted out, it should be fairly easy, just create a two-column table, with the Code and County.

My guess is the problems you are having is with the method of looking up the data, no? If that is the case, please explain how you are trying to do that.

[orange]

This is an ideal set up for using a Lookup Table with 2 fields

eg: tblCountyLookup

CountyCode PrimaryKey
CountyName

You populate the table with your countycodes and names.

Then in a query, using your tblREGIONAL_BRIDGES and tblCOUNTYLookup

Select 
tblREGIONAL_BRIDGES.*
,tblCountyLookup.CountyName
From
tblREGIONAL_BRIDGES INNER JOIN tblCOUNTYLookup ON
tblREGIONAL_BRIDGES.CountyCode = tblCOUNTYLookup.CountyCode

Good luck

[pjordan@drcog.org]

I actually had a lookup table created already, I just didn't do the query part of it like you showed. Now how do I link that Query to my Text Box in my Form? Or is it not a Text Box I want to use? So that it can properly display the names?

Thanks for your help guys...

[JoeM]

This Form, is it for Data Entry, or just reviewing current existing records?
Your answer may help determine the best way to do this.

[orange]

Let's start with a query to make sure you get the data you expect.
Create a table as suggested.
Go to query wizard, SQL view and paste in the sql I provided.
Run the query - - Does it work? Is the data what you expected?

[pjordan@drcog.org]

Yes the result I want is just for display in the Form (not as a drop down menu like I have done before). It will show the County ID in the form which can be edited, but for reference I want the County Name to also be displayed as a visual.

And yes the Query works fine. I just don't know how to link the County Name from the Query to the text box in my form that is all.

I have in my txt box for CONTROL SOURCE: =[County_Code_Convert]![CountyName] (names are a bit different than your example as I had already created the lookup table previously). The above though doesn't return anything so I'm obviously doing this part wrong.

[JoeM]

If this is a "Display Only" Form, just create the Query Orange provided to you, and use this Query as the Record Source of your Form.
Then, all of these fields (including the CountyName) will be available for you to place on your Form.

[pjordan@drcog.org]

No it's not a display only form. The form mainly is to enter and edit the original Region_Bridge table. I just wanted to have this query to show up next to the actual entry field for County as a reference to make sure the correct code is placed in.

I've attached a photo of the Form itself and then a photo of the Properties of the Text Box I am trying to display the actual County Names.

So the County Name in the Form is what I'm trying to display with the lookup.

These are the actual table names (since I had them before Orange gave me his demo one)
(Region_Bridges) with COUNTY (which is the original ID)
(County_Lookup_Table) with CountyID and CountyName


(and the CONTROL SOURCE actually reads =[County_Code_Convert]![CountyName] (not what I had in the photo)
Hope that helps a bit more.

[JoeM]

OK, what I would then do is add VBA code to the After Update event of your COUNTY field, that looks up the County Name of the code chosen (using the DLOOKUP function - you can Google it if you need to see examples), and then populate the County Name text box with the value from the DLOOKUP calculation.

[pjordan@drcog.org]

Thanks for the suggestion on the dlookup. Not working the way I want it though. I may need to just give up on this for now and figure another way.

My original table: Region_Bridge has the COUNTY with all the Id's (many records and many with the same COUNTY value)
My Lookup table: County_Lookup_Table has CountyID (the county id's) and CountyName which correspond to those ID's.

My Form has a text box called COUNTY_Txt_Box which displays the original COUNTY from Region_Bridge which is fine

My new text box is called County_Lookup and this is where I am having the issue
- My Control Source for this I assume is COUNTY correct?
- Then in the AfterUpdate, I have the following:

=DLookUp("CountyName","County_Lookup_Table","COUNT YID=" & [COUNTY_Txt_Box])

I have tried a number of variations with that last argument and can't seem to figure out how to include the value of the original table ([COUNTY] from Region_Bridge) to the lookup table (County_Lookup_Table and match the CountID and then display the CountyName) in that dlookup command at all.

So with my limited knowledge of forms and VB and such, I'm still just having a hard time trying to get this to work. I'm sure it's simple and hopefully now it might make better sense what I'm trying to get. It's a Fri and I'm in going home mode now. I'll check back on Monday to see if you or Orange can figure out my issue.

Thanks again...

[orange]

Can you describe in plain English what you want to do. Forget Access and text boxes for the moment?

Are you using the query as the recordsource of the form?

[pjordan@drcog.org]

I just have a list of bridges and the counties they are in. The counties are in numeric form. I want to have a way of also having the county names displayed on my form based on those numeric numbers.

That way in my form (whose source is the original bridge table) which I can go to any existing bridge or create a new bridge, I can either see the numeric county number, but also the easier understandable name of the county. Or when creating it, I can enter in the number, but also verify the name is correct as well.

I just don't want to create a new field in the original table with the county names (which I could do), but I would like to find a way to "convert" that number to a name that one can understand better. Also if there is no value, I would like to have the name mention that there is currently no county name available.

Hope that makes sense.

[orange]

We are not suggesting you add another field to the table.
You have a table

Bridges that has details of each bridge.
You also have a field in that table to identify the CountyNumber for the county in which that bridge is located.

You also have a lookup table that has CountyNumber and CountyName.

If you use the query as the recordsource of the form, then you can get all of the fields in the form since they are all available in the recordsource.

If you use the Bridges table as recordsource, then you do not have the CountyName available.

To solve this, try

adding a blank textbox on the form (wherever you need it)

make its ControlSource

=DLookup("CountyName","County_Lookup_Table","CountID = '" & Me.County & "'")

Is County a numeric value in the Bridge table? What datatpye is CountID in the Lookup table?

[pjordan@drcog.org]

Yes it's the text box I am trying to get working so I will try out your code that you mentioned and see if I can get that to work.

And yes the original COUNTY is text in the table so I made the CountyID text as well (even though they only contain numbers).

The other option of course is just to populate the main table with a new field and just do it that way, but I was just curious how I can get something like this to work without having to do that for future forms I may be working with. So this is kind of my test form just to see how easy it is (or hard) to actually do something like this.