Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: MS Access Forms/Tables Problem Afterdate value

    I have three field in a form namely
    1. item_id
    2. item
    3. hardware_id

    What i wanted to do was when i type an id in hardware_id which is equal to the hardware_id in my hardware table it will get the value from my table and pass this value to the item field.

    Hardware Table
    1. hardware_name
    2. hardware_id

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by jcombalicer
    I have three field in a form namely
    1. item_id
    2. item
    3. hardware_id

    What i wanted to do was when i type an id in hardware_id which is equal to the hardware_id in my hardware table it will get the value from my table and pass this value to the item field.

    Hardware Table
    1. hardware_name
    2. hardware_id
    Get what value ? Do you mean you want hardware_name to appear in the item field ? Do you then want to store this value (not good idea) or do you just want to diplay it so the user can see what hardware he's chosen? you can do this using dlookup() or you can add it to your source query (although depending on your design you may not be able to enter/edit data via this method).

    I'm slightly confused about what "hardware" is as opposed to what an "item" is. Perhaps you could explain the relationship.

    Chris

  3. #3
    Join Date
    Aug 2006
    Posts
    3
    yes i want the hardware_name to appear in the item field and store it. What i did first was to let the user choose from the item field as drop down menu from the hardware_name... But i wanted it to more easier, i want them to just type in the hardware_id and after that the item field will be automatically updated with the value from the hardware table.

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Ok, so you have a form for entering information (items whatever they are??) and this is being stored in a table other than your hardware table. You want the user to enter the hardware_id but you want to store the hardware_name. This is a strange this to do I think. Normally you would store the hardware_id as this is the primary key of your hardware table and hence uniquely identifies your hardware (I realise I'm making an assumption here).

    So I would propose that you allow the user to enter the hardware_id and store that in your table. Then if you need to refer to the hardware name in, for example, a report, then you create a join query so that hardware_name becomes available for you to use. Equally you could display the hardware_name along side the box where the hardware_id is entered so that the user gets some feedback about what is being entered (but the hardware name doesn't get store - no need).

    I suspect we have some database design issues here. Pehaps you could explain a little more about the purpose of your for i.e. what is an Item, what is the source data of the form and how and item is related to hardware. Then we maybe able to better advise better.

    If you really really want to enter a hardware_id in a box and have the form store the hardware name then do the following:

    • You need to have two text boxes on your form (besides any others you might want).
    • One text box will be for your hardware_name and will be bound to your table (I've called it Hardware_ID_Textbox). This text box can be set to invisible so that the users don't see it although you might want to keep it visible while your testing
    • Create another text box (I've called it Hardware_name_textbox) for entering the hardware_ID. This should not be bound
    • Add the following code to the afterupdate event for the hardware_ID textbox:

    Code:
    Dim myCriteria As String
    myCriteria = "Hardware_ID='" & Me!Hardware_ID_textbox & "'"
    Me!hardware_name_textbox = DLookup("Hardware_Name", "Hardware", myCriteria)
    That's it. When you enter a hardware id in the Hardware_id box, the code will lookup the hardware name and put it the the box bound to your underlying data source.

    hth
    Chris

  5. #5
    Join Date
    Aug 2006
    Posts
    3
    thanks for the help i found the answer already i use the function below. just like you said... thank you.. maraming salamat po

    item = DLookup("hardware", "hardware", "hardware_id = '" & hardware_id & "'")

Posting Permissions

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