Updateing a combobox

[tracey]

I have created a form that has a combox box which has a
list of parts. I coded a proc that adds a part to the
list If the user types in a part that doesn't exist in the
list. This opens another form in which the user inputs
additional info and then closes. The new part is now in
the list however the combobox does not show the new item
yest, and the combobox has the new part number (from when
the user typed it in to begin with). I wrote code that
when the user changes records then the combobox will
requery and the new item will be there. Unfortunatley I
would prefer to have the combo box requery and and the
user select the new part without leaving the record. Any
ideas? I searched the newsgroups and didn't find anything
similar, I can't imiagine that I am the only one that has
had this problem. As always, I appreciate the help!
Tracey

 

[Graham Mandeno]

Hi Tracey

You update the list in the combo box with the Requery method:
Me.ComboBoxName.Requery

If you open your second form modally (by specifying acDialog for the
WindowMode argument for OpenForm) then the execution of your calling
procedure will be suspended until that form is closed. You can then requery
the combo box right after the OpenForm line.

 

[tina]

i tried the "open form in Dialog mode" approach a few weeks ago after
reading another post here in the newsgroups, but the rest of the code in my
procedure did not suspend. i had to end up putting the open form (in Dialog)
action in a macro and using DoCmd.RunMacro in the procedure - *then* it
suspended the rest of the code until the form was closed.
was using A2000 db in A2003, on Win 2000 Pro OS. do you know if there's an
identified bug? or perhaps i did something wrong? any comments will be
appreciated.

Hi Tracey

You update the list in the combo box with the Requery method:
Me.ComboBoxName.Requery

If you open your second form modally (by specifying acDialog for the
WindowMode argument for OpenForm) then the execution of your calling
procedure will be suspended until that form is closed. You can then requery
the combo box right after the OpenForm line.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have created a form that has a combox box which has a
list of parts. I coded a proc that adds a part to the
list If the user types in a part that doesn't exist in the
list. This opens another form in which the user inputs
additional info and then closes. The new part is now in
the list however the combobox does not show the new item
yest, and the combobox has the new part number (from when
the user typed it in to begin with). I wrote code that
when the user changes records then the combobox will
requery and the new item will be there. Unfortunatley I
would prefer to have the combo box requery and and the
user select the new part without leaving the record. Any
ideas? I searched the newsgroups and didn't find anything
similar, I can't imiagine that I am the only one that has
had this problem. As always, I appreciate the help!
Tracey

 

[Graham Mandeno]

Hi Tina

You don't say what code you used to open the form in dialog mode. If you
specified WindowMode (the sixth argument) as acDialog, then it should
certainly work.

Sometimes it's easy when there are lots of null arguments to put one in the
wrong position. For example:
DoCmd.OpenForm "frmMyDialog", , , , acDialog
has one too few commas, so you are passing acDialog as the *fifth* argument
(DataMode).

For this reason, it can be a good idea to use named arguments. For example:
DoCmd.OpenForm "frmMyDialog", WindowMode:=acDialog
(note the colon before the "=")
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

i tried the "open form in Dialog mode" approach a few weeks ago after
reading another post here in the newsgroups, but the rest of the code in my
procedure did not suspend. i had to end up putting the open form (in Dialog)
action in a macro and using DoCmd.RunMacro in the procedure - *then* it
suspended the rest of the code until the form was closed.
was using A2000 db in A2003, on Win 2000 Pro OS. do you know if there's an
identified bug? or perhaps i did something wrong? any comments will be
appreciated.

Hi Tracey

You update the list in the combo box with the Requery method:
Me.ComboBoxName.Requery

If you open your second form modally (by specifying acDialog for the
WindowMode argument for OpenForm) then the execution of your calling
procedure will be suspended until that form is closed. You can then requery
the combo box right after the OpenForm line.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have created a form that has a combox box which has a
list of parts. I coded a proc that adds a part to the
list If the user types in a part that doesn't exist in the
list. This opens another form in which the user inputs
additional info and then closes. The new part is now in
the list however the combobox does not show the new item
yest, and the combobox has the new part number (from when
the user typed it in to begin with). I wrote code that
when the user changes records then the combobox will
requery and the new item will be there. Unfortunatley I
would prefer to have the combo box requery and and the
user select the new part without leaving the record. Any
ideas? I searched the newsgroups and didn't find anything
similar, I can't imiagine that I am the only one that has
had this problem. As always, I appreciate the help!
Tracey

 

[tina]

here's the line of code i attempted:

DoCmd.OpenForm "frmColors", acFormDS, , , acFormAdd, acDialog

Hi Tina

You don't say what code you used to open the form in dialog mode. If you
specified WindowMode (the sixth argument) as acDialog, then it should
certainly work.

Sometimes it's easy when there are lots of null arguments to put one in the
wrong position. For example:
DoCmd.OpenForm "frmMyDialog", , , , acDialog
has one too few commas, so you are passing acDialog as the *fifth* argument
(DataMode).

For this reason, it can be a good idea to use named arguments. For example:
DoCmd.OpenForm "frmMyDialog", WindowMode:=acDialog
(note the colon before the "=")
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

i tried the "open form in Dialog mode" approach a few weeks ago after
reading another post here in the newsgroups, but the rest of the code in my
procedure did not suspend. i had to end up putting the open form (in Dialog)
action in a macro and using DoCmd.RunMacro in the procedure - *then* it
suspended the rest of the code until the form was closed.
was using A2000 db in A2003, on Win 2000 Pro OS. do you know if there's an
identified bug? or perhaps i did something wrong? any comments will be
appreciated.

Hi Tracey

You update the list in the combo box with the Requery method:
Me.ComboBoxName.Requery

If you open your second form modally (by specifying acDialog for the
WindowMode argument for OpenForm) then the execution of your calling
procedure will be suspended until that form is closed. You can then requery
the combo box right after the OpenForm line.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have created a form that has a combox box which has a
list of parts. I coded a proc that adds a part to the
list If the user types in a part that doesn't exist in the
list. This opens another form in which the user inputs
additional info and then closes. The new part is now in
the list however the combobox does not show the new item
yest, and the combobox has the new part number (from when
the user typed it in to begin with). I wrote code that
when the user changes records then the combobox will
requery and the new item will be there. Unfortunatley I
would prefer to have the combo box requery and and the
user select the new part without leaving the record. Any
ideas? I searched the newsgroups and didn't find anything
similar, I can't imiagine that I am the only one that has
had this problem. As always, I appreciate the help!
Tracey

 

[Tracey]

