If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Get data from combo box to popluate the next box
I have a form whose first box is a combo. I enter in a PO and that numbe
appears. I need to have that number automatically go to the next box so it will appear in a different table. PO Number comes off the Order table. I use the PO number to bring up the order's vender and stores the vendors name in the Invoice Table. So to get the PO into the Invoice table I now have to rekey. Is there a way to copy it into the box. Lin -- Lin Light Herrick District Library |
#2
|
|||
|
|||
Hi, Lin.
I'm not clear why the combo box itself is not bound to the appropriate field in the Invoice table, but... To copy a value to a bound control, use the AfterUpdate event of the combo box: Me!YourOtherControlName = Me!YourComboBoxName You do not need the vendor's Name in the Invoice table, by the way. Assuming their primary key field is either stored in Orders, or is related by way of the product, you can get the name through a multiple-table query. Hope that helps. Sprinks "Lin Light" wrote: I have a form whose first box is a combo. I enter in a PO and that numbe appears. I need to have that number automatically go to the next box so it will appear in a different table. PO Number comes off the Order table. I use the PO number to bring up the order's vender and stores the vendors name in the Invoice Table. So to get the PO into the Invoice table I now have to rekey. Is there a way to copy it into the box. Lin -- Lin Light Herrick District Library |
#3
|
|||
|
|||
I don't have a primary key assigned. Will that make a difference in your
instructions? And yes I could and I do have a relationship between tables based on PO, but I have staff that wanted something more, say, tangible. They also wanted the secure feeling that the PO they are entering data for is being charged to the correct vendor. Thanks Lin "Sprinks" wrote: Hi, Lin. I'm not clear why the combo box itself is not bound to the appropriate field in the Invoice table, but... To copy a value to a bound control, use the AfterUpdate event of the combo box: Me!YourOtherControlName = Me!YourComboBoxName You do not need the vendor's Name in the Invoice table, by the way. Assuming their primary key field is either stored in Orders, or is related by way of the product, you can get the name through a multiple-table query. Hope that helps. Sprinks "Lin Light" wrote: I have a form whose first box is a combo. I enter in a PO and that numbe appears. I need to have that number automatically go to the next box so it will appear in a different table. PO Number comes off the Order table. I use the PO number to bring up the order's vender and stores the vendors name in the Invoice Table. So to get the PO into the Invoice table I now have to rekey. Is there a way to copy it into the box. Lin -- Lin Light Herrick District Library |
#4
|
|||
|
|||
One more thing. Could you give a more detailed instructions. Some of the
background coding is a bit fussie to me. LIn "Sprinks" wrote: Hi, Lin. I'm not clear why the combo box itself is not bound to the appropriate field in the Invoice table, but... To copy a value to a bound control, use the AfterUpdate event of the combo box: Me!YourOtherControlName = Me!YourComboBoxName You do not need the vendor's Name in the Invoice table, by the way. Assuming their primary key field is either stored in Orders, or is related by way of the product, you can get the name through a multiple-table query. Hope that helps. Sprinks "Lin Light" wrote: I have a form whose first box is a combo. I enter in a PO and that numbe appears. I need to have that number automatically go to the next box so it will appear in a different table. PO Number comes off the Order table. I use the PO number to bring up the order's vender and stores the vendors name in the Invoice Table. So to get the PO into the Invoice table I now have to rekey. Is there a way to copy it into the box. Lin -- Lin Light Herrick District Library |
#5
|
|||
|
|||
Lin,
I think you have a misunderstanding of some of Access’ features; I’ll try to clear them up as I think they’ll help you resolve your form issues. 1. Primary Keys/Relational Database/Normalization. To harness the power of a relational database, virtually every table should have a primary key. The simplest type, and the one I use almost exclusively, is an AutoNumber. Tables should describe attributes of a single “thing” – such as Customers, Orders, Products, etc. Fields from one table need not and should not be duplicated in another table with the exception of foreign keys, which are fields corresponding to another table’s primary key. Tables thus designed are called normalized. Any Access reference will cover this subject in detail. As an example, the Orders table will include a CustomerNumber field to identify the customer who placed the order. The OrderDetails table will have a ProductNumber field to identify the product ordered. BUT, Orders will NOT include the CustomerName or their PhoneNumber, and OrderDetails will NOT include the ProductName. Foreign keys give you “Access” to any field in the corresponding table by linking the tables in a query by the common field. Take a look at the sample database that comes with Access (Help/SampleDatabases/Northwind). 2. Difference Between What Is “Displayed” and What is “Stored”. Most of your post is concerned with displaying meaningful information to your users, which is great! However, you can display information from another table without redundantly storing it by basing the form on a multi-table query rather than a single table, or you can display information through calculations and function and method calls. For example, take a look at the Orders Form in the Northwind database. It is based on the query Orders Qry, which links the Orders and Customers tables by the CustomerNumber. When the customer is entered on the form via the drop-down combo box, the form displays the address, phone, and other customer information on the form. However, other than the customer number none of this data is stored in the Orders table! 3. Bound and Unbound Controls. A form control (textbox, combo box, checkbox, etc.) is Bound if its ControlSource property is set to the name of a field in the form’s underlying table or query (specified in the form’s RecordSource property). If data is entered into the control, it is then stored in the field to which it is bound. Controls can also be unbound, and their ControlSource could be an expression rather than the name of a field. For example, a control that displays an Extended Price might have the ControlSource: =[UnitPrice]*[Qty] Unbound controls merely display information. 4. Combo Boxes. Combo boxes confused the hell out of me when I started with Access, but they aren’t really very complicated once you understand how they work. They are controlled by several key properties. The RowSource property holds an SQL statement (a query) that selects the data for the rows of the combo box. The ControlSource property is the name of the field into which the selected value is stored once a row is selected. The BoundColumn property is the number of the column to store. Normally, as described above, you will store a foreign key, so in most cases, this will be the first column. The ColumnWidths property is the width in inches devoted to each column in the display. If a Column is set to 0”, it is not displayed at all. For example, to choose a customer for an Orders form, you might have the following property settings: ControlSource CustomerID RowSource Select CustomerID, CustomerName FROM Customers ORDER BY CustomerName; BoundColumn 1 ColumnWidths 0”;4” Because the first column in ColumnWidths is set to 0”, only the CustomerName will display in the drop-down list, but once selected, the CustomerID is what is stored in the underlying table, because the BoundColumn is set to 1. 5. One-to-One Relationships. Although there are exceptions, if a table has a one-to-one relationship with another table, it should be a red flag that you probably don’t need the second table, and its non-redundant fields could simply be added to the first. I think that is probably true in your case regarding the one-to-one relationship between Orders and Invoices. Take a look at the Northwind database, which doesn’t have an Invoices table. Onto your form’s issues. I don’t think you need to copy the PO number to another form control, as you were looking to do in your original post. Simply set its ControlSource to the corresponding foreign key field in the underlying table. To display the vendor name on your form, base your form on a query rather than a table. In Query Design view, link the Orders table to the Customers table by the foreign key CustomerID, and the Orders table to the Vendors table via the VendorID. Then select the fields you’d like to display to the users, including the VendorName that you’d like to show your users. When selecting one of the linking fields, select the one from the Many side of the relationship. For example, for an Orders form, select the CustomerID field from the Orders table not the Customer table. Once you’ve saved the query, set the RecordSource property of your form to the name of the query, and you will be able to place any of the queries fields on your form. If you don’t want the user to be able to change fields from the One Side, which could affect multiple records on the Many side, set the Locked property to Yes or the Enabled property to No. Use the wizard to place a combo box to select an PONumber (to turn Wizards On if it is off, select View, Toolbox and toggle on the button with the stars and magic wand). Tell the wizard to get its rows from the Orders table, select the PONumber field, and tell it to store the value in your corresponding field. As explained earlier, a primary key for all your tables is highly recommended. To add one to an existing table, you’ll have to consider both the One and the Many sides of any relationships. For example, if you’ve been storing the CustomerName field in the Orders table, you will need to add an AutoNumber primary key to the Customers table and a numeric foreign key to the Orders table. Access will assign a number to each customer in the Customers table, but you will need to run an Update query to update the field in the Orders table. Let me know if you need any assistance doing this. Hope that resolves your issues. Sprinks "Lin Light" wrote: One more thing. Could you give a more detailed instructions. Some of the background coding is a bit fussie to me. LIn "Sprinks" wrote: Hi, Lin. I'm not clear why the combo box itself is not bound to the appropriate field in the Invoice table, but... To copy a value to a bound control, use the AfterUpdate event of the combo box: Me!YourOtherControlName = Me!YourComboBoxName You do not need the vendor's Name in the Invoice table, by the way. Assuming their primary key field is either stored in Orders, or is related by way of the product, you can get the name through a multiple-table query. Hope that helps. Sprinks "Lin Light" wrote: I have a form whose first box is a combo. I enter in a PO and that numbe appears. I need to have that number automatically go to the next box so it will appear in a different table. PO Number comes off the Order table. I use the PO number to bring up the order's vender and stores the vendors name in the Invoice Table. So to get the PO into the Invoice table I now have to rekey. Is there a way to copy it into the box. Lin -- Lin Light Herrick District Library |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Data Source issues. ??data.access.pages | Phil | Database Design | 2 | October 11th, 2004 02:42 AM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |
Combo Box Problem | Mr. T | New Users | 15 | July 15th, 2004 09:47 PM |
Combo don't update data in Table | an | Using Forms | 2 | July 6th, 2004 08:51 PM |
Synchronizing Multiple Combo boxes to view matching data on a Form | Mark Senibaldi | Using Forms | 4 | June 16th, 2004 08:48 PM |