Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    dbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10

    auto fill fields based on another field


    I am building an inventory tracking database. I have a table with the columns "Part#"(primary key), "Vendor" and "Notes". I want to have various forms that will draw this information. I want a user to open the appropriate form and select "Part #" from a drop down list, then have "Vendor" and "notes" autofill.
    There will also be specific fields to each form, but this base info is what I am having trouble with.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    example:
    If you have a VENDOR combo on the form, the user would pick a Vendor,
    the combo would have an AfterUpdate event to populate the PART#s.

    Code:
    sub cboVendor_AFTERUPDATE
      cboPARTS.requery
    end sub
    The cboParts.Rowsource has a query that uses the criteria: forms!frmMain!cboVendor
    to pull all parts using that vendor picked on the form. The afterupdate refreshes the list if it changes.

    Any help?
    Last edited by ranman256; 05-16-2014 at 12:20 PM. Reason: spellg

  3. #3
    dbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    Not really what I want. I want them to pick PART# from a combo on the form, Vendor will auto-populate for the purchaser to see, but it is the part # that needs to get picked and then VENDOR and NOTES will autofill . The reason for this is so that if this part # is picked on a different form, the same notes will be reflected.
    also, not to complicate it, but if notes are then updated on 1 form, I would like the changes to be shown on any form listing that PART#.
    I hope that is clear, I am trying to explain as best I can but I am still a novice.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The query in the cboParts query would have 2 columns...[part],[vendor]
    Code:
    sub cboParts_Afterupdate
       cboVendor = cboParts.column(1)   'combo box object has col(0) as 1st column
    end sub
    After picked, the vendor would populate off that result.
    you get the idea?

  5. #5
    dbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    So I have tried that but a couple of problems... and please forgive my ignorance as I am sure this is an easy fix...
    When entering the first line "sub cboParts_Afterupdate" it continues to add a () after it... not sure if this is a problem... and also when I try to select a value from the drop down in the "Part" column, rather than updating the vendor it comes up with "run-time error '424' Object Required" and gives me the option to end or debug...
    I just dont get what I am doing wrong?

    I think the problem is in my "Vendor" field. I am not sure how this column should be set up... right now it is drawing from the same drop down list as the "Part" list, not autopopulating... how should this be setup?

  6. #6
    dbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    I think the problem is in my "Vendor" field. I am not sure how this column should be set up... right now it is drawing from the same drop down list as the "Part" list, not autopopulating... how should this be setup?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If vendor is a field in the parts table, it can be included in the row source of the combo, and the above technique should work. More info here:

    BaldyWeb - Autofill
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    dbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    OK! That is working great now. Thanks!
    I should be able to copy to get the Notes field to update the same way correct? Also if notes are written into the notes field on the form, will it update the source table?

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, the same thing should work for notes. Updating the source table depends on the method used, but if you update a bound textbox it should update the table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    dbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    So for updating notes my code looks as follows:


    Click image for larger version. 

Name:	db2a.JPG 
Views:	33 
Size:	10.4 KB 
ID:	16545
    The top 1 auto was filling the vendor field perfectly, but after entering the second I now get the following error "The expression After Update You entered as the even property setting produced the following error: Ambiguous name detected: Part_AfterUpdate." Thoughts?

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can't have 2 subs with the same name. Put both lines inside the original sub and delete the second.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is purpose of this form? What table is it bound to?

    Are Vendor and Notes bound controls? Is this duplicating data?

    Code is not needed to simply display the values from the Part combobox. Expression can be in textbox ControlSource.

    =[Part].[Column](1)

    Code is needed if want to save the values to form's record.


    BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be PartNum, PartNo, Part_Num
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    dbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    Ok so last question, If a user fills in the "Notes" field or changes the contents of that field, it should update the table it draws all the above information from?

  14. #14
    dbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    OK forget my last question... I think i have it working!!!
    Thanks for all the help!

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to modify the source table then it must be included in the form RecordSource and textboxes bound to fields of the source table, then edits in the textboxes should be reflected in the source table. However, this is not usually desirable method for editing lookup source tables.

    Do you want to provide db for analysis? Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-12-2013, 01:07 AM
  2. Replies: 3
    Last Post: 03-15-2013, 03:27 PM
  3. Replies: 3
    Last Post: 03-21-2012, 01:43 PM
  4. Replies: 10
    Last Post: 11-21-2011, 02:56 AM
  5. Replies: 5
    Last Post: 01-20-2011, 11:36 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