Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    2

    Unanswered: Form Help: have a text box assume a default value based on another text box's value?

    Hello everyone, this is my first time posting here. I recently started using Access to help keep my laboratory organized by tracking the usage of equipment and various consumables. The version I have available for use is Access 2010 (v14.0.6129.5000, 32-bit).

    I'm trying to set up a form that lets me update the table listing everything I have in-stock as supplies are used up. The table ("LED Inventory") associated with the form ("LED Inventory Adjustment Form") has fields "Lot #" (number, primary key) and "# Parts On-Hand" (number). What I'd like to do is have the text box for "# Parts On-Hand" fill itself in once I've filled in the "Lot #" text box.

    Right now I'm trying to accomplish this by setting the ControlSource property of the "# Parts On-Hand" text box to the expression below, but it just seems to leave the text box blank instead.
    Code:
    =DLookUp("[# Parts On-Hand]","[LED Inventory]","[# Parts On-Hand] =" & [Forms]![LED Inventory Adjustment Form]![LOT #])
    If at all possible, I'd really prefer to avoid solutions that require VBA or SQL since I'm not very familiar with them.
    Last edited by PolskiPhysics; 09-20-13 at 12:04.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what datatype is lot #
    if its numeric your code should work
    if its alphanumeric it will need delimiting

    Code:
    =DLookUp("[# Parts On-Hand]","[LED Inventory]","[# Parts On-Hand] ='" & [Forms]![LED Inventory Adjustment Form]![LOT #] & "'")
    im assuming that [# Parts On-Hand] should actually refer to the lot number column in your table

    incidentally you will make life a lot easier for yourself if you drop the space in your table /column names. there is absolutely no need to use spaces (which are great for humans but not nbeed for computers. if you want a human useable description set somethign in the caption property of each column
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Posts
    413
    Most everyone here uses VBA,if your going to use Access, try and learn it. FYI, Please do yourself a favor and research naming conventions, you have some characters and spaces that should not be used. By your code your saying Lot # on LED Inventory Adjustment Form (If these names are correct) should equal Parts On-Hand from Table Parts On-Hand, is this correct? Lot # should be LotNo and LED Inventory Adjustment should be tblLEDInventoryAdjustment and LED Inventory Adjustment Form should be frmLEDInventoryAdjustment as examples.

  4. #4
    Join Date
    Sep 2013
    Posts
    2
    Quote Originally Posted by healdem View Post
    im assuming that [# Parts On-Hand] should actually refer to the lot number column in your table
    Making this change seems to have solved the issue. Thanks!

    Quote Originally Posted by Burrina View Post
    Most everyone here uses VBA,if your going to use Access, try and learn it. FYI, Please do yourself a favor and research naming conventions, you have some characters and spaces that should not be used. By your code your saying Lot # on LED Inventory Adjustment Form (If these names are correct) should equal Parts On-Hand from Table Parts On-Hand, is this correct? Lot # should be LotNo and LED Inventory Adjustment should be tblLEDInventoryAdjustment and LED Inventory Adjustment Form should be frmLEDInventoryAdjustment as examples.
    I really should do both of those things... my other responsibilities have kept me busy enough that I haven't really had the time. Before I started at this location there was no real record of lab supplies to speak of, so my top priority is simply to get a database created as quickly as possible.

  5. #5
    Join Date
    Nov 2011
    Posts
    413
    Glad it is working for you. Good luck With Your Project.

Posting Permissions

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