Results 1 to 5 of 5
  1. #1
    sabrown is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    4

    Question Update a field in a table based upon information in anther table?


    I have a table called [Mall Information]. In this table I have two fields called "Mall Number" and "Mall Name". How do I get the "Mall Name" field to auto populate with the correct Name when the corresponding "Mall Number" is selected.

    I have another table in my database called [Mall Numbers] that has the list of each corresponding Mall Number and Mall Name. Is there a way to get the field to look at that table and autopopulate my field in the [Mall Information] table?

    I know how to run an update query, however I'm looking for a way to update the table while you are entering the data without having to close out of the table and run the update query.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Ok the first thing to do is to remove the mall name field from the table. You only need to store the PK from the malls table in the main table as a FK. Then in your query you create a join between the two corresponding fields in both tables and bring down the description in the query. This means that any changes made to the description field will cascade throughout the whole table.

    David

  3. #3
    Ryan is offline Novice
    Windows Vista Access 2003
    Join Date
    Aug 2009
    Location
    Lafayette LA 70592
    Posts
    26

    Lightbulb

    Recently I had to something almost like that. I'll go from scratch, so you will understand what Im saying.

    Make a Table, lets say MALLTable, with the Names / Numbers you want

    Make a Form, lets say : MALLS
    On this form, make a combobox. The combo box will let you pick your table MallTable. Then you can say pick, Mall Number.
    So on your main form, you have a drop down box with all mall numbers.
    (((If you right click the Combobox-you get propertys, it will give you a Comb#, you will need this later. Example: Combo24))) While the property menu is up for the combo box, Click Data tab, Find Bound Column and change the number from 1 to 2.


    Make a Query, with the fields you want from the MALLTable.
    (((We made the combobox pull Numbers from the Table)))

    Edit the Query in design view, you will see there is a line for Criteria. Here is a example of what you would put in. [Forms]![MALLS]![combo24]
    ===The [Forms] is telling the query, the data it needs to find is on a form, then the [MALLS] is the name of the Form, lastely the [combo24] is the name of the Combobox. that you can pick from on the drop down. So when you run this query, your query will look at the Form Mall for the data.

    Now, getting closer to being done. Make a new Form, based on this Query you just made. Make it as a DATASHEET. (call it something so you know what it is for like : MALLSubform)

    Now, open the main form MALL. Insert SubForm, and pick from existing forms. Pick the Form you Just made (MALLSubform).

    While your editing the Main Form, Click Command button, Make the command run the query you made.

    Done, with options to add.

    If you right click the Command button you made, it has filter/Sort on it. It will display everything on the page you have (SubForm, on the main form)
    The query may pop open a new window. If right clicking is annoying, it is to me. Make a macro that runs the command, Show All Records. Once you make a macro, you can go back to the Main Document, Make a new command button, and make it run the macro instead.

    Each time you load your Main form, pick from the drop down list. Run the query, or macro for ShowAllRecords. And it should display on the Subform.

    ONLY the ones with the DATA you selected will appear. For example, you have 40 malls, and each had a number. You picked 37, Only the data for mall 37 will show up!

    I know its lengthy, but I had a hard time grasping it with shorter versions, hope this helps!!!!

    ***Wow, i posted this on the wrong thread. Sorry bout that.
    Last edited by Ryan; 09-03-2009 at 10:35 AM. Reason: wrong thread

  4. #4
    sabrown is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    4
    Ryan - When I create my query and run it it says "Enter Parameter Value" for the field that I'm using to look up the Form.

  5. #5
    Ryan is offline Novice
    Windows Vista Access 2003
    Join Date
    Aug 2009
    Location
    Lafayette LA 70592
    Posts
    26
    Thats actually good. You should run the query from the Form.
    That drop down box, with the Mall Data is what it is looking for. If you enter a number in the parameter box, then you should get a result. (The number you should enter, would be one of the items on that drop down box-Which is pulling from that table/ like Mall #(number))


    Run the Query from the Form, and what results do you get on the subform?
    It should be all the data with the Mall # you selected.

    Just Remember, the Query is set to run From that form.
    If you run it outside the Form, it is wanting to know the mall number, if thats what you selected for the combo box.

    Hope that helps!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-30-2013, 11:42 AM
  2. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 PM
  3. Replies: 1
    Last Post: 03-08-2009, 01:50 PM
  4. how to update only one field in 1 table?
    By viccop in forum Access
    Replies: 3
    Last Post: 02-21-2009, 02:32 PM
  5. Update table from calculated field in a form
    By BernardKane in forum Forms
    Replies: 3
    Last Post: 11-28-2006, 09:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums