Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Location
    Orlando, FL
    Posts
    6

    Unanswered: List Box - multiple columns

    I'm fairly new to Access and totally self-taught. I am not a programmer and use it to help keep track of lots of data for myself at work. I have run into a situation that I have not been able to figure out myself and I am actually having trouble describing. I've done Google searches in the past and found something that eventually leads me to what I need to figure it out on my own. No can do this time, so here goes: I have a table that has many fields and in one of them (called "City") I've used a 2nd table to provide choices in a list (8 different choices). I set this 2nd table up with 2 columns - 1 with a 2 digit numeric "code" (city number) and the second with the name for this code (name of the city). When entering data, I have bound column 1, even though I display both in the drop-down. When the choice is made, the data captured in the first table is simply the 2 digit code. If I use a query, then I enter the 2 digit code to find the records I'm looking for. Now here's the real issue - when I create a report from that query, I want the report to display the city name from the 2nd column in the list, not the 2 digit code. I've tried making another field ("city name") in the first table and then make it look at "City" to get the data, but display the data found in the 2nd column from the list's table. I am lost and need direction if anyone can understand what I'm trying to do - all help is GREATLY and TRULY appreciated. Bruce.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As the Control Source for an Unbound Textbox on the Report, use the DLookup Function to return the CityName associated with the given CityCode.

    If CityCode is defined as Numeric
    Code:
    Me.ReportTextbox = DLookup("CityName", "CityChoiceTable", "[CityCode] = " & Me.CityCode)
    If CityCode is defined as Text
    Code:
    Me.ReportTextbox = DLookup("CityName", "CityChoiceTable", "[CityCode] = '" & Me.CityCode & "'")
    Replacing the names I've used with your actual names, of course.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2011
    Location
    Orlando, FL
    Posts
    6

    Thanks

    Linq, Thanks for your help. It's got me heading in the right direction to understanding how to do this, but I am still missing something. In my post yesterday, I used fictuous names to help you (or anyone) better understand what I was doing. I tried to substitute the fictitious names in the string you provided with the real ones from my dB and I could not make it work.

    The main table is "CONTACTS" in this table the field linked to the other table is "DISTRICT". The 2nd table is "DISTRICTS" and has only 2 fields - "ID" (the 2 digit code in text) and "DISTRICT" (the mutli digit name of the district also in text). I have bound the "DISTRICTS.ID" field to every other table, query, report or form needed, so that all entry is simply a 2 digit code. The only place the name (DISTRICTS.DISTRICT)appears is in the drop down lists. In the query that I'm attempting to run this report from I have a variable entry for CONTACTS.DISTRICT ([Enter District #]) so that I can run a seperate report for each district.

    I may be making in more complicated than it is, but like I said yesterday - I'm self taught and some of the lingo escapes me still.

    Thanks again - Bruce

  4. #4
    Join Date
    Jun 2011
    Location
    Orlando, FL
    Posts
    6

    More

    Not sure it makes a diff, but I'm using MS Access 2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •