Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Looking up Data in Text Box

    Hi, I am trying to understand how its posible to input into a text box a value and then it change to another lookup
    example
    txtAge = 30 but shows as 30 and Over
    Is this possible to do?

    Jez

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    The short answer is almost certainly yes, but without more specific information cannot be more helpful. Only comment is that you should store date of birth in a table not age (age changes with time, at least mine does, most inconvenient!).


    MTB

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    MTB, Sorry being a bit of a pain today...

    Reading back through that thread, not the best example to choose.

    I'll try again and explain better what I am looking at doing...

    In my input form I have a field that looks at Ethnicity, I also have a table that has all different breakdowns of ethnicity. example

    Letter = Meaning
    A = British
    M = Caribbean
    R = Chinese

    What I want to do is when I get to the text box where the code needs to go, I input the relevant code, but I want it to show as the meaning instead of a letter and then populate a column in my data table to show both the letter and the meaning

    Hope this is a better example.

    Jez

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    What happent to the Age question ?

    With regards to the last post, as you have all the ethnic categories etc. stored in the DB, with the codes & descriptions ?

    Then perhaps you should be looking at listbox or combobox controls ???


    MTB

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    If you are using a combo box, as I assumed in the duplicate post, you can do what you are asking through a UNION query. Because you should have, as MTB suggested, these codes and descriptions in your DB, you can create a UNION query that will allow you to enter A and have the combo box just show British. To do this, create two separate queries, as per my sample below, then put them into a UNION query. Here is the code for the UNION query:
    Code:
    SELECT Letter, "A" AS SortBy, Meaning
    FROM tblLetterMeaning;
    UNION SELECT Letter, "B" AS SortBy, [Letter] & " - " & [Meaning] AS Combined
    FROM tblLetterMeaning
    ORDER BY SortBy, Meaning;
    And here is the output of that query:
    Code:
    Letter      SortBy    Meaning
    ~~~~~~      ~~~~~~    ~~~~~~~
       A          A       British
       M          A       Caribbean
       R          A       Chinese
       A          B       A - British
       M          B       M - Caribbean
       R          B       R - Chinese
    Although this looks like it duplicates everything (which it sort of does), by putting the Letter in front of the Meaning for the second half of the listing, that allows the user to enter just the letter code, at which point they will see that entey, unless there is an entry that starts with that letter. For example, if you have a Letter code of "C", then just entering "C" will popup Caribbean. But a "C" and a space will give you the "C" code entry, which will look like: "C - Whatever C Is"

    For the combo box itself, you will need to define it as 3 columns, and the column widths need to be 0, 0. This will hide the first two columns, and leave the 3rd column as the default width. You will also need to define the first column as the Bound column.

    Hope this helps,

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    Not to hijack a thread here but what if you want to take it to the next logical step?
    So, using the example already provided, you want to have British, Chinese, etc. show up on a form. The table stores the data as A, C, etc but there is a supporting table that clarifies a=british, c=carribean or whatever. So you want the combo box to look up the supporting tables list and to let a user select it but then you want the letter or number stored in the main table.

    I'm thinking I need a hidden field on the form to store the letter and to have a combo box pointed at the supporting table with an "on change" event on the combo box that updates the field.
    Am I massively over complicating it?

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Starkmann,

    Yes, you are massively over complicating it.

    Have you tried what was suggested above? From what I understand in your post, the above will do just what you want. I have found that 99% of the time, the normal way Access is designed, is what we really need/want. What I have added to the "normal" way of doing things is the ability to allow the user to input the code followed by a blank rather than having to type British, or even the first few characters, which would be the normal way. And, by putting the code first descriptions (which is what allows the code entry in the first place) at the end of the list of available entries, that sort of hides them, and also puts just the British in the visible part of the combo box, rather than the A - British in the visible part of the combo box. But, the combo box is bound to the first column, which is where the code is, so there is no need for some hidden field, and magical lookup stuff anywhere.
    HTH,

  8. #8
    Join Date
    Feb 2007
    Posts
    348
    dang it!
    I clearly misunderstood the original question cause the original person was working well ahead of me. Sorry bout that.
    Golfer guy you hit it. specifically with

    For the combo box itself, you will need to define it as 3 columns, and the column widths need to be 0, 0. This will hide the first two columns, and leave the 3rd column as the default width. You will also need to define the first column as the Bound column.
    I think I'm gonna have one more question but I want to poke it with a stick first.

Posting Permissions

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