Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Unanswered: Using one Combo Box to fill out text boxes and cascading into a Combo Box

    Hello, this is my first post, I am trying to give as much information/background as possible, my actual problem won't be described until further down. I am using Microsoft Access 2007.

    I am trying to create a form for my company's salesmen to fill out new sales orders. The goal is for the salesman to fill out as little information as possible and then to have necessary fields filled in automatically.

    Right now I have one table called tblMachine. It's fields are Machine, Solenoid, Brake, Dec, OT and Instruction.

    I started making my form. And have one combo box, cboMachine and four text boxes, txtSolenoid, txtBrake, txtDec, txtOT and txtInstruction.

    Right now, I have it set up so that you use cboMachine to select the Machine you want, and then it'll automatically fill the four text boxes.

    I set up cboMachine with a row source that is below, the column count is 6, and the column widths are 1";0";0";0";0";0";

    Row Source: SELECT Machine, Solenoid, Brake, Dec, OT, Instruction FROM tblMachine ORDER BY Machine;

    I set up the text boxes with a control source (column numbers go from 1-5):
    =[cboMachine].[Column](1)

    My question is: Is there any way to set up the combo box so that it'll fill the four text boxes and cascade into another combo box?

    What I want is for the salesman to select the Machine, have the 4 text boxes filled, and then have the salesman select the Machine model from a list of Models which will change depending on the Machine they choose.

    For example if this was for cars, the user would select a manufacturer (Honda), then some manufacturer info would be filled in, such as country (Japan), date they started, etc.. and then the user would be able to select the Honda models like Acura, Civic, Fit, CRV, etc. If the user chooses Ford, the information would change and the list of models would be different.

    What would be the best way to set this up? I know how to cascade combo boxes, but I only know how to cascade one combo box to another. Would I make another table relating the machine and models and use that? Any help here would be appreciated.

    Also I would like to make it so that the text boxes are editable in case the Salesman do not know Machine. For example if they choose Not Specified in the Machine combo box, Salesman can still go and enter in the information they know into the text box.

    Any help would be greatly appreciated, sorry about the length.

    Thanks

  2. #2
    Join Date
    Dec 2009
    Location
    Lake County, IL
    Posts
    8

  3. #3
    Join Date
    Dec 2011
    Posts
    3
    Hey Alan,

    Thanks for the response. I have already taken a look at that site and know how to cascade one combo box to another. I need to be able to do cascade one combo box to another and fill out text boxes at the same time.

    If you know how to do that, that would be great.

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. A combobox can have several columns. When a value is selected, you can retrieve the value of any column of the selected row using the syntax:
    Code:
    Me.ComboX.Column(n)
    where n is a numeric value (or a variable containing a numeric value) between 0 (leftmost column) and the number of columns in the combo -1. So for instance:
    Code:
    Me.Text2.Value = Me.Combo1.Column(3)
    If, for any reason, you cannot use this technique, you can use a domain function (usually DLookUp) to retrieve the values you need to fill the textboxes from a table or a query, using the value selected in the combobox as a criteria (filter).

    If the value in the combo is numeric:
    Code:
    Me.Text2.Value = DLookUp("ColumnName", "TableName", "OtherColumnName = " & Me.Combo1.Value)
    If the value is a string (text):
    Code:
    Me.Text2.Value = DLookUp("ColumnName", "TableName", "OtherColumnName = '" & Me.Combo1.Value & "'")
    If the Value property of the combobox can be Null, you'll have to test it first (IsNull function) or use the Nz function to convert it to a non-Null value before using it for assembling the criteria.
    Have a nice day!

  5. #5
    Join Date
    Dec 2011
    Posts
    3
    Thank you for the response.

    Sorry, I am still new to using Access. Where am I putting this code? Into the text box value? Is there any way to make it so that the text box is still editable by the user? If the user for example, does not select anything in the combo box, but knows some of the text box field values, could he input them in manually?

    Also will I then be able to put in an after update event on the combo box to cascade into another combo box?

    Thank you for the help!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As the value that must be inserted in the textboxes depends on the value selected in the combobox and probably must change when the value of the combobox is changed, one normal place where to use the code is in the AfterUpdate event handler of the combobox.

    There can be other considerations to take into account, such as whether the controls or some of them are bound to the form Recordset, if this form is bound to a data source. None of these would prevent the user from manually changing the values in the textboxes, provided that integrity constraints are no violated, if some are defined.
    Have a nice day!

Posting Permissions

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