A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Requery Combobox



 
 
Thread Tools Display Modes
  #1  
Old May 24th, 2004, 02:50 AM
MJ
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 09:51 AM
Gary Walter
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 01:03 PM
MJ
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 01:32 PM
Gary Walter
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 01:35 PM
Gary Walter
external usenet poster
 
Posts: n/a
Default 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  
Old May 25th, 2004, 02:52 AM
Mike J
external usenet poster
 
Posts: n/a
Default 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  
Old May 25th, 2004, 03:59 AM
tina
external usenet poster
 
Posts: n/a
Default 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  
Old May 25th, 2004, 11:01 AM
Gary Walter
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.