Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: Using Query results in a form?

    Hi guys, I think this is only my 2nd time posting here. Please bear with me as I'm very new to MS Access (and SQL/db in fact) and most of what I know are either trial-and-error or thru forum like these.

    Anyways, I have created a table and a query to add a few columns to the table. These additional columns are designed to look up values from the said table and return conditional values. (Hence I needed a query instead of adding these columns in the table itself.)

    Now, I need to create a form based on the table, and which would need to incorporate the query results. But MS Access will not let me use query fields in my form. What can I do?

    I suppose I could use the same lookup formula to create new fields in my form, but I'm trying to simplify things.

    ----
    For those curious to know what data I'm talking about. My db is an insurance module, with data on policies issued and claims experience. The table consists of raw data, one of which field contains the client's "discount level".

    A separate table holds all the claims records. A query will be run to match every policy against its claims record (if any) to find out what level of discount they get to enjoy next year.

    Right now I have already managed to put all these info in the query (a little brutish, but it works for now). But that itself is not the end of the process, because I will need to create a form which will be printed out as "renewal notice" to the clients. Of course, the new discount level (query result) is an integral part of this form.

    If anyone could assist here, that would be awesome.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    "MS Access will not let me use query fields in my form"

    ....this is a fundamental error or misunderstanding

    A form is sourced on a record set. The record set can be a table or a query.

    Use the design wizard to create a form and source it on the query.

    If you need the form sourced on the table and also need columns/fields from a query then you make a main form sourced on one, and a subform sourced on the other....

  3. #3
    Join Date
    Jul 2009
    Posts
    32
    Thanks NTC, I didn't realise that because when I choose "Form Design" it automatically displays all available tables only. But when I used the wizard like you suggested, Queries show up too.

    Well, now I have a second question... slightly related. I have 2 sets of records, quite similar in nature. So after being done with the first set, tables queries forms and all... I want to more or less "duplicate" them. I could duplicate the table fine, but not the form. The new form still looks up values from the first table. Short of having to redesign the form from scratch, is there a shortcut for me to "point it" to the new recordset? (FYI I'm using both records in the same db, not separate)

    **EDIT*
    Nvm, ignore my n00b question, found the command that does it.
    Last edited by andi_kan; 11-25-09 at 02:05. Reason: Found answer
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    I would recommend that you simply right click - copy & paste the form to make a duplicate form with a slightly different name. Then open the form in design view, and in properties - change the record source to the other table.

    Note that you must correct any controls on the form - that have old record source field names that do not match the new record source field names in the field list....

    Then you can toggle between the two forms as needed.

Posting Permissions

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