Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Linking Equations in Queries to Combo Boxes

    Hi All-

    I am new at Microsoft Access and my internship currently has taken a turn to include database management, of which I have limited experience. The project I am currently working on hopes to have a main form (or report im not picky) with subforms and queries that it calls upon to populate the corresponding fields. More specifically we are creating a Pavement Management database for a municipality which will be able to rate sections of roads for a pavement schedule through mathematical equations. I will have a list of criteria that I will be collecting in the field and each value (i.e. low, medium, high) corresponds to a numerical value which can be chosen by a combo box. This chosen numerical field will then, hopefully, be added to an mathematical equation to generate an index value. Is this possible? If so what is the best way to approach this issue?

    I have already created a form that is populated with equations in text boxes that call upon the combo box selection and works beautifully, but I have since read that all equations should be housed in a separate location and called upon via queries.

    I have attached the form I mentioned above that is working, with all of the math and selection within one form. I would like to replicate this using a separate query that can be called upon to populate the fields with numerical indice values. Thanks in advance for help.
    Attached Thumbnails Attached Thumbnails AccessForm.jpg  
    Last edited by natemietk; 06-06-11 at 11:27.

  2. #2
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Possibilities

    The easiest way is to calculate the fields within the query:

    aCalculatedFireld: [field] + [field2] + [field3]....

    You can probably also create functions in the module, and call them from the query (or the form/report; take your choice).

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    Praxis-

    Thank you for replying to my post, and I would love to call the equatios from a query but it never seems to work out. Whenever I call upon the variables from the combo box selection the equation cannot locate the value for that field. Meaning that the variable cannot be located making the equation variable flagged as an unknown reference error. That is one of the reasons why I though VB would have been a better alternative due to the query being unable to locate the variable within the combo box selection.

    You mentioned running the equation through a macro. I also tried this without any success. I am almost positive it is due to my lack of knowledge as far as syntax is concerned. I was getting confused on how to call upon the combo box selection, running the equation and then linking the equation with the text box so the user can see the answer within the macro form. Any ideas in how to construct the macro?

    thank you

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    To reference the contents (Value) of a control from a Query, use:
    Code:
    Forms!<FormName>.<ControlName>.Value
    or
    Code:
    Forms("<FormName>").Controls("<ControlName>").Value
    The form <FormName> must be open as closed forms are not member of the Forms collection.

    For more information about referencing forms and subforms, see: http://access.mvps.org/access/forms/frm0031.htm
    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
  •