Results 1 to 9 of 9

Thread: Darn Lookups

  1. #1
    Join Date
    Sep 2009
    Posts
    5

    Unanswered: Darn Lookups

    Hi,

    I'm going to start with the notice: I've upgraded to Access 2007.
    How different is this?!

    Anyway...

    So, I have a form, with a few items on it... long story short, I have a field called Supplier Name, that is populated through a drop down list from another table.

    When the Supplier name is chosen, I want the filed below; Supplier Code (which is just a number related to the Supplier) to update automatically.

    I've searched tirelessly for some info on it, however, it's all written in ways I don't understand.. VBA and code, of which, I am less than novice.

    I'm a simpleton at heart.

    Any help appreciated - even if it's just to laugh at my plight.

    Lovely.

    Andy

  2. #2
    Join Date
    Sep 2009
    Posts
    5
    I looked at this;

    Update field in MS Access using Lookup Table? - Yahoo! Answers

    which was quite nice.

    However, I have NO IDEA what they are talking about.

    Andy

  3. #3
    Join Date
    Sep 2009
    Posts
    5
    I'm now looking at DLookups on ;

    http://office.microsoft.com/en-us/ac...288251033.aspx

    again, not fully understanding.

    Thanks,
    Andy

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    All you need to do, assuming your table design is correct, is to include the code in the forms underlying query and bind a control to it... if it's a bound form you're talking about.
    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

  5. #5
    Join Date
    Sep 2009
    Posts
    5
    I found that the best way for me to do it on the form, was to use a dblookup in the source for the field.

    However, now that my form field auto updates based on a selection within another field, when the record is saved, it does not save the auto looked up fields back to the table.

    Any idea how I can sort that?


    Thanks.
    Andy

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't see why you are designing like that.

    The way it normally works (for bound forms) is that you have a combo box bound to your SupplierCode in the <whatever form you're working on> form which shows the supplier name, but only stores the supplier code in the table. You don't need Dlookups or code or anything like that to do it.
    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

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What I do is this...

    I have my combobox which is bound to a field (ie. it has a controlsource - perhaps the field SupplierCode for the main data table). The first column in the rowsource for the combobox is the SupplierCode field. The 2nd column in the combobox is the SupplierName (whatever tables I "may" need to link together in the rowsource to make the 2nd column the SupplierName. If the rowsource is based on 1 lookup table that has SupplierCode and SupplierName, I don't need to link in any other tables in the rowsource). Since the combobox has a controlsource, whatever is selected in the dropdown of the combobox is stored in whatever field the controlsource is (which the form's recordsource itself is hopefully your "main" data table). Now if the FORM's recordsource is based on something other than the main data table you're updating (ie. a lookup table or other table), then you have to take a different approach (this is not normal.)

    Then in the AfterUpdate event of the combobox, I set my "SupplierName" field on the form equal to the 2st column in the combobox (which is really identified as column 1 in code since column "0" is the combobox's true value).

    For example, my code in the afterupdate event of the combobox might look like this:

    me!SupplierName = me.mycomboboxname.column(1)

    This essentially writes the 2nd column of the combobox's rowsource (for whatever value is selected) into the SupplierName field.

    Just remember:
    me!mycomboboxname = the 1st column of the combobox rowsource
    me.mycomboboxname.column(1) is the 2nd column of the combobox rowsource
    me.mycomboboxname.column(2) is the 3rd column of the combobox rowsource
    etc...etc..

    (Microsoft just couldn't make it easy for comboboxes and I'm often counting the columns in the comboboxrowsource and subtracting 1 to find the correct column I want.)

    If in doubt, simply add this code to your afterupdate event of the combobox:

    msgbox me!mycomboboxname
    msgbox me.mycomboboxname.column(1)

    (or per your question, you can have SupplierName the 1st column and SupplierCode the 2nd column). I re-read your post and got it backwards above. (although ideally, I'd have it setup like above and simply make the 1st column of the combobox hidden (ie. column widths = 0";2") so it shows SupplierName in the combobox (but is really storing the SupplierCode) and then make the SupplierName field on the form invisible.

    Just remember, the rowsource for a combobox is simply the "values you can select from" for the field (ie. "here's the values you can pick from to store in this field".) The combobox field should have the controlsource = to the field from the data table for the form's recordsource itself that is being used for storing that data. If you configure the rowsource query for the combobox correctly, you can utilize that to populate other fields on the form based on what is selected in the combobox using the .column(x)

    ...and you simply manipulate which column you actually want to show (visibly on the form) for the combobox using the column width's.

    I hope this all makes sense. Comboboxes are great tools to use on a form and you should read up and understand how they work.
    Last edited by pkstormy; 09-05-09 at 04:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Sep 2009
    Posts
    5
    Awesome reply.

    I'll have a fiddle with this later today and see how I get on.

    I'll have a good old read of the combo box stuff too.

    I have this Massive 1000 odd page Access book - but it's from MS Access 2 (so it's about 10 years out of date and never seems to show what you want to do).

    Thanks,

    I'll let you know how I get on.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Get the MSAccess Developers Book by Sybex. It's a great book with lots of examples and includes a CD with mdb files of those examples. And best of all, you don't need to run any "setup" to look at the examples! You just open the appropriate mdb file and see the code.

    I especially use the FormInfo and SystemInfo class modules in all my applications (I just import them into the mdb.)

    It's the only MSAccess book I keep on my shelf. It won't matter if you purchase the 2000, 2002 or 2007 (if they have one). I have the 2000 version and still use it.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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