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
|
|||
|
|||
Requery Combobox
I have a combobox with an event that adds a record if
the 'id' doesn't exist. I try to requery the control (combobox) but I get an error 2118 saying I must save the field befor the requery. my statement is: IDCtl.Requery where IDCtl is set to the control. .. |
#2
|
|||
|
|||
Requery Combobox
"MJ" wrote I have a combobox with an event that adds a record if the 'id' doesn't exist. I try to requery the control (combobox) but I get an error 2118 saying I must save the field befor the requery. my statement is: IDCtl.Requery where IDCtl is set to the control. . Hi MJ, I think the following may help you: Use NotInList Event to Add a Record to Combo Box http://support.microsoft.com/?kbid=197526 You might just need ' Set Response argument to indicate that new data is being added. Response = acDataErrAdded although it can be difficult to sort this out the first time. It wouldn't hurt to post the code you have so far, the source (SQL) of your combobox, and which column is the bound column. Please respond back if I have misunderstood. Good luck, Gary Walter |
#3
|
|||
|
|||
Requery Combobox
-----Original Message----- "MJ" wrote I have a combobox with an event that adds a record if the 'id' doesn't exist. I try to requery the control (combobox) but I get an error 2118 saying I must save the field befor the requery. my statement is: IDCtl.Requery where IDCtl is set to the control. . Hi MJ, I think the following may help you: Use NotInList Event to Add a Record to Combo Box http://support.microsoft.com/?kbid=197526 You might just need ' Set Response argument to indicate that new data is being added. Response = acDataErrAdded although it can be difficult to sort this out the first time. It wouldn't hurt to post the code you have so far, the source (SQL) of your combobox, and which column is the bound column. Please respond back if I have misunderstood. Good luck, Gary Walter . Hey; I read the KB article and basically have the same code. The record get's added fine but the requery of the control won't work w/o getting the error. I will try to add the Response = acDataErrAdded but I'm not sure what it does. Mike |
#4
|
|||
|
|||
Requery Combobox
Hi MJ,
Not easy to find in Help, so with apologies, here it be ****quote**** NotInList Event - Event Procedures To create an event procedure that runs when the NotInList event occurs, set the OnNotInList property to [Event Procedure], and click the Build button . Syntax Private Sub controlname_NotInList(NewData As String, Response As Integer) The NotInList event procedure has the following arguments. Argument Description controlname The name of the control whose NotInList event procedure you want to run. NewData A string that Microsoft Access uses to pass the text the user entered in the text box portion of the combo box to the event procedure. Response The setting indicates how the NotInList event was handled. The Response argument can be one of the following intrinsic constants: Constant Description acDataErrDisplay (Default) Displays the default message to the user. You can use this when you don't want to allow the user to add a new value to the combo box list. acDataErrContinue Doesn't display the default message to the user. You can use this when you want to display a custom message to the user. For example, the event procedure could display a custom dialog box asking if the user wanted to save the new entry. If the response is Yes, the event procedure would add the new entry to the list and set the Response argument to acDataErrAdded. If the response is No, the event procedure would set the Response argument to acDataErrContinue. -------------------------- acDataErrAdded Doesn't display a message to the user but enables you to add the entry to the combo box list in the NotInList event procedure. After the entry is added, Microsoft Access updates the list by *requerying* the combo box. Microsoft Access then rechecks the string against the combo box list, and saves the value in the NewData argument in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message. --------------------- Remarks You can create an event procedure for the NotInList event that provides a way for the user to add a new item to the combo box list. For example, you can add a record to the table that supplies the list's values, or add a value to the value list that is the source for the combo box list. To add a new entry to a combo box list, your event procedure must add the value in the NewData argument to the source of the combo box list. How you do this depends on the type of source the combo box list uses, as determined by the RowSourceType and RowSource properties of the combo box. In the Example in this topic, the event procedure adds the new value to a value list for the combo box. If you let the user change the value originally typed in the combo box (for example, in a custom dialog box), you must set the combo box value to the new entry entered in the custom dialog box. This saves the new value in the field the combo box is bound to. Set the Response argument to acDataErrContinue, and Microsoft Access will add the new value to the combo box list. ***unquote** |
#5
|
|||
|
|||
Requery Combobox
and here be Help example
***quote*** NotInList Event - Event Procedures Example The following example uses the NotInList event to add an item to a combo box. To try this example, create a combo box called Colors on a form. Set the combo box's LimitToList property to Yes. To populate the combo box, set the combo box's RowSourceType property to Value List, and supply a list of values separated by semicolons as the setting for the RowSource property. For example, you might supply the following values as the setting for this property: Red; Green; Blue. Next add the following event procedure to the form. Switch to Form view and enter a new value in the text portion of the combo box. Private Sub Colors_NotInList(NewData As String, _ Response As Integer) Dim ctl As Control ' Return Control object that points to combo box. Set ctl = Me!Colors ' Prompt user to verify they wish to add new value. If MsgBox("Value is not in list. Add it?", _ vbOKCancel) = vbOK Then ' Set Response argument to indicate that data ' is being added. Response = acDataErrAdded ' Add string in NewData argument to row source. ctl.RowSource = ctl.RowSource & ";" & NewData Else ' If user chooses Cancel, suppress error message ' and undo changes. Response = acDataErrContinue ctl.Undo End If End Sub Note The above example adds an item to an unbound combo box. When you add an item to a bound combo box, you add a value to a field in the underlying data source. In most cases you can't simply add one field in a new record - depending on the structure of data in the table, you probably will need to add one or more fields to fulfill data requirements. For instance, a new record must include values for any fields comprising the primary key. If you need to add items to a bound combo box dynamically, you must prompt the user to enter data for all required fields, save the new record, and then requery the combo box to display the new value. ***unquote*** "Gary Walter" wrote in message ... Hi MJ, Not easy to find in Help, so with apologies, here it be ****quote**** NotInList Event - Event Procedures To create an event procedure that runs when the NotInList event occurs, set the OnNotInList property to [Event Procedure], and click the Build button . Syntax Private Sub controlname_NotInList(NewData As String, Response As Integer) The NotInList event procedure has the following arguments. Argument Description controlname The name of the control whose NotInList event procedure you want to run. NewData A string that Microsoft Access uses to pass the text the user entered in the text box portion of the combo box to the event procedure. Response The setting indicates how the NotInList event was handled. The Response argument can be one of the following intrinsic constants: Constant Description acDataErrDisplay (Default) Displays the default message to the user. You can use this when you don't want to allow the user to add a new value to the combo box list. acDataErrContinue Doesn't display the default message to the user. You can use this when you want to display a custom message to the user. For example, the event procedure could display a custom dialog box asking if the user wanted to save the new entry. If the response is Yes, the event procedure would add the new entry to the list and set the Response argument to acDataErrAdded. If the response is No, the event procedure would set the Response argument to acDataErrContinue. -------------------------- acDataErrAdded Doesn't display a message to the user but enables you to add the entry to the combo box list in the NotInList event procedure. After the entry is added, Microsoft Access updates the list by *requerying* the combo box. Microsoft Access then rechecks the string against the combo box list, and saves the value in the NewData argument in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message. --------------------- Remarks You can create an event procedure for the NotInList event that provides a way for the user to add a new item to the combo box list. For example, you can add a record to the table that supplies the list's values, or add a value to the value list that is the source for the combo box list. To add a new entry to a combo box list, your event procedure must add the value in the NewData argument to the source of the combo box list. How you do this depends on the type of source the combo box list uses, as determined by the RowSourceType and RowSource properties of the combo box. In the Example in this topic, the event procedure adds the new value to a value list for the combo box. If you let the user change the value originally typed in the combo box (for example, in a custom dialog box), you must set the combo box value to the new entry entered in the custom dialog box. This saves the new value in the field the combo box is bound to. Set the Response argument to acDataErrContinue, and Microsoft Access will add the new value to the combo box list. ***unquote** |
#6
|
|||
|
|||
Requery Combobox
-----Original Message----- and here be Help example ***quote*** NotInList Event - Event Procedures Example The following example uses the NotInList event to add an item to a combo box. To try this example, create a combo box called Colors on a form. Set the combo box's LimitToList property to Yes. To populate the combo box, set the combo box's RowSourceType property to Value List, and supply a list of values separated by semicolons as the setting for the RowSource property. For example, you might supply the following values as the setting for this property: Red; Green; Blue. Next add the following event procedure to the form. Switch to Form view and enter a new value in the text portion of the combo box. Private Sub Colors_NotInList(NewData As String, _ Response As Integer) Dim ctl As Control ' Return Control object that points to combo box. Set ctl = Me!Colors ' Prompt user to verify they wish to add new value. If MsgBox("Value is not in list. Add it?", _ vbOKCancel) = vbOK Then ' Set Response argument to indicate that data ' is being added. Response = acDataErrAdded ' Add string in NewData argument to row source. ctl.RowSource = ctl.RowSource & ";" & NewData Else ' If user chooses Cancel, suppress error message ' and undo changes. Response = acDataErrContinue ctl.Undo End If End Sub Note The above example adds an item to an unbound combo box. When you add an item to a bound combo box, you add a value to a field in the underlying data source. In most cases you can't simply add one field in a new record - depending on the structure of data in the table, you probably will need to add one or more fields to fulfill data requirements. For instance, a new record must include values for any fields comprising the primary key. If you need to add items to a bound combo box dynamically, you must prompt the user to enter data for all required fields, save the new record, and then requery the combo box to display the new value. ***unquote*** "Gary Walter" wrote in message ... Hi MJ, Not easy to find in Help, so with apologies, here it be ****quote**** NotInList Event - Event Procedures To create an event procedure that runs when the NotInList event occurs, set the OnNotInList property to [Event Procedure], and click the Build button . Syntax Private Sub controlname_NotInList(NewData As String, Response As Integer) The NotInList event procedure has the following arguments. Argument Description controlname The name of the control whose NotInList event procedure you want to run. NewData A string that Microsoft Access uses to pass the text the user entered in the text box portion of the combo box to the event procedure. Response The setting indicates how the NotInList event was handled. The Response argument can be one of the following intrinsic constants: Constant Description acDataErrDisplay (Default) Displays the default message to the user. You can use this when you don't want to allow the user to add a new value to the combo box list. acDataErrContinue Doesn't display the default message to the user. You can use this when you want to display a custom message to the user. For example, the event procedure could display a custom dialog box asking if the user wanted to save the new entry. If the response is Yes, the event procedure would add the new entry to the list and set the Response argument to acDataErrAdded. If the response is No, the event procedure would set the Response argument to acDataErrContinue. -------------------------- acDataErrAdded Doesn't display a message to the user but enables you to add the entry to the combo box list in the NotInList event procedure. After the entry is added, Microsoft Access updates the list by *requerying* the combo box. Microsoft Access then rechecks the string against the combo box list, and saves the value in the NewData argument in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message. --------------------- Remarks You can create an event procedure for the NotInList event that provides a way for the user to add a new item to the combo box list. For example, you can add a record to the table that supplies the list's values, or add a value to the value list that is the source for the combo box list. To add a new entry to a combo box list, your event procedure must add the value in the NewData argument to the source of the combo box list. How you do this depends on the type of source the combo box list uses, as determined by the RowSourceType and RowSource properties of the combo box. In the Example in this topic, the event procedure adds the new value to a value list for the combo box. If you let the user change the value originally typed in the combo box (for example, in a custom dialog box), you must set the combo box value to the new entry entered in the custom dialog box. This saves the new value in the field the combo box is bound to. Set the Response argument to acDataErrContinue, and Microsoft Access will add the new value to the combo box list. ***unquote** . Thanks for the explanation...makes sense now. However, the combobox is bound to a query and I do add the record to the table but get an error when I do the requery. But the error says that the field needs to be saved first. |
#7
|
|||
|
|||
Requery Combobox
please post the event procedure.
"MJ" wrote in message ... I have a combobox with an event that adds a record if the 'id' doesn't exist. I try to requery the control (combobox) but I get an error 2118 saying I must save the field befor the requery. my statement is: IDCtl.Requery where IDCtl is set to the control. . |
#8
|
|||
|
|||
Requery Combobox
Mike wrote:
I have a combobox with an event that adds a record if the 'id' doesn't exist. I try to requery the control (combobox) but I get an error 2118 saying I must save the field befor the requery. my statement is: IDCtl.Requery where IDCtl is set to the control. --------- Gary wrote: I think the following may help you: Use NotInList Event to Add a Record to Combo Box http://support.microsoft.com/?kbid=197526 It wouldn't hurt to post the code you have so far, the source (SQL) of your combobox, and which column is the bound column. ----------- Mike replied: I read the KB article and basically have the same code. The record get's added fine but the requery of the control won't work w/o getting the error. I will try to add the Response = acDataErrAdded but I'm not sure what it does. ------------ Gary replied: Here be everything from Access2000 Help ------------. Mike replied: Thanks for the explanation...makes sense now. However, the combobox is bound to a query and I do add the record to the table but get an error when I do the requery. But the error says that the field needs to be saved first. ------------ Gary replied 1) What is the rowsource query of the combobox? (and which field in the query is the bound column?) 2) What is the complete code for your NotInList Event? 3) How are you using this combobox? a) is it bound to a field of the form's recordsource? (this is the only situation where I personally have used NotInList event) 4) What is the recordsource for the form that contains the combobox? 5) Do the form and combobox "share" the same table? Thanks, Gary Walter |
Thread Tools | |
Display Modes | |
|
|