Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Posting form informatiom into two tables (2000)

    I have a form that is connected to a table. Among other things, the form displays First and Last name from the table. As I fill in the blank text boxes and then click a save button I have, the information is saved or entered into the table. There are a few dropdown boxes on the form that are populated by other tables. Once I pick the desired information from those dropdown boxes, that information is also entered into the original table. What I'm trying to do is save the first and last name to a different table at the same time. For instance, one of the tables connected to one of the dropdown boxes is a table with loner car information. When I select a particular car using the dropdown box, the car information is entered into the original table. I would like the names to be also entered into the first and last name fields in the loner car table. Can you help?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Posting form informatiom into two tables (2000)

    That process is fairly straightforward when you use ADO or DAO, as you simply open a recordset, find the appropriate loner car table and update the information. BUT - it seems to me that you might be better served by having a customer table, a loner car table, and a linking table that indicates that this car is loaned to this customer for this period of time. I'm saying this with little information about your database design, but as a rule of thumb, you never want to store the same information in more than one place. If this doesn't make sense to you, post back and I'll try to clarify.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Posting form informatiom into two tables (2000)

    To be honest, what I'm trying to do is this. In order for the user to pick the right car, they go through a group of cascading combo boxes that filters down to the right car make, car size, car color and car location. The combo boxes filter a subform down to the right car and with the click of a save button, the car information is entered into the main table for that individual. Since there are at times two or more cars with the same information, I would like to add first and last name fields to the subform. The subform gets its data from a table. I added first and last name fields to that table and thought when the actual car information is added or saved to the main table I could also add the names to the table the subform is linked to. That way when the user filters down to a particular car and there is more than one, they could see the ones that already are taken and who has them. I hope that helps.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Posting form informatiom into two tables (2000)

    Well, that's not too different from what I thought you were doing. But why not simply exclude cars that are taken by using a query that only returns cars that are currently free?
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Posting form informatiom into two tables (2000)

    You mean have the subform linked to a query instead of a table. If so, that is what I was originally trying to do, but could figure out how to set the query up so that it displayed all the cars available.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Posting form informatiom into two tables (2000)

    I presume you have a flag that indicates that a car has been loaned but not returned, or something like that. If you create a query that excludes those cars, that would be the data source for your subform. I think you may actually need another table however. That table would look something like this:

    <table border=1><td>CarNum</td><td>A unique number assigned to each car (long integer)</td><td>CustomerNum</td><td>A unique number assigned to each customer (long integer)</td><td>DateLoaned</td><td>The date the car was actually loaned (date/time)</td><td>DateReturned</td><td>The date the car was actually returned (date/time)</td></table>
    You might also want to include other fields such as notes, damage reports, problems found, etc. The field CarNum should point to the Primary Key in the Cars table - the field CustomerNum in the Customers table. Hope this is useful.

    Wendell - Moderator: Access, PowerPoint
    (BTW, we are having some problems with our web site at the moment as we are moving it to a different ISP.)
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Posting form informatiom into two tables (2000)

    For give me for being so slow at understanding, but what I have now is a table that contains all the information about the customers. There is another table that contains all the information about the fleet of cars. That table of cars only holds information on the cars, not any date in or out information. When the user looks for a car for a customer, they use a filtering form that filters a subform down until the right car is seen in the subform. Keep in mind that there is usually more than one car that matches the same description, so after the filtering, there maybe 2, 3 or even 4 cars to choose from. By selecting one of the filtered records from the subform, the filtering form closes and the user is now looking at a form with customer

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Posting form informatiom into two tables (2000)

    It is not a good idea to store customer info in the car table and car information in the customer table; this is redundant, and prone to errors. You should us one table to store this information.

    The best way to store this info would be to have an intermediate table with the structure Wendell proposed; if you don't need the dates, you can omit them. The intermediate table would be linked to the customer table by the cstomer number field, and to the car table by the cr number field, and it would keep track of which customer has which car.
    You could use the Find Unmatched Query wizard to create a query that returns all cars from the car table that have no matching record in the intermediate table, i.e. cars that have not been taken. This query could be the record source of the subform.

    If you want to avoid the intermediary table, you can include a customer number in the car table. If this field is filled, the car is taken. You could create a query based on the car table with Is Null as criteria in the customer number field; this query returns all cars that have NOT been taken yet.

    There is no code needed to transfer information from one table to another in this setup.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Posting form informatiom into two tables (2000)

    If I use your intermediate table suggestion, which I am willing to do, how do I get the car information from the car table and the customer ID number from the customer table into the new intermediate table?

    If I go with your second suggestion, how do I get the customer ID into the car table?

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Posting form informatiom into two tables (2000)

    Since you aren't doing multiple loans of cars, the intermediate table is a bit redundant, as Hans noted:
    <hr>If you want to avoid the intermediary table, you can include a customer number in the car table. If this field is filled, the car is taken. You could create a query based on the car table with Is Null as criteria in the customer number field; this query returns all cars that have NOT been taken yet.<hr>
    Doing that would display the unloaned cars in the subform. Unfortunately, subforms are traditionally used to link to a set of records that belong to an individual rather than your scenario, so I think you are going to need some code to set the value in the cars record. That involves using either DAO or ADO - are you familiar with VBA and recordset processing?
    Wendell

  11. #11
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Posting form informatiom into two tables (2000)

    If you you mean (sql ="Select * FROM...) Statements, I am somewhat familiar, but after trying a few staements on my own, I couldn't get them to work. Can you help me with that?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Posting form informatiom into two tables (2000)

    I have attached a very simple zipped database (Access 97, to reduce size) that uses a slightly different setup (a car number in the customer table) and only a minimum of code.

    The user can inspect and select the available cars from a combo box. There is a bit of code to update the combo box when a car is selected.
    Attached Files Attached Files

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Posting form informatiom into two tables (2000)

    No, SQL is how you create queries, while DAO and/or ADO let you manipulate the results of a query or a table in VBA. But Hans has pointed you in the easiest way to select a car for a customer using a combo box rather than a subform.
    Wendell

Posting Permissions

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