Results 1 to 15 of 15
  1. #1
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Autofill ?

    Hi,



    Thanks to those who have helped me in the past. I am still trying to find the best (easiest) way to auto fill three fields on a form created from linked to a table called called StudentEnrollmentTable. The form asks the user to slect a ClassNumber from a combo box (from CourseInventory table) and upon selction, I would like to it autofill the CourseName, Instructor and Section. It would pull these item from the table called CourseInventory also.

    Thanks so much.

    Take care,

    Daryl

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    In your lookup, it is generated by a SQL statement in the row source of the properties for combo box. Look at the query and the columns in Access are represented by numbers from left to right 0, 1, 2, 3 etc.

    In the textboxes you want to fill, type =comboxname.column(x) where x is the column number for the value you want in the text box and comboboxname is the name assigned to the combobox control.

    Alan

  3. #3
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Works for one of three

    Hi Alan,

    Thnaks so much for your reply. That is really easy. For some reason it is working for one of the text boxes but not the other two. I have look at the property sheet and anything else I know of to find a possible reason, but with no luck. Any suggestions?

    Take care,

    Daryl

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Really need to see your file to determine any thing else. Suggest you post with sample data.

  5. #5
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    File too large to attach

    Hi,

    I am really new at access and this fourm. Is there anyway I can send you only the form required. Will that be enough?

    Take care,

    Daryl

  6. #6
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Suggest you only send file with sample data. Run a compact and repair and then zip it. It should fit then. Also just noted that you are using 2010. Should probably save file to upload as an earlier version 2003 or earlier if you want maximum visibility.

  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 it's working for some and not others, my guess is that the column count property of the combo is incorrect. What are the row source and column count properties?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Zipped File

    Thanks so much for your instructions. Here is the zipped file.

    Take care,

    Daryl
    Attached Files Attached Files

  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
    I couldn't open it with 2007, so I assume you missed this:

    Quote Originally Posted by alansidman View Post
    Should probably save file to upload as an earlier version 2003 or earlier if you want maximum visibility.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    2007?

    Hi,

    Thanks for that. I didn't notice it until after I posted. I change the extension and re-zipped. Hope it will open.

    Take care,

    Daryl

  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
    Changing the extension doesn't affect the file format (at least my computer still won't open it). You have to use the "Save As" function to save it in a different format.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    saving as 2007

    I'm stumped. My 2010 access save-as dialogue box does not offer any earlier versions of access to save the file as. I'll keep looking.

    Thanks everyone for your patience.

  13. #13
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

    Save as .mdb


  14. #14
    troggernaught is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    5

    My first post on accessforums.net!

    Just to be clear...the boxes that you want to be autofilled are actually fields in your StudentEnrollmentTable and when you select a course number, you want data that comes from the CourseInventory table to go in those.

    A calculated control (anything where the Control Source begins with "=") will not put any data into your StudentEnrollmentTable. It will just find the corresponding record in your CourseInventory table and display it on the form when the form is open. In other words, it calculates the value that it shows on-the-fly. If you look at your table, the "autofilled" data will not be there.

    To actually fill in data, you need to bind the textboxes to your fields in the StudentEnrollmentTable. For example, the Control Source for CourseName needs to be "CourseName". To autofill these boxes, you need to write a macro for the ClassNumber After Update event. You should use SetValue in the macro for each textbox you want to autofill.

    Also, the ClassNumber textbox needs to include all the columns you intend to use for autofilling. In this case, the Column Count property should be set to 5. When referencing the columns in your macro, remember that it is zero-based, so the first column's value would be obtained by [ClassNumber].[Column](0) for example. To autofill CourseName, LevelSection, and Instructor, your macro should look like this to match the column order of your CourseInventory table:
    SetValue [CourseName],[ClassNumber].[Column](2)
    SetValue [LevelSection],[ClassNumber].[Column](3)
    SetValue [Instructor],[ClassName].[Column](4)

    I attached a revised database with the above changes.

  15. #15
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Thank You

    Hi Everyone,

    Thanks for all your help. The macro worked beautifully. I leaned much through this thread.

    Take care,

    Daryl

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

Similar Threads

  1. Autofill
    By evosheas in forum Access
    Replies: 4
    Last Post: 09-20-2011, 02:29 PM
  2. Having trouble with autofill
    By Kerrydunk in forum Forms
    Replies: 1
    Last Post: 04-25-2011, 11:26 PM
  3. Yet Another autofill question
    By srcacuser in forum Database Design
    Replies: 1
    Last Post: 01-29-2011, 11:05 AM
  4. Autofill
    By kdcooper88 in forum Access
    Replies: 1
    Last Post: 09-18-2010, 05:52 AM
  5. ComboBox Autofill
    By t_dot in forum Forms
    Replies: 2
    Last Post: 08-19-2010, 06:18 AM

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