Results 1 to 10 of 10
  1. #1
    Nate74 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Location
    Southern CA
    Posts
    10

    Text Box Populated based on ComboBox selection - two linked tables

    This is total newbie question I'm sure, but as my MS Access guru hasn't been hired yet... and I'm trying to get started on my own, here goes.



    I'm trying to setup an auto quotation tool for simple metal components. I've setup a number of input text boxes that use a calculation to determine the volume of the component. From there, I have a ComboBox with all the alloys listed which in turn populates a text box with the density of the selected Alloy so I can calculated the weight of the components (Volume*Density).

    I have two tables. The first, AlloyDensity, is the list of alloys and their densities. This one obviously won't be changing. The second, AlloyPricing, is a list of the alloys and there $/lb price. This one will need monthly updating. I used the relationship screen to "link" the two tables together by alloy name.

    I'm to the point now where I need the $/lb price from the AlloyPricing to populate a text box for use in a calculation, but the ComboBox is referencing a column in the AlloyDensity table.

    I'm messing with a DLookUp in the Control Source that looks like this:

    =DLookUp("[AlloyPricing]![Price/Lb]"," [AlloyPricing]"," [AlloyPricing]![Alloy] = [Alloy_Value] ")

    Where Alloy_Value is what I've called the contents of the Alloy dropdown box.

    But that's just going off something I found online as a tutorial and it's not working.
    Last edited by Nate74; 01-17-2013 at 11:17 AM.

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Try
    Code:
    =DLookUp("[Price/Lb]","AlloyPricing","[Alloy] = '" & Me.Alloy_Value & "'")


    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Nate74 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Location
    Southern CA
    Posts
    10
    Quote Originally Posted by Missinglinq View Post
    Try
    Code:
    =DLookUp("[Price/Lb]","AlloyPricing","[Alloy] = '" & Me.Alloy_Value & "'")


    Linq ;0)>
    Thank You! I'm happy to see I was on the right track. I'm a bit confused by the "Me" part though. Is that actually part of the code or is that forum code for something else?

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Me. takes the place of explicitly referencing the Form, in VBA code. You indicated that your Combobox (what you called a 'dropdown box') on the Form was named Alloy_Value, so

    Me.Alloy_Value

    in VBA code, essentially means 'the Control named Alloy_Value on the Form the code resides in.

    So, if your are in the code module for a Form named MyFormName, you can refer to a Control on that Form as

    Forms!MyFormName.MyControlName

    or

    Forms("MyFormName")("MyControlName")

    or more simply

    Me.MyControlName

    Which, of the three, would be easiest to remember/less likely to make a mistake spelling, especially if the Form has a long name?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Nate74 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Location
    Southern CA
    Posts
    10
    Cool, that makes sense. And THANKS! So something still isn't working though... just keeps returning "#Name?" Is that indicating a syntax error or???

  6. #6
    Nate74 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Location
    Southern CA
    Posts
    10
    Hmmmm.... well still struggling with this. I've made some progress though. I went into the VBA debugger thing and into the Immediate table.

    When I broke out the first part of the DLookUp code,

    ? DLookUp("[Price/Lb]","[tblAlloyPricing]")

    It returns the first price in the table, regardless of what Alloy_Value is; which I think is correct.

    So, my problem is with my criteria. When I hard code a criteria like this:

    ? DLookUp("[Price/Lb]","[tblAlloyPricing]","[Alloy] = '101 OFHC Copper'")

    It returns the correct value, in this case $2.

    So somehow, I think the way I'm referring to the value in Alloy_Value isn't working. So close, yet still so far away...????

  7. #7
    Nate74 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Location
    Southern CA
    Posts
    10
    Well, finally got a code that seems to work:

    DLookUp("[Price/Lb]","[tblAlloyPricing]","[Alloy] = '" & Forms![Form1]![Alloy_Value]& "'")

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    The problem with the code I originally gave you was that I used 'AlloyPricing,' the Table name you used in your original code, and in your latest code you've changed that to tblAlloyPricing, which is apparently the correct name, since the code now works! As my signature says The Devil's in the Details!

    And assuming that this code is running behind Form1, you can replace

    Forms![Form1]![Alloy_Value]

    with

    Me.[Alloy_Value]


    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Nate74 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Location
    Southern CA
    Posts
    10
    I actually updated that table name after realizing there seem to be some standard formats for naming things. qryXXXX, tblXXXX, etc.

    But now it's working perfectly. Many thanks!

    The final step is to now have the multiple values from the combobox, text boxes, and calculated fields, export to a printable form. Should be easy after the DLookUp struggle

  10. #10
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Glad you got it working!

    Good luck with your project!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Populate a text box based on a combobox selection?
    By Richie27 in forum Programming
    Replies: 4
    Last Post: 04-25-2012, 08:00 AM
  2. Replies: 1
    Last Post: 09-06-2011, 01:47 PM
  3. Replies: 29
    Last Post: 08-16-2011, 05:52 PM
  4. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  5. Replies: 0
    Last Post: 12-16-2009, 01:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums