Trying to "Drop-up" a dropped-down combobox

[Fred Boer]

Hello:

I am having a problem with the "dropped-down" state of a combobox. It occurs
in a "not in list" situation. Code is reproduced below:

I found a possible solution by googling, which involves moving the focus
from the combobox, and then moving the focus back to the combobox (see
"Attempted solution here:" in code). It doesn't work. The focus ends up back
in the combobox, but the combobox is still "dropped down". I have tried
setting break points and the code *does* fire. I have also tried remarking
out the line which would set the focus back to the combobox. When I do this
the focus is correctly set on the command button.

Thanks!
Fred Boer


Private Sub cboAuthor_NotInList(NewData As String, Response As Integer)
On Error GoTo Errorhandler

Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String
Dim db As Database, ssql As String

strMsg = NewData & " isn't an existing author. " & "Add a new author?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid Author")

Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "Frm_InsertAuthor", OpenArgs:=NewData,
DataMode:=acFormAdd, Windowmode:=acDialog

' Stop here and wait until the form goes away.

If ISLOADED("Frm_InsertAuthor") Then
Response = acDataErrAdded
DoCmd.Close acForm, "Frm_InsertAuthor"
Else
Response = acDataErrContinue
End If

Case vbNo
Response = acDataErrContinue
End Select

'Attempted solution here:

'Shift focus from combobox to force close of dropdown list, then back
Forms!Frm_LibraryDataEdit!cmdGoFirst.SetFocus
Forms!Frm_LibraryDataEdit!cboAuthor.SetFocus

ExitPoint:
Exit Sub
Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint
End Sub

 

[Ken Snell [MVP]]

Just a guess, Fred... is there code on the GotFocus event of the combo box
that is dropping down the list?

 

[Fred Boer]

Hi Ken!

Good guess, but there is no GotFocus code... There is code for the
AfterUpdate Event and Not In List Event.

I don't *think* that the AfterUpdate event could be a problem, but I've
appended that code below..

Thanks a lot!
Fred

Private Sub cboAuthor_AfterUpdate()
On Error GoTo Errorhandler
Dim db As Database, ssql As String

ssql = "INSERT INTO Tbl_BookAuthor (Book_ID, Author_ID) SELECT " &
Forms!Frm_LibraryDataEdit.txtBook_ID & " AS Expr1," &
Forms!Frm_LibraryDataEdit.cboAuthor & " AS Expr2"
Set db = CurrentDb()
db.Execute ssql, dbFailOnError

If db.RecordsAffected <> 1 Then
MsgBox "This is not a valid Book ID. Please try again.", vbOKOnly +
vbInformation, "W. Ross Macdonald School"
Me.Undo
End If

Set db = Nothing
Me.Refresh
Forms!Frm_LibraryDataEdit!cboAuthor = Null
Forms!Frm_LibraryDataEdit!cboTitle.SetFocus

ExitPoint:
Exit Sub
Errorhandler:
If Err.Number = 3134 Then
MsgBox "You must enter the title information first.", vbOKOnly +
vbInformation, "W. Ross Macdonald School"
Me.Undo
Me.cboAuthor = Null
Else
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint
End If
End Sub

 

[Fred Boer]

Dear Ken:

Made a mistake; I used the wrong MDB. Here is the correct AfterUpdate code,
which still seems unrelated to the problem to me...:

Private Sub cboAuthor_AfterUpdate()
On Error GoTo Errorhandler
Dim db As Database, ssql As String

ssql = "INSERT INTO Tbl_BookAuthor (Book_ID, Author_ID) SELECT " &
Forms!Frm_LibraryDataEdit.txtBook_ID & " AS Expr1," &
Forms!Frm_LibraryDataEdit.cboAuthor & " AS Expr2"
Set db = CurrentDb()
db.Execute ssql, dbFailOnError

If db.RecordsAffected <> 1 Then
MsgBox "This is not a valid Book ID. Please try again.", vbOKOnly +
vbInformation, "W. Ross Macdonald School"
Me.Undo
End If

Set db = Nothing
Me.Refresh
Forms!Frm_LibraryDataEdit!cboAuthor = Null
Forms!Frm_LibraryDataEdit!cboAuthor.SetFocus



ExitPoint:
Exit Sub
Errorhandler:
If Err.Number = 3134 Then
MsgBox "You must enter the title information first.", vbOKOnly +
vbInformation, "W. Ross Macdonald School"
Me.Undo
Me.cboAuthor = Null

Else
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint
End If

End

Hi Ken!

Good guess, but there is no GotFocus code... There is code for the
AfterUpdate Event and Not In List Event.

I don't *think* that the AfterUpdate event could be a problem, but I've
appended that code below..

Thanks a lot!
Fred

<snip>

 

[Ken Snell [MVP]]

Hmmmm. The workaround of resetting the focus is not intended to be used for
a NotInList situation, but rather for closing a combo box list that was
opened via other means (such as .DropDown method).

I set up a small test form where I put a combo box (limit to list = yes;
event procedure for not in list event as noted below) and a text box on the
form.

Private Sub Combo2_NotInList(NewData As String, Response As Integer)
MsgBox "no"
Response = acDataErrContinue
Me.Text0.SetFocus
Me.Combo2.SetFocus
End Sub

When I open the form and type in a value that is not in the combo box's list
and hit tab, I get into a continuous loop in the NotInList procedure. I note
this to you because your code has similar construction for when the person
does not want to add the new value. To avoid this loop, the code has to
include an Undo step:

Private Sub Combo2_NotInList(NewData As String, Response As Integer)
MsgBox "no"
Response = acDataErrContinue
Me.Combo2.Undo
Me.Text0.SetFocus
Me.Combo2.SetFocus
End Sub

You should modify your current code accordingly?

Now, to the dropdown issue -- the dropdown list shows because of the
NotInList event occurring. (You can see this if you add a loop into the code
(after setting focus to the other control) to DoEvents up to 500 times --
when the focus goes back to the combo box, the list drops down.) I don't
know of any way to turn this "feature" off.

After I read your reply, a faint memory tickle came into my head that I'd
had a similar situation in a database and had programmed around it.. went
looking for it, but couldn't find it.

So, I put my thinking cap on and came up with a "small" workaround. Turn off
the LimitToList property and remove your code from the NotInList event.
Instead, I think you can use the BeforeUpdate event of the combo box to test
for whether the item is in the list or not, and then to open your popup
form:

Private Sub Combo2_BeforeUpdate(Cancel As Integer)
Dim varValue As Variant
If DCount("*", Me.Combo2.RowSource, "FieldName=" & Me.Combo2.Value) = 0 Then
Me.Combo2.Undo
Cancel = True
If vbYes = MsgBox("add the item?") Then
DoCmd.OpenForm "Frm_InsertAuthor", OpenArgs:=NewData,
DataMode:=acFormAdd, Windowmode:=acDialog
' rest of your code to test for whether item was inserted or not goes here
' including the next requery step as appropriate
Me.Combo2.Requery
End If
End If
End Sub

A bit of a workaround, yes.....

 

[Fred Boer]

Dear Ken:

Thank you for taking so much time to help me! Now that I'm home, I have to
get supper ready, (I've snuck into the basement for a quick peek at the
newsgroups! Shh!), so I can't give this any attention. I hope to deal with
it tomorrow. If I have problems, I'll post back, but at first glance I think
I understand what you suggest. I don't recall falling into a loop using this
process before, but I will certainly check that!


Thanks!
Fred

 

[Fred Boer]

Dear Ken:



Well, it's lunchtime and I've been able to spend a little time working with
the problem.



1. Thanks for the warning about the continuous loop problem. My production
database code doesn't include the two SetFocus lines which create the loop
you described. The code I posted was from a working database, and it
reflected an attempt I had made to solve the dropdown issue. I was careless
in posting my code. Obviously, it had been an unsuccessful attempt! You
did make me think, however, about inserting an "Undo" into my code, so,
thanks!



2. You suggest that I use DoEvents to be able to see what is happening with
the combobox. Could you please explain how to do this? It looks like an
interesting debugging trick, but I'm having trouble figuring out how to do
it. I tried the following, but nothing changed...



Case vbNo

Response = acDataErrContinue

Me.Undo

Me.cmdGoFirst.SetFocus

Dim i As Integer

Do While i < 500

i = i + 1

DoEvents

Loop

Stop

Me.cboAuthor.SetFocus



3. I have to go buy some lunch in the cafeteria, and then I'll try your
proposed solution to the dropdown issue! I think I better plan some seatwork
for my afternoon students!



Cheers!

Fred

 

[Ken Snell [MVP]]

Here is my "sample" code modified with that embedded DoEvents method:


Private Sub Combo2_NotInList(NewData As String, Response As Integer)
Dim intLoop As Integer
MsgBox "no"
Response = acDataErrContinue
Me.Combo2.Undo
Me.Text0.SetFocus
For intLoop = 1 To 500
DoEvents
Next intLoop
Me.Combo2.SetFocus
End Sub

 

[Fred Boer]

Hi Ken:

Thanks for the DoEvents code. It is clear to me now. I will work on
implementing your "dropdown" solution as soon as this job of mine stops
interfering with my Access playtime...

BTW... Lunch was pork ragout. It was delicious!

Cheers!
Fred

 

[Ken Snell [MVP]]

Fred -- just curious for future reference... were you able to make this idea
work in your database?

 

[Fred Boer]

Dear Ken:

First of all, my apologies for not getting back to you. After you were so
kind to help, too. I'm really sorry!

