Results 1 to 13 of 13

Thread: Newbe to Access

  1. #1
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32

    Unanswered: Newbe to Access

    Have a table with following fields, client name and client number. Would like to pull the client number when I type in the client name into a form. Been trying to pull this from the table.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Typing it into WHAT on the form? Text box? Combobox? Depending on what you're using it can already be done for you ... Suggestion: Try using a combobox ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32

    Newbe

    The name is a text field and client number is number.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Like M Owen has allready indicated...use a ComboBox.

    1) Place a Combox onto you form (when in design view),

    2) Make sure the Row Source Type property is set to Table/Query,

    3) Enter this SQL string into the Row Source property:

    Code:
    SELECT DISTINCT ClientTableName.ClientID, ClientTableName.ClientName FROM ClientTableName ORDER BY ClientTableName.ClientName;
    Of course, change the table and table fields tro their proper names. Table is indicated in red and table fields are indicated in blue.

    4) Make sure the Bound property for the ComboBox is set to 2,

    5) Make sure the Limit To List property is set to Yes. This way a use can't enter a name that does not exist within your table.

    6) Make sure the Column Count property for the ComboBox is set to 2,

    7) Make sure the Column Widths propety is set to: 0";1"


    There...Now in the On Click event for you ComboBox, enter code something like this:
    Code:
    MsgBox Me.myComboBoxName.Column(0)
    be sure to make sure your ComboBox name is used and not the one above.

    You see .Column(0) holds the Client ID but is not Visible because the Column Widths property has this column set to zero (0). You can set and reference any number of columns. It all depends upon how you configure things. Set your MS Access help system about this.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    Thanks for the hep. I can get the combo box, but I have to select the name. Maybe I missed something. Again, I would like to type the clients name and pull the client number.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RBURRELL
    Thanks for the hep. I can get the combo box, but I have to select the name. Maybe I missed something. Again, I would like to type the clients name and pull the client number.
    Well IF you use a combobox, you can have it set up where there are 2 columns: 1 has the client ID and the other the client name. IF you make the client ID column not visible (set the column width to 0) then all that anyone will see is the client name. Comboboxes have this nifty little feature called auto-complete where it matches to items in the list as you type ... Try it sometime.
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    Thanks for the help. I can select the name and the customer number appears. One more question, how do I get the name to stay in the client field in the table the info is stored.

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    Hmmm...Am I missing something here? RBURRELL, Can you elaborate a little more on your last post?

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  9. #9
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    I'm using forms to update a table. The combo will save the client number, but the client name is not saving in the table.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So uhh... Where are you trying put this client information. if it's anywhere BUT your client table, you should only be storing the client number.

    ProTip: Combobox's can only have one value. You get either the name OR the number. If you want both, you're going to have to do a bit more.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    Yes, the data is being put into a table. Since a combo box can only have one value I'll have to settle for the number. Going to input the name separate. Appreciate the help.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    which table?

    ProTip1: It is physically impossible to give us TOO MUCH information to help you.

    ProTip2: If you're doing what I think you're doing, which is attaching client information to a different table, then you should not EVER use the id AND the name as a foriegn key.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Teddy
    So uhh... Where are you trying put this client information. if it's anywhere BUT your client table, you should only be storing the client number.

    ProTip: Combobox's can only have one value. You get either the name OR the number. If you want both, you're going to have to do a bit more.
    Teddy is right to point out that a combo can give a single column. For a newbie its probably worth sticking to that thought. However you can retieve different values from a combobox (you can hide data within the rowsource and rretireve at will). -just something to bear in mind for use in a few months / years / decades times.

Posting Permissions

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