Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    8

    Unanswered: Autofilling a combobox based on contents of another box...

    I currently have a form that a user uses to enter a student's community service projects into the Project's Table (which has a many-to-one relationship with the student table which holds the constant, non changing data for students e.g. Student ID, Last name, first name, graduation year). the form contains the following labels/[comboboxes] in this sort of pattern:

    Student ID: [StudentID]<-------(the primary key)
    Last Name: [LastNameID]
    First Name: [FirstNameID]
    Graduation Year: [GraduationYearID]
    Project Year
    Project Name
    Project Description
    Project Date
    Hours Completed

    I currently have the form set up so that when a student's student ID is entered in the [StudentID] combobox that when he goes to the last name and first name combo boxes the options he can enter are limited for him (through use of the "[Forms]![Enter New Project Form]![StudentID]" type formula), but he still has to manually type in the data for each of the comboboxes even though each only has one option (as the student Id is bound to one student with one last name and one first name and one graduation year).

    So I was wondering if there was any way to by selecting a student Id then the values for first name, last name, and graduation year could all automatically be put into the combo boxes as they are all constants and never change. Is this possible?

    Any help would be greatly appreciated

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Is there a reason why you use comboboxes instead of textboxes?
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Posts
    8
    basically just to make it easier for the user, so it could be easily changed if needed. If I changed the box to a text box that could be automatically filled then there really is no problem in changing it as it would be just as convenient.

    So how could I automatically fill a textbox based on what is entered in the combo box?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    in the afterupdate of the combobox....

    me!MyTextBox = me!MyCombobox
    or
    me!MyTextbox = me.MyCombobox.column(X) where X = the column.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Apr 2010
    Posts
    8
    Thanks for the reply

    Just wondering is the example code you gave me used completely as is except for the column part, or do I need to change names/types of boxes accordingly?

    So in the case that I gave in the first post would I put the code in the after update of the [StudentID] combo box?

    Sorry for my questions, I'm a novice at all this code stuff

  6. #6
    Join Date
    Apr 2010
    Posts
    8
    All right I changed the First name, last name, and graduation year combo boxes to text boxes and tried the code that you gave in the code builder in the after event box in the Student ID combo box.

    I got the

    Code:
    me!MyTextBox = me!MyCombobox
    code to work so that every time I enter the student Id then that student ID is automatically entered in each of the text boxes.

    I tried using the

    Code:
    me!MyTextbox = me.MyCombobox.column(X)
    changing x for the various column numbers but I can't seem to get it to work, as of now it leaves the last name, first name, graduation year text boxes blank

    This is the code that I'm currently using:

    Code:
    Private Sub StudentID_AfterUpdate()
    Me!LastNameID = Me.StudentID.Column(2)
    Me!FirstNameID = Me.StudentID.Column(3)
    Me!GraduationYearID = Me.StudentID.Column(4)
    End Sub
    and this is the sql of the query the combo box uses:

    Code:
    SELECT [Base table].[Student ID], [Base table].[Last Name], [Base table].[First Name], [Base table].[Graduation Year]
    FROM [Base table]
    with StudentID being column 1, last name column 2, first name column 3, graduation year column 4.

    I'm probably making a simple mistake here, any ideas?

    Any help would be greatly appreciated!

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Check the column count property of your combobox. If you don't want to show the actual columns but use them, then for the column widths of that combobox you can enter something such as: 1";1";0";0" but the column count should be 4.

    But you have to make sure the column count matches the columns you have in the query. If your column count is 1 of the combobox, it will only recognize the 1st column only and not the others.

    Remember that me!MyCombobox is actually the 1st column in the rowsource query of the combobox while me.mycombobox.column(1) is the 2nd column, etc...etc..
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Apr 2010
    Posts
    8
    That worked wonderfully, thank you for the help!

    Everything seems to be working well, thanks again!

Posting Permissions

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