Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: Need to lookup value in table

    Hi,

    I'm new to Access and DB programming (though have experience in VB and VBA).

    The question is simple: I want the user to be able to type in the Ticker symbol (Abbr) for a stock then lookup that value and return the whole name in the Name field once the Abbr field has lost focus.

    I have the table of Abbreviations and full Names, which is three fields: the AutoID, Abbr and Name.

    The lookup would be invoked by the Positions table which is what the user would be interacting with.

    Thanks,
    Jeff

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    If you were on a form you could do something like:
    Code:
    'UNTESTED
    me.TxtBoxFullName = nz(dlookup("[FldName]","TblName","[FldAbbr] = '" & me.TxtBoxAbbr & "'"),"")
    Quote Originally Posted by Jeffrey58
    The lookup would be invoked by the Positions table which is what the user would be interacting with.
    But are you saying that the user is editing data directly in the table?
    Me.Geek = True

  3. #3
    Join Date
    Jun 2007
    Posts
    7
    Hi,
    Thanks for your reply.

    Actually I was trying to do this by editing the table in design view. But your code does make sense, I just don't know where it would go. How can I get "under the hood" and see the VB code for this? I thought there would be something like a Modules View?

    In response to your second query, the user would have access only to the Positions table. The list of Abbreviations and Stock names would be uneditable.

    Thanks,
    Jeff

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Jeffrey58
    Actually I was trying to do this by editing the table in design view. But your code does make sense, I just don't know where it would go. How can I get "under the hood" and see the VB code for this? I thought there would be something like a Modules View?
    As far as I know, you can't do modules for tables. In Access, tables are pretty much just meant to store data, not to do anything like what you're asking. If you want to do what you're asking, I think you're going to have to design a form. You can then use an event to trigger the code above.

    HTH
    Me.Geek = True

  5. #5
    Join Date
    Jun 2007
    Posts
    7
    Hi,

    Actually I did create a form and got it linked up to the table via Form->DesignView. I had thought to build something off of the Abbr textbox but the
    whole Macro builder and expression builder thing just looks bizarre. I'd much rather get at this through VBA but I don't see where the code is generated. Can I get behind this and look at the code the system is generating? Or at least how can I get in there to specify a particular event (like:"User has just typed something in the Abbr field and left"). Sorry I've just never dealt with VB/VBA in this context so I'm pretty disoriented.

    Thanks,
    Jeff

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    No problem

    Right click your text box in design view, and check out the events tab. You can put code in any one of those events by clicking the button with the elipsis to the right of the event, and selecting code builder from the pop up form. Access should generate a module for that event, and any time that event is triggered, it'll run whatever code you put into that module. Without knowing your situation entirely, I'd recommend looking at the After Update event. Use my code from above, but fill in the appropriate names.
    Me.Geek = True

  7. #7
    Join Date
    Jun 2007
    Posts
    7
    Okay, I've been able to get in there to do some basic things like programmatically change text on a text box ("try walking before running a 100 yard dash"). It's taking a bit to adjust to this environment, but I'm getting it. Hopefully I'll be able to integrate some database commands here soon...

    Thanks,
    Jeff

  8. #8
    Join Date
    Jun 2007
    Posts
    7
    Hi Nick,
    What I was trying was to access the table independently, without having anything linked to it. So I used the code you suggested substituting in the relevant field names.

    It is giving me the message the "table 'MyStockNames' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically".

    Is there a way to check to see who has it open and request access? To my knowledge the Table is not open though I'm not sure what an "open" table is.

    Thanks for any insight.

    Jeff

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Jeffrey58
    Is there a way to check to see who has it open and request access?
    Are you the admin on this dB? Are you the only one working on this particular copy? I never work on a dB that has been released and is in use (the only exception to this is when I need to do an update or something).
    Quote Originally Posted by Jeffrey58
    What I was trying was to access the table independently, without having anything linked to it.
    Where is your table?
    Me.Geek = True

  10. #10
    Join Date
    Jun 2007
    Posts
    7
    Hi,

    Interesting. I think I may have inadvertantly had the table open but minimized? I'm not sure, but I tried closing and reopening Access and now it is working. It looked up MSFT and returned Microsoft Cp. Very cool.

    Thanks.

  11. #11
    Join Date
    Jun 2007
    Posts
    7
    To avoid this problem in the final version, is there a way to make sure the tables stay out of view and closed from the user?

    Jeff

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    Check out Tools > Startup... There's some settings there (such as hiding the database window) that will allow you to keep some of the behind-the-scenes-stuff, well, behind the scenes. You might also want to look at the Switchboard Manager then. It's a quick and dirty way of creating an adequate form for your users to interface with.
    Me.Geek = True

Posting Permissions

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