Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: displaying data in a form; dlookup

    I'm trying to get this to work in a gotFocus event in my subform:

    [Forms]![ovCountM]![Text_Location]= DLookup("[In-House Location]", "[list - sections]", "sec_code = '" & [Sec_Code] & "'")
    ovCountM is a parent form, the data will be taken from table "list - sections".

    Or maybe dlookup isn't what I need. Any suggestions? Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm not entirely certain what it is you want to do...... so I can't advise if dlookup is right or wrong for this use.

    so presumably its not working as you expect
    if so which part isn't working as your expect
    ie have you proven the dlookup is returning the corect value
    does it matter if it doesn't return a value (do you need to put in sme form of error trap to cater for missing data in the dlookup)

    fwiw I think you need to rethink your table & column names. it probably doens't matter too much here but Id get rid of the spaces and hyphenation, they will cause problems in the future.. they are the main reason you are having to encapsulate everything with square brackets
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2008
    Posts
    163
    The code I wrote won't work, I'm guessing I got the syntax wrong. When a record in my subform gotfocus, I want it to show the details of the record in the main form.

    What I'm doing is creating a summary screen for my users.

    I appreciate the advice about my field names, I regret that i stuck with my initial mistakes when creating the tables (I didn't know better then). In one case, I even used a reserved word. It is a hassle to mind the bracketing, but I've learned to live with it. Should I ever have to create a new database after this one (unlikely, but who knows), at least I know what (not) to do.

    Anyway, I don't mind if dlookup returns a null value for missing details (I want the textbox to be empty when there's no data). Anyway, the form's purpose is strictly for reviewing data and not for editing/adding/removing.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so again what part isn't working
    the dlookup
    OR
    the assignment to a contorl on anohter form?

    what I'm trying to do is to make you think about the development process and debugging, putting in watches and break points and stepping through the code. this si something that VB & VBA are excellent at, and used properly a great tool for the developer

    what I'd usually do is break each section into discrete elements. do the dlookup on one line (assign it to a local variable in the code), the assign that value to the control inthe other form.. break down the problem into smaller chunks and it becomes easier to see where the problem actually lies.. this is especailly true of SQL problems as often the developer is so close to the code, they know what it is supposed to do they don't see the problem, or they don't see what is actually being sent to the SQL engine.

    I regret that i stuck with my initial mistakes when creating the tables (I didn't know better then)...
    we've all been there..... some of us may have lost that teeshirt over the years, but we've all done that.

    I hardly ever use domain functions so Im ot all that familair with them
    it may be that dlookup is choking on the single quotes encasing the sec_code
    I take it you have checked that the column name, table name and sec_code all exist
    when you say it doesn't work.. what doesn't work... have you checked that the value of the parameter/where clause [Sec_Code] actaully has a value and it exists in the table

    its very common when debugging SQL to think the code is the problem, when it may be that the data being sent to eh SQL is the problem (ie you are asking for a null value n a where clause, or data that doesn't exist)

    check the values of Sec_Code in the form.... make sure it is the value you expect
    break apart the sttement into fucntional blocks, don't try to do evrything in ne sentance
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup... put a MsgBox DLookup("[In-House Location]", "[list - sections]", "sec_code = '" & [Sec_Code] & "'") before your line of code and see if the DLookup is returning the expected value. If it is, good, then it's a reference problem to the parent form's control and you can focus on that.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jun 2008
    Posts
    163
    Thanks guys.

    ST, the one you gave worked. Oddly enough, I already tried it before posting here (I keep every line code to know what I already tried, I just mark it with the ' -it makes for a very messy coding, but since I'm the only one viewing it, its fine)
    DLookup("[In-House Location]", "[list - sections]", "sec_code = '" & [Sec_Code] & "'")
    You did mean "msgbox =" right? or I'm totally missing something, again.

    ...development process and debugging, putting in watches and break points and stepping through the code...
    As much as I appreciate the tips, I honestly have no idea what those terms mean. I've only just recently learned how to use the immediate window for checking code. I'll try to read on it, sounds like its what you guys use to check for errors. Thanks again.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ST, the one you gave worked.
    Good. Then the DLookup works ok.

    You did mean "msgbox =" right? or I'm totally missing something, again.
    Yes. Not as a final solution, but as a means to confirm that the DLookup is working, instead of using watches, breakpoints or stepping through code. I sometimes do this as I CBA doing the latter.

    As much as I appreciate the tips, I honestly have no idea what those terms mean.
    Debugging
    The process of entering the code window in break mode is effectively debugging. It will automatically happen when there is a problem line in code. A line of code is yellow and the code stops. You can force this by using Stop, by setting a breakpoint or by stepping through code.

    Watches
    When you are in break mode (debugging), you can select an expression or a variable and create a watch for it. The expression is added to a watches window and you can see the result of the expression... so you can WATCH its content.

    A quick version of this is to just select the expression and then point to it with your mouse, but this is limited to only a small amount of text.

    Breakpoints
    Pressing F9 on almost any line of code will cause the code to enter break mode (debugging) when the code gets to the line of code. The line goes red... well... maroon or whatever colour

    Stepping Through Code
    Once you are in break mode, pressing F8 will advance the code one line at a time. This is called stepping through code.

    Stop
    Placing a VBA command Stop anywhere in your code acts like a "permanent" breakpoint. Remember to remove these!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jun 2008
    Posts
    163
    Ah, now I understand, I'll learn how to to that in the coding windows. Thanks again! =D

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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