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
|
|||
|
|||
Displaying Additional Columns in Access Form
I have a form based off of a trouble ticket table. In the form, I have a
combo box that is currently allowing a person to select the customer from a drop down list of customer names. The list to this box is coming from another table within the database named "customers" and the customers table is linked to the trouble ticket table by customer ID. The customers table also contains maintenance status and expiration date columns. What I want to know is if there is a way for the maintenance status and expiration date to show up on the form when a certain customer is selected. For example - if customer ABC is selected from the drop down list of customer and their maintenance status is "current" and expiration date is "1/1/2006", is there a way to display (in 2 additional boxes on the form) the word "current" and the date "1/1/2006" any time someone picks customer ABC from the drop down list for customer when entering a trouble ticket? The technicians do not know the maintenance status and expiration of the customers and I need those two columns from the Customer table to populate into seperate boxes under the drop down list for Customers. This way, any time they enter a trouble ticket and selecting a customer, those two boxes will populate the maintenance status of the customer so the tech knows. I had one reply before (listed below) and when I followed those coding instructions, it showed all 3 columns (company name, maint, expiration) all in the drop down list for the Customer, but when you selected a certain customer, the only thing visible was the company name. I just want to get the maintenance and expiration to populate into 2 other boxes when a certain company is selected from the drop down menu. Is this possible and if so, how do I accomplish it? ORIGINAL ANSWER: The Row Source property of the combo box on the Form should be a Query selecting the customer information. I'm suggesting that that query include the two fields which you want to see. For example, the RowSource property of the combo could be SELECT CustomerID, CustomerName, Status, ExpirationDate FROM Customers ORDER BY CustomerName; The Combo's ColumnCount property would be 4 to include all fields; its ColumnWidths property would be 0;1.5;0;0 to display the customer name and hide all the other fields. The Bound Column would be 1 so that the CustomerID is bound to your trouble ticket's CustomerID field. A Combo Box has a "Column" property which lets you extract information other than the bound column's value from the combo. If your combo is named cboCustomer, then simply look at the Control Source property of the textbox on the form in which you wish to display the status, and type in =cboCustomer.Column(2) This will automatically display the third (zero based, remember) field from the combo - the status, using the example above. John W. Vinson[MVP] |
#2
|
|||
|
|||
There are a couple of ways. The easiest is probably to add the other two
fields as additional columns to the combo box. Set the width of those two fields to zero so that they don't show in the combo box. Add two textboxes to the form and set their Control Sources to point to these other two columns. Example: =cboMyCombo.Column(2) This would give you the value in the third column of the combo box. The index number is zero based, so 2 is the third column, 3 is the fourth, etc. -- Wayne Morgan MS Access MVP "Roxanne" wrote in message ... I have a form based off of a trouble ticket table. In the form, I have a combo box that is currently allowing a person to select the customer from a drop down list of customer names. The list to this box is coming from another table within the database named "customers" and the customers table is linked to the trouble ticket table by customer ID. The customers table also contains maintenance status and expiration date columns. What I want to know is if there is a way for the maintenance status and expiration date to show up on the form when a certain customer is selected. For example - if customer ABC is selected from the drop down list of customer and their maintenance status is "current" and expiration date is "1/1/2006", is there a way to display (in 2 additional boxes on the form) the word "current" and the date "1/1/2006" any time someone picks customer ABC from the drop down list for customer when entering a trouble ticket? The technicians do not know the maintenance status and expiration of the customers and I need those two columns from the Customer table to populate into seperate boxes under the drop down list for Customers. This way, any time they enter a trouble ticket and selecting a customer, those two boxes will populate the maintenance status of the customer so the tech knows. I had one reply before (listed below) and when I followed those coding instructions, it showed all 3 columns (company name, maint, expiration) all in the drop down list for the Customer, but when you selected a certain customer, the only thing visible was the company name. I just want to get the maintenance and expiration to populate into 2 other boxes when a certain company is selected from the drop down menu. Is this possible and if so, how do I accomplish it? ORIGINAL ANSWER: The Row Source property of the combo box on the Form should be a Query selecting the customer information. I'm suggesting that that query include the two fields which you want to see. For example, the RowSource property of the combo could be SELECT CustomerID, CustomerName, Status, ExpirationDate FROM Customers ORDER BY CustomerName; The Combo's ColumnCount property would be 4 to include all fields; its ColumnWidths property would be 0;1.5;0;0 to display the customer name and hide all the other fields. The Bound Column would be 1 so that the CustomerID is bound to your trouble ticket's CustomerID field. A Combo Box has a "Column" property which lets you extract information other than the bound column's value from the combo. If your combo is named cboCustomer, then simply look at the Control Source property of the textbox on the form in which you wish to display the status, and type in =cboCustomer.Column(2) This will automatically display the third (zero based, remember) field from the combo - the status, using the example above. John W. Vinson[MVP] |
#3
|
|||
|
|||
Wayne,
Right now I have the following on the Form: My combo box has a name of 'Customers' and shows the following for the Row Source: SELECT Customers.CustomersID, Customers.CompanyName, Customers.MaintenanceStatus, Customers.Expiration FROM Customers ORDER BY CompanyName; I have made the column count 4 and when I put in widths of 0";1.975;1.5";1.5 - the combo box itself will show the customer selected but I can see the other two columns when going to select a customer. Now, taking the widths back down to 0';1.975";0";0", I return to just seeing the customer name in the drop down list (so all is ok at this point). I then added one text box and attempted to set the Control Source to column 2 as you described below, but I get an error that the expression is not right. When looking at the form in "view" the text box shows "Name?" and it doesn't populate the maintenance status from the customers table. A couple questions I have are (1)I am using Access 2003 - is the name of my combo box "Customers" - any time I input cbo like you show below nothing works. (2)Is there something else I need to be setting in the properties of the text box so that the field will populate once a certain customer is picked in my drop down list? (3) Is there a way to just get all 3 columns to show up on the form from the combo box itself? When I adjust out the widths of the other 2 columns in the properties and make the actual box as wide as needed to show all the columns, only the customer name appears in the box (even though when you select the drop down arrow, you can scroll over and see the other columns and their values for each customer)? Thanks, Roxnne "Wayne Morgan" wrote: There are a couple of ways. The easiest is probably to add the other two fields as additional columns to the combo box. Set the width of those two fields to zero so that they don't show in the combo box. Add two textboxes to the form and set their Control Sources to point to these other two columns. Example: =cboMyCombo.Column(2) This would give you the value in the third column of the combo box. The index number is zero based, so 2 is the third column, 3 is the fourth, etc. -- Wayne Morgan MS Access MVP "Roxanne" wrote in message ... I have a form based off of a trouble ticket table. In the form, I have a combo box that is currently allowing a person to select the customer from a drop down list of customer names. The list to this box is coming from another table within the database named "customers" and the customers table is linked to the trouble ticket table by customer ID. The customers table also contains maintenance status and expiration date columns. What I want to know is if there is a way for the maintenance status and expiration date to show up on the form when a certain customer is selected. For example - if customer ABC is selected from the drop down list of customer and their maintenance status is "current" and expiration date is "1/1/2006", is there a way to display (in 2 additional boxes on the form) the word "current" and the date "1/1/2006" any time someone picks customer ABC from the drop down list for customer when entering a trouble ticket? The technicians do not know the maintenance status and expiration of the customers and I need those two columns from the Customer table to populate into seperate boxes under the drop down list for Customers. This way, any time they enter a trouble ticket and selecting a customer, those two boxes will populate the maintenance status of the customer so the tech knows. I had one reply before (listed below) and when I followed those coding instructions, it showed all 3 columns (company name, maint, expiration) all in the drop down list for the Customer, but when you selected a certain customer, the only thing visible was the company name. I just want to get the maintenance and expiration to populate into 2 other boxes when a certain company is selected from the drop down menu. Is this possible and if so, how do I accomplish it? ORIGINAL ANSWER: The Row Source property of the combo box on the Form should be a Query selecting the customer information. I'm suggesting that that query include the two fields which you want to see. For example, the RowSource property of the combo could be SELECT CustomerID, CustomerName, Status, ExpirationDate FROM Customers ORDER BY CustomerName; The Combo's ColumnCount property would be 4 to include all fields; its ColumnWidths property would be 0;1.5;0;0 to display the customer name and hide all the other fields. The Bound Column would be 1 so that the CustomerID is bound to your trouble ticket's CustomerID field. A Combo Box has a "Column" property which lets you extract information other than the bound column's value from the combo. If your combo is named cboCustomer, then simply look at the Control Source property of the textbox on the form in which you wish to display the status, and type in =cboCustomer.Column(2) This will automatically display the third (zero based, remember) field from the combo - the status, using the example above. John W. Vinson[MVP] |
#4
|
|||
|
|||
1) The name is of the combo box is on the Other tab of the Properties sheet
when you have the form open in design mode and the combo box selected. It should also be what shows in the drop down of the Properties sheet. You will need to change the name I used to the correct name for your combo box. If the combo box and the field it is bound to (Control Source of the combo box) have the same name, you may have a problem. If so, change the name of the combo box by putting cbo in front of it. You will also need to change the name of any procedures in the form's code module that may use the old name and in any queries or other forms or reports that may have referred to the combo box. 2) As long as the textbox and combo box are on the same form, the textbox will update automatically as the combo box changes. If it is a parent form/subform setup and one is on the parent and the other on the subform then something else will need to be done. 3) Yes, you could change the Row Source of the combo box to have it concatenate the desired fields together into one field. They will then show up in the combo box itself after a selection has been made. Example: SELECT Customers.CustomersID, Customers.CompanyName & ", " & Customers.MaintenanceStatus & ", " & Customers.Expiration FROM Customers ORDER BY CompanyName; If you do this, the combo box will only be a 2 column box. -- Wayne Morgan MS Access MVP "Roxanne" wrote in message ... Wayne, Right now I have the following on the Form: My combo box has a name of 'Customers' and shows the following for the Row Source: SELECT Customers.CustomersID, Customers.CompanyName, Customers.MaintenanceStatus, Customers.Expiration FROM Customers ORDER BY CompanyName; I have made the column count 4 and when I put in widths of 0";1.975;1.5";1.5 - the combo box itself will show the customer selected but I can see the other two columns when going to select a customer. Now, taking the widths back down to 0';1.975";0";0", I return to just seeing the customer name in the drop down list (so all is ok at this point). I then added one text box and attempted to set the Control Source to column 2 as you described below, but I get an error that the expression is not right. When looking at the form in "view" the text box shows "Name?" and it doesn't populate the maintenance status from the customers table. A couple questions I have are (1)I am using Access 2003 - is the name of my combo box "Customers" - any time I input cbo like you show below nothing works. (2)Is there something else I need to be setting in the properties of the text box so that the field will populate once a certain customer is picked in my drop down list? (3) Is there a way to just get all 3 columns to show up on the form from the combo box itself? When I adjust out the widths of the other 2 columns in the properties and make the actual box as wide as needed to show all the columns, only the customer name appears in the box (even though when you select the drop down arrow, you can scroll over and see the other columns and their values for each customer)? Thanks, Roxnne "Wayne Morgan" wrote: There are a couple of ways. The easiest is probably to add the other two fields as additional columns to the combo box. Set the width of those two fields to zero so that they don't show in the combo box. Add two textboxes to the form and set their Control Sources to point to these other two columns. Example: =cboMyCombo.Column(2) This would give you the value in the third column of the combo box. The index number is zero based, so 2 is the third column, 3 is the fourth, etc. -- Wayne Morgan MS Access MVP "Roxanne" wrote in message ... I have a form based off of a trouble ticket table. In the form, I have a combo box that is currently allowing a person to select the customer from a drop down list of customer names. The list to this box is coming from another table within the database named "customers" and the customers table is linked to the trouble ticket table by customer ID. The customers table also contains maintenance status and expiration date columns. What I want to know is if there is a way for the maintenance status and expiration date to show up on the form when a certain customer is selected. For example - if customer ABC is selected from the drop down list of customer and their maintenance status is "current" and expiration date is "1/1/2006", is there a way to display (in 2 additional boxes on the form) the word "current" and the date "1/1/2006" any time someone picks customer ABC from the drop down list for customer when entering a trouble ticket? The technicians do not know the maintenance status and expiration of the customers and I need those two columns from the Customer table to populate into seperate boxes under the drop down list for Customers. This way, any time they enter a trouble ticket and selecting a customer, those two boxes will populate the maintenance status of the customer so the tech knows. I had one reply before (listed below) and when I followed those coding instructions, it showed all 3 columns (company name, maint, expiration) all in the drop down list for the Customer, but when you selected a certain customer, the only thing visible was the company name. I just want to get the maintenance and expiration to populate into 2 other boxes when a certain company is selected from the drop down menu. Is this possible and if so, how do I accomplish it? ORIGINAL ANSWER: The Row Source property of the combo box on the Form should be a Query selecting the customer information. I'm suggesting that that query include the two fields which you want to see. For example, the RowSource property of the combo could be SELECT CustomerID, CustomerName, Status, ExpirationDate FROM Customers ORDER BY CustomerName; The Combo's ColumnCount property would be 4 to include all fields; its ColumnWidths property would be 0;1.5;0;0 to display the customer name and hide all the other fields. The Bound Column would be 1 so that the CustomerID is bound to your trouble ticket's CustomerID field. A Combo Box has a "Column" property which lets you extract information other than the bound column's value from the combo. If your combo is named cboCustomer, then simply look at the Control Source property of the textbox on the form in which you wish to display the status, and type in =cboCustomer.Column(2) This will automatically display the third (zero based, remember) field from the combo - the status, using the example above. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Displaying AVI and Jpeg images in an access form. | RAM | General Discussion | 0 | August 5th, 2005 01:53 AM |
Combo Box NotInList - How To Add Data To Underlying Table | 10SNUT | Using Forms | 19 | July 8th, 2005 09:12 PM |
Weird Access 2K problems in a form | Chuck Chopp | Using Forms | 4 | September 1st, 2004 08:54 PM |
Upload Image | Jason MacKenzie | General Discussion | 1 | September 1st, 2004 04:38 AM |
Recordset in subform based on field in parent form | Lyn | General Discussion | 15 | June 14th, 2004 03:10 PM |