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
|
|||
|
|||
combobox rowsource
I have a combo box whos control source is stored in a table, products. Its
row source is a query against the vendors table that filters records by a field called "Active". When entering data, the combo box lists all vendors that are active. Works fine. After changing a vendors status to inactive, viewing old records for that vendor yields an empty entry in the combo box. Not good. I need to allow users to see the old records vendor value but not let them pick inactive vendors in the combo box during data entry. I hope I explained that well enough. Any help would be greatly appreciated. |
#2
|
|||
|
|||
combobox rowsource
"DaveE" wrote in message
... I have a combo box whos control source is stored in a table, products. Its row source is a query against the vendors table that filters records by a field called "Active". When entering data, the combo box lists all vendors that are active. Works fine. After changing a vendors status to inactive, viewing old records for that vendor yields an empty entry in the combo box. Not good. I need to allow users to see the old records vendor value but not let them pick inactive vendors in the combo box during data entry. One way to do this, *if* the combo's Bound Column is the visible and displayed column, is to set the combo box's LimitToList property to No (so the user can theoretically enter values not in the list, and the combo will show all existing values), but use the combo's BeforeUpdate event to check whether any entry is actually in the list, and cancel the update if it isn't. If the value entered in the combo box is not in the list, the combo box's .ListIndex property will have a value of -1. So you could have a BeforeUpdate event procedure like this: '------ start of example code ------ Private Sub cboVendor_BeforeUpdate(Cancel As Integer) If Me!cboVender.ListIndex 0 Then MsgBox _ "You entered a vendor that is not in the list. " & _ "Please choose an active vendor from the list.", _ vbExclamation, _ "Invalid Vendor" Cancel = True End If End Sub '------ end of example code ------ Now, that won't work if the combo box's bound column is, say, the VendorID, but it displays some other column such as the vendor's name. In such a case, there are a couple of other alternatives. One is to arrange the combo's rowsource and columns so that it includes the Active field, but selects all vendors, active or no. It should probably go ahead and sort all the inactive vendors to the bottom of the list. Then use the combo's BeforeUpdate event to check whether the user has chosen an inactive vendor, and if so, display a suitable message and cancel the update. If you decide you really have to keep the inactive vendors out of the list entirely, then you need to fudge by modifying the form's recordsource to pick up the vendor name from wherever it resides, and have a text box bound to the vendor name on the form, positioned so that it overlays the text portion of the combo box. Make sure the text box is on top (Format - Bring to Front), and set its Disabled and Locked properties both to True. When the user tabs through the fields on the form, the focus will go to the combo box, which will be blank if the vendor is not in the list, but but when the focus is not in the combo box, the text box will display the vendor name. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#3
|
|||
|
|||
combobox rowsource
In the Current event of the form, change the rowsource, something like:
Sub Form_Current() If Me.NewRecord = True Then Me.cboComboWhatever.RowSource = "Query1" Else Me.cboComboWhatever.RowSource = "Query2" End If End Sub -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "DaveE" wrote in message ... I have a combo box whos control source is stored in a table, products. Its row source is a query against the vendors table that filters records by a field called "Active". When entering data, the combo box lists all vendors that are active. Works fine. After changing a vendors status to inactive, viewing old records for that vendor yields an empty entry in the combo box. Not good. I need to allow users to see the old records vendor value but not let them pick inactive vendors in the combo box during data entry. I hope I explained that well enough. Any help would be greatly appreciated. |
#4
|
|||
|
|||
combobox rowsource
Thank you both for responding. Two good ideas.
"Dirk Goldgar" wrote: "DaveE" wrote in message ... I have a combo box whos control source is stored in a table, products. Its row source is a query against the vendors table that filters records by a field called "Active". When entering data, the combo box lists all vendors that are active. Works fine. After changing a vendors status to inactive, viewing old records for that vendor yields an empty entry in the combo box. Not good. I need to allow users to see the old records vendor value but not let them pick inactive vendors in the combo box during data entry. One way to do this, *if* the combo's Bound Column is the visible and displayed column, is to set the combo box's LimitToList property to No (so the user can theoretically enter values not in the list, and the combo will show all existing values), but use the combo's BeforeUpdate event to check whether any entry is actually in the list, and cancel the update if it isn't. If the value entered in the combo box is not in the list, the combo box's .ListIndex property will have a value of -1. So you could have a BeforeUpdate event procedure like this: '------ start of example code ------ Private Sub cboVendor_BeforeUpdate(Cancel As Integer) If Me!cboVender.ListIndex 0 Then MsgBox _ "You entered a vendor that is not in the list. " & _ "Please choose an active vendor from the list.", _ vbExclamation, _ "Invalid Vendor" Cancel = True End If End Sub '------ end of example code ------ Now, that won't work if the combo box's bound column is, say, the VendorID, but it displays some other column such as the vendor's name. In such a case, there are a couple of other alternatives. One is to arrange the combo's rowsource and columns so that it includes the Active field, but selects all vendors, active or no. It should probably go ahead and sort all the inactive vendors to the bottom of the list. Then use the combo's BeforeUpdate event to check whether the user has chosen an inactive vendor, and if so, display a suitable message and cancel the update. If you decide you really have to keep the inactive vendors out of the list entirely, then you need to fudge by modifying the form's recordsource to pick up the vendor name from wherever it resides, and have a text box bound to the vendor name on the form, positioned so that it overlays the text portion of the combo box. Make sure the text box is on top (Format - Bring to Front), and set its Disabled and Locked properties both to True. When the user tabs through the fields on the form, the focus will go to the combo box, which will be blank if the vendor is not in the list, but but when the focus is not in the combo box, the text box will display the vendor name. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|