I *did* spend some time on this, but then I got sidetracked by another
issue. ("Sometimes you really dig a girl the moment you kiss her/ And then
you get distracted by her older sister...")

Having resolved the.. uh.. "older sister" issue, I am working on this again.
Would you be kind enough to keep an eye on this thread? I hope to put more
time into this problem today. Actually, I have been working on it a bit this
morning, and have hit a roadblock which has nothing to do with your code:

The combobox has a rowsource with two fields "Author_ID" and
"AuthorFullname". The Afterupdate code uses the Author_ID to update a table,
so the Author_ID has to be the bound field. If the Limit to List property is
set to Yes, I can hide the Author_ID column and use only the AuthorFullName
column. However, I need to set the Limit to List property to "No", and
Access won't let me do this if the first column is bound and hidden. I'm in
the middle of trying to figure this out, so forgive me if my description
isn't clear.

I'll post back here as soon as I make some useful progress!

Thanks!

Fred


Y

 

[Ken Snell [MVP]]

You'll probably need to reverse the order of the two fields in the combo
box's Row Source, and then use code to read the AuthorID value from the
combo box's Column(1) property and write it to wherever it needs to go. Just
remember that you'll need to have a way to get a new Author_ID value when
you add a new AuthorFullName value to the combo box.

--

Ken Snell
<MS ACCESS MVP>

Dear Ken:

First of all, my apologies for not getting back to you. After you were so
kind to help, too. I'm really sorry!

I *did* spend some time on this, but then I got sidetracked by another
issue. ("Sometimes you really dig a girl the moment you kiss her/ And then
you get distracted by her older sister...")

Having resolved the.. uh.. "older sister" issue, I am working on this
again. Would you be kind enough to keep an eye on this thread? I hope to
put more time into this problem today. Actually, I have been working on it
a bit this morning, and have hit a roadblock which has nothing to do with
your code:

The combobox has a rowsource with two fields "Author_ID" and
"AuthorFullname". The Afterupdate code uses the Author_ID to update a
table, so the Author_ID has to be the bound field. If the Limit to List
property is set to Yes, I can hide the Author_ID column and use only the
AuthorFullName column. However, I need to set the Limit to List property
to "No", and Access won't let me do this if the first column is bound and
hidden. I'm in the middle of trying to figure this out, so forgive me if
my description isn't clear.

I'll post back here as soon as I make some useful progress!

Thanks!

Fred


Y

 

[Fred Boer]

Hi Ken:

Spent a bit more time on it. I have the combobox working, and can insert the
new authors into the table. I am having a problem with the following:

1.The combobox before update code runs correctly.
2.A form to add the new author opens, and is loaded with value from
combobox. (Examination shows that the new author record is created properly)
3. On closing the "InsertAuthor" form, Access throws the following error:

#2115 The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing ... from saving data in the field.

I have removed the validation rule for the control, error persists.

Still having fun!

Fred

You'll probably need to reverse the order of the two fields in the combo
box's Row Source, and then use code to read the AuthorID value from the
combo box's Column(1) property and write it to wherever it needs to go.
Just remember that you'll need to have a way to get a new Author_ID value
when you add a new AuthorFullName value to the combo box.

 

[Ken Snell [MVP]]

Sounds as if you're trying to change the value of the control in that
control's BeforeUpdate event. Cannot do that.

Some fellow MVPs suggested an alternative approach to me for a similar
situation (which I just implemented in a database last night):
Have a module-level variable (Private VariableName As ...) that you will
use to flag when something in the BeforeUpdate event requires the value to
be changed.
In the BeforeUpdate event, when that situation occurs, set the global
variable to the "flagged" value (true/false, for example), *do not* cancel
the event, *do not* Undo the record or control, and exit the sub.
In the AfterUpdate event, first lines of code should test for that
variable's value. If it's the "flagged" value, the code then can do the
"magic" stuff and then write the desired value into the control.

To the user, this acts just the same way as the BeforeUpdate setup.
--

Ken Snell
<MS ACCESS MVP>

 

[Fred Boer]

Hi Ken:

Yes, now that you mention it, I realize I am trying to change a value in the
beforeupdate event. Hmm... I've read the proposed solution to that issue.
I'm beginning to wonder about this whole scenario. It seems like I'm having
to do a lot of work for what should be a rather simple operation. I mean,
all I really was after was making a combobox "drop up". Recently, I found
that I had a rather complicated process in my application that I realized I
could greatly simplify. I wonder if the process I am dealing with here could
be simplified, too.

I think, Ken, that I am going to put this to the side for now. I want to
have a good look at the broader picture of what's happening on the form, and
if I can do it in a more straightforward fashion..

I appreciate your help, and I expect this will come in handy another time!

Fred

 

[George Akers]

Fred:
Have you come up with a "drop up" solution? I am having the exact same
problem in a project. That project is actually on hold right now or I
would have picked on this thread a lot sooner and got into it when you
and Ken were talking. Still I would like to find a solution to this
before the project comes back to life.

TIA,
George

 

[Fred Boer]

Dear George:

No, actually I haven't. I've put the issue on the back burner while I dealt
with a reworking of my data entry process. I expect to eventually return to
this problem, but it might be a while yet.

Fred

P.S. You might want to try Ken's idea...

 

[George Akers]

Thanks Fred. My project is on the back burner too. How about posting to
this thread when you start working on it again and I will do the same.

George

 

[Fred Boer]

Be happy to do so, George...

Fred

Thanks Fred. My project is on the back burner too. How about posting to
this thread when you start working on it again and I will do the same.

George