The me.combobox.requery will not work because the value of
the combobox must be something within the list or empty.
Lets say you type something in the combobox that is not in
the list, the "add to" form opens in whatever mode you
add data and close the form which forces the requery, the
combobox still has the value that you typed into it (which
wasn't in the list to begin with), which is not in the
list yet, an error occurrs because of that. even clearing
the contents of the combobox generates and error
because "null" is not in the list? The error is occuring
because the field cannot requery while text is present. I
think I understand how to write the code to force the
requery, I am having trouble clearing data or the new
record before forcing the requery. Any Ideas?

 

[tina]

this is the solution that worked for me. notice that the code is running
from the combo box's NotInList event.

Private Sub Combo0_NotInList(NewData As String, Response As Integer)

If MsgBox("Do you want to add a new color to the list?",
vbDefaultButton1 + vbYesNo) = vbYes Then
' DoCmd.OpenForm "frmColors", acFormDS, , , acFormAdd, acDialog
DoCmd.RunMacro "Macro1.open form"
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!Combo0 = Null
Me!Combo0.Dropdown
End If

End Sub

i commented out the "DoCmd.OpenForm..." line because that did not suspend
the code for me; instead i used the next line to run a macro, settings
below:

Macro object name: Macro1
Macro name: open form
first Action: OpenForm
Form Name: frmColors
View: Datasheet
Data Mode: Add
Window Mode: Dialog
second Action: StopMacro

you could try the "DoCmd.OpenForm..." line first (commenting out the
"DoCmd.RunMacro" line), since that one is supposed to work. if it doesn't
suspend the code, use the other line instead, like i did.
and, of course, you have to substitute your own control, form and macro
names, and msgbox message, etc.

hth

 

[Graham Mandeno]

Hi Tina

DoCmd.OpenForm "frmColors", acFormDS, , , acFormAdd, acDialog

The problem is the acFormDS.

AFAIK it's not documented anywhere, but if you open a form in datasheet view
it CANNOT be modal. I'm only guessing, but I assume this is due to some
low-level Windows restriction in the way datasheet windows are implemented.

The behaviour seems to be inconsistent. If you create a form with
DefaultView set to DataSheet, and open it acDialog with the second argument
blank, then it will open in Normal view and be modal. However if you
specify acFormDS in the OpenForm call it will open in datasheet view but
will not be modal (so execution is not suspended).

There are two workarounds:

1. If possible, design the form for continuous view. If you wish, you can
make it look almost exactly like a datasheet. There are all sorts of
advantages (more choice of controls, headers & footers, etc) and few
disadvantages (cannot change column width in form view, etc)

2. If you REALLY need a datasheet, then embed it as a subform in another
unbound form.

HOWEVER, I cannot see why, when adding a single record, you would want
either continuous or datasheet view. Do you really need to see multiple
records? (You won't anyway, because you are using acFormAdd). Why not just
use a form in Single Record view?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

here's the line of code i attempted:

DoCmd.OpenForm "frmColors", acFormDS, , , acFormAdd, acDialog

Hi Tina

You don't say what code you used to open the form in dialog mode. If you
specified WindowMode (the sixth argument) as acDialog, then it should
certainly work.

Sometimes it's easy when there are lots of null arguments to put one in the
wrong position. For example:
DoCmd.OpenForm "frmMyDialog", , , , acDialog
has one too few commas, so you are passing acDialog as the *fifth* argument
(DataMode).

For this reason, it can be a good idea to use named arguments. For example:
DoCmd.OpenForm "frmMyDialog", WindowMode:=acDialog
(note the colon before the "=")
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

i tried the "open form in Dialog mode" approach a few weeks ago after
reading another post here in the newsgroups, but the rest of the code

in
my

procedure did not suspend. i had to end up putting the open form (in Dialog)
action in a macro and using DoCmd.RunMacro in the procedure - *then* it
suspended the rest of the code until the form was closed.
was using A2000 db in A2003, on Win 2000 Pro OS. do you know if

there's

an

identified bug? or perhaps i did something wrong? any comments will be
appreciated.


Hi Tracey

You update the list in the combo box with the Requery method:
Me.ComboBoxName.Requery

If you open your second form modally (by specifying acDialog for the
WindowMode argument for OpenForm) then the execution of your calling
procedure will be suspended until that form is closed. You can then
requery
the combo box right after the OpenForm line.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have created a form that has a combox box which has a
list of parts. I coded a proc that adds a part to the
list If the user types in a part that doesn't exist in the
list. This opens another form in which the user inputs
additional info and then closes. The new part is now in
the list however the combobox does not show the new item
yest, and the combobox has the new part number (from when
the user typed it in to begin with). I wrote code that
when the user changes records then the combobox will
requery and the new item will be there. Unfortunatley I
would prefer to have the combo box requery and and the
user select the new part without leaving the record. Any
ideas? I searched the newsgroups and didn't find anything
similar, I can't imiagine that I am the only one that has
had this problem. As always, I appreciate the help!
Tracey

 

[tina]

ah, there's the answer to the puzzle! and you're right, in a situation like
this, i can't think of any reason why the form would *have* to be datasheet
view. thanks for your help!

Hi Tina

DoCmd.OpenForm "frmColors", acFormDS, , , acFormAdd, acDialog

The problem is the acFormDS.

AFAIK it's not documented anywhere, but if you open a form in datasheet view
it CANNOT be modal. I'm only guessing, but I assume this is due to some
low-level Windows restriction in the way datasheet windows are implemented.

The behaviour seems to be inconsistent. If you create a form with
DefaultView set to DataSheet, and open it acDialog with the second argument
blank, then it will open in Normal view and be modal. However if you
specify acFormDS in the OpenForm call it will open in datasheet view but
will not be modal (so execution is not suspended).

There are two workarounds:

1. If possible, design the form for continuous view. If you wish, you can
make it look almost exactly like a datasheet. There are all sorts of
advantages (more choice of controls, headers & footers, etc) and few
disadvantages (cannot change column width in form view, etc)

2. If you REALLY need a datasheet, then embed it as a subform in another
unbound form.

HOWEVER, I cannot see why, when adding a single record, you would want
either continuous or datasheet view. Do you really need to see multiple
records? (You won't anyway, because you are using acFormAdd). Why not just
use a form in Single Record view?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

here's the line of code i attempted:

DoCmd.OpenForm "frmColors", acFormDS, , , acFormAdd, acDialog

Hi Tina

You don't say what code you used to open the form in dialog mode. If you
specified WindowMode (the sixth argument) as acDialog, then it should
certainly work.

Sometimes it's easy when there are lots of null arguments to put one

in
the

wrong position. For example:
DoCmd.OpenForm "frmMyDialog", , , , acDialog
has one too few commas, so you are passing acDialog as the *fifth* argument
(DataMode).

For this reason, it can be a good idea to use named arguments. For example:
DoCmd.OpenForm "frmMyDialog", WindowMode:=acDialog
(note the colon before the "=")
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


i tried the "open form in Dialog mode" approach a few weeks ago after
reading another post here in the newsgroups, but the rest of the

code

in

my
procedure did not suspend. i had to end up putting the open form (in
Dialog)
action in a macro and using DoCmd.RunMacro in the procedure - *then* it
suspended the rest of the code until the form was closed.
was using A2000 db in A2003, on Win 2000 Pro OS. do you know if

there's

an
identified bug? or perhaps i did something wrong? any comments will be
appreciated.


Hi Tracey

You update the list in the combo box with the Requery method:
Me.ComboBoxName.Requery

If you open your second form modally (by specifying acDialog for the
WindowMode argument for OpenForm) then the execution of your calling
procedure will be suspended until that form is closed. You can then
requery
the combo box right after the OpenForm line.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have created a form that has a combox box which has a
list of parts. I coded a proc that adds a part to the
list If the user types in a part that doesn't exist in the
list. This opens another form in which the user inputs
additional info and then closes. The new part is now in
the list however the combobox does not show the new item
yest, and the combobox has the new part number (from when
the user typed it in to begin with). I wrote code that
when the user changes records then the combobox will
requery and the new item will be there. Unfortunatley I
would prefer to have the combo box requery and and the
user select the new part without leaving the record. Any
ideas? I searched the newsgroups and didn't find anything
similar, I can't imiagine that I am the only one that has
had this problem. As always, I appreciate the help!
Tracey

 

[tina]

tracey, if you try my solution as previously posted, see the last post from
Graham. the code should work, without the macro, if changed as follows:

Private Sub Combo0_NotInList(NewData As String, Response As Integer)

If MsgBox("Do you want to add a new color to the list?",
vbDefaultButton1 + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmColors", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!Combo0 = Null
Me!Combo0.Dropdown
End If

End Sub