Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Albuquerque, NM
    Posts
    4

    Unhappy Unanswered: Subforms and Control Boxes

    I am relatively new to access and am trying to create a database to suite my customer's needs... but I'm having some trouble.

    I would like to use a subform within the regular form. I want to create a subform so the user can enter more than one value in the field for each entry. I.E. - Each entry is information about a specific test. Each field in the entry specific information about the test (where, when, who, what was tested). I want the subform to allow multiple instruments to be used for each test (entry into the database).

    The user wants to be able to select from a list or type in a new instrument for each instrument used. There can be as many as 20 instruments used in a single test (entry into the database).

    I was told a control box will allow the user to chose from a list or enter their own value. Will the control box then remember what was entered in the next time they want to use that entry? (the goal is to make it fast - so the user doesn't have to type in the instrument used every time).

    I hope I am making sense....

    To sum it up:
    I want to have a subform with a control box that will allow the user to enter multiple entries into the subform. I want the user to be able to enter information by chosing from a list or by typing in their data.

    1. Is this possible?
    2. How can I do this?

    (I've tried to do this... but I must be doing something wrong) (I'm not very familiar with using SQL... so I'm going to hope I don't have to... but I'll learn if it must be done with SQL)

    Thank you for any help you can give me!

    Vexed

  2. #2
    Join Date
    Feb 2004
    Posts
    3

    Re: Subforms and Control Boxes

    I don't think you're going to be able to use simple text list for the source of your combo box.

    You'll need to put the instrument choices in a table and set the combo box data source to the table or a query based on it.

    To add a new entry based on what a user types in, you'll need to add code to the NotInList Event on the combo that adds their entry to the table and requeries the box.

    If you want it to remember a certain user's instrument choices, you'll have to construct your instrument table so that it references users, and also build that into the query that feeds your combo box.

    It sounds like a lot, but each piece of the puzzle should be pretty easy. I'd start with getting the data relations between users, instruments, and tests worked out, build a query on top of that, then build the basic combo box based on that query, then add the code to accept the new entry and add it to the instrument/user table.

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Subforms and Control Boxes

    Originally posted by Vexed
    I am relatively new to access and am trying to create a database to suite my customer's needs... but I'm having some trouble.

    I would like to use a subform within the regular form. I want to create a subform so the user can enter more than one value in the field for each entry. I.E. - Each entry is information about a specific test. Each field in the entry specific information about the test (where, when, who, what was tested). I want the subform to allow multiple instruments to be used for each test (entry into the database).

    The user wants to be able to select from a list or type in a new instrument for each instrument used. There can be as many as 20 instruments used in a single test (entry into the database).

    I was told a control box will allow the user to chose from a list or enter their own value. Will the control box then remember what was entered in the next time they want to use that entry? (the goal is to make it fast - so the user doesn't have to type in the instrument used every time).

    I hope I am making sense....

    To sum it up:
    I want to have a subform with a control box that will allow the user to enter multiple entries into the subform. I want the user to be able to enter information by chosing from a list or by typing in their data.

    1. Is this possible?
    2. How can I do this?

    (I've tried to do this... but I must be doing something wrong) (I'm not very familiar with using SQL... so I'm going to hope I don't have to... but I'll learn if it must be done with SQL)

    Thank you for any help you can give me!

    Vexed
    It sounds like you are on the right track. Three tables is what I would recommend. Table 1 is the Test table with the Test info. Table 2 is the Instrument table that has the Instruments listed. Table 3 contains the Test number and the instrument number or name or whatever you are using to identify the instrument. This table should be linked to each of the other tables with a one to many relationship.

    Ultimately, you will be displaying the Test info on the main form and the information from the Linking table on the subform. This allows you to add as many instruments to a particular test as you want and add the same instruments to multiple tests. You can use combo box controls to allow the selection of instruments for each test.

    If you are lost at this point, you might want to do a little more research but it's pretty easy to set up once you get the hang of it.

    Hope this gets you started and doesn't confuse you more.

    Gregg

  4. #4
    Join Date
    Feb 2004
    Location
    Albuquerque, NM
    Posts
    4

    Question Almost got it.... I hope... maybe

    Ok.... I think I almost understand this.

    This is what I've done so far:

    I created a joining table so I can connect the Test table to the Instrument table.
    I created the relationships so there is a many to many relationship.

    From here I get a little confused. I think I'm supposed to creat a query and then have the combo box in the subform of the main table pull information from the query that will then give the instrumentation information... but I'm not sure what type of query to make. I have found that there are many different types... Also, I'm not sure what to include in the query: just the instruments, or more?
    I tried creating a simple query... but I definately did something wrong... cause that didn't get me any further than I was before I got all this help. (Thanks for the help by the way!)

    So... what do I do now?
    What type of query with what fields in it do I creat? Do I then need to creat a form based on the query and have that form be the subform for the main form and table?

    Still a bit
    Vexed

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Almost got it.... I hope... maybe

    Originally posted by Vexed
    Ok.... I think I almost understand this.

    This is what I've done so far:

    I created a joining table so I can connect the Test table to the Instrument table.
    I created the relationships so there is a many to many relationship.

    From here I get a little confused. I think I'm supposed to creat a query and then have the combo box in the subform of the main table pull information from the query that will then give the instrumentation information... but I'm not sure what type of query to make. I have found that there are many different types... Also, I'm not sure what to include in the query: just the instruments, or more?
    I tried creating a simple query... but I definately did something wrong... cause that didn't get me any further than I was before I got all this help. (Thanks for the help by the way!)

    So... what do I do now?
    What type of query with what fields in it do I creat? Do I then need to creat a form based on the query and have that form be the subform for the main form and table?

    Still a bit
    Vexed
    You don't explicitly need to create a query for the subform. You can use the linking table as the source of data. When you are creating your subform, don't drag any of the fields from the field list into the form. First make sure that the subform and main form are linked together with the LinkMasterFields and LinkChildFields properties. Now, add a combo box to the form. In the properties sheet for the combobox select the RowSource and click the elipse to the right. Make sure RowSourceType is Table/Query. When you see the QBE pane, put the Instruments table into the window. Select the InstrumentID and the Instrument name in the grid. I'm assuming that you have an ID and a description field for this. If you don't then I'll assume that the linking table also does not have an ID but only a text description for the instrument. If this is the case, just add the description field to the grid.

    Close the QBE and save the Rowsource for the combobox. You'll be prompted. You don't want to necessarily save it as a standalone query, just as the RowSource for the combo.

    Now, make sure that the ControlSource for the combo is set to the field that you want the data to go into. It's now a matter of selecting the number of columns and the column widths that allow you to display what you want. As long as the bound column matches the value you want to save in the Link table, the display is just for how you want it to look.

    You don't actually have to display, or even have on the form, the linking field between the two forms. Since it is linked, it will be added automatically each time you add a new instrument to the form.

    Let me know if this is enough or if you need further clarification. If you select a property, in the porperties window and press F1, you will get context sensitive help if you need it.

    Gregg

  6. #6
    Join Date
    Feb 2004
    Location
    Albuquerque, NM
    Posts
    4

    Question Re: Almost got it.... I hope... maybe

    I'm working on this.... I think the directions you gave me will be enough, but I won't know until I'm done. One question, though:

    What is a QBE pane?

    Thanks!

    Vexed (hoping not to be much longer)

  7. #7
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Almost got it.... I hope... maybe

    Originally posted by Vexed
    I'm working on this.... I think the directions you gave me will be enough, but I won't know until I'm done. One question, though:

    What is a QBE pane?

    Thanks!

    Vexed (hoping not to be much longer)
    Sorry. Hard to know how much someone is familiar with in the Access environment. QBE is Query By Example. It's what you create most of your queries in in Access. It's hopefully what you will see when you click the elipse to the right of the rowsource property.

    Let us know if you need any more help.

    Sounds like you about got though.

    Gregg

Posting Permissions

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