Requery on Subform -- can't do

[my-wings]

This is driving me nuts. Searching old posts and reading the Knowledge Base
Article - 279028 which I found cited (url below) just hasn't helped.

http://support.microsoft.com/defaul...port/kb/articles/q279/0/28.asp&NoWebContent=1

I am running Microsoft Access 2000 (9.0.2720)

I have a form frmBookList which includes a subform,
jcnCollectionsInBooksSubform. The subform has a field CollectionsID, which
is a dropdown list bound to a junction table, jcnCollectionsBooks. (I'm not
sure if that last is important, but thought I'd just toss it in.)

For most other dropdown lists bound to tables, I use a very simple system to
add new entries to the table and have them immediately available in the
dropdown list. The top entry in the table will be "[add new]", and I use an
AfterUpdate event macro to test if the field reads "[add new]". If it does,
I open a form to add the entry to the table. This form just gets closed in
the normal way, and I have another macro attached to the On GotFocus event
of the field I want updated which is nothing but the single action Requery
with the field name specified.

When I try the same thing in my subform, the above sequence of events nets
me the error message mentioned in the Knowledge Base article.

I have tried the fixes given in the Knowledge Base article, but neither
seems to work. The "RunCommand" / Run thing just makes no sense whatsoever
to me, even though I tried it. All that happened was that my screen would
flicker for several seconds when I first entered the field, and then nothing
after I closed the form after adding a new collection.

Then I tried doing the Visual Basic solution and putting my form and field
names into that, it was essentially the same response.

I think the problem may by that I'm trying to use the "On GotFocus" event as
the trigger for this Requery action. Even though my curser is clearly IN
that field after I close the update form, for some reason, this is not being
interpreted as "On GotFocus" in the subform. I am reluctant to attach a
macro to the closing of the update form, because it may not always be called
when frmBookList is open.

I would appreciate any help or suggestions anyone is willing to offer. If
the solution involves Visual Basic, I would be forever grateful if it could
be made childishly clear, as I know almost nothing about Visual Basic. All
of my solutions so far have been macros.

Thanks.

Alice

 

[my-wings]

Thank you for this incredibly quick response. I will try it tonight and let
you know if I'm successful!

Alice

<snip for bandwidth reasons>

 

[my-wings]

I'm sorry to report that this didn't work for me.

I'm assuming here that you're using an OpenForm action to open the second
form. And I'm assuming that this OpenForm action is part of the macro that
you're running on the AfterUpdate event. And I'm assuming that this macro
consists of a single "step" involving a Condition that is comparing the
value in the combo box and then, if it matches the "[add new]" value, you
then open the second form. Stop me if I'm off track here.

All of your assumptions were correct.

So, let's do this. Open the first form in design view, click on the combo
box control, click on the Properties icon on the toolbar, and click on the
Events tab. Go to the AfterUpdate event, click in that box, and drop down
the list for that combo box; select [Event Procedure]. Then click on the
"three-little-dots" box at the far right side of the box. The Visual Basic
Editor will then open, showing three lines (one with text, one blank, and
another with text), with the cursor on the blank line (I assume that the
combo box on your form is named CollectionsID):

Private Sub CollectionsID_AfterUpdate()

End Sub

In this blank line, type the following lines:

If Me.CollectionsID.Value = "[add new]" Then _
DoCmd.OpenForm "Nameofsecondform", , , , _
acFormAdd, acDialog, "True"

Change the text Nameofsecondform to the actual name of the second form.

Close the first form (save the changes!).

This didn't work. I cut and pasted directly from your post, but the form
didn't open up when I selected "[add new]"

Open the second form in design view, click on the command button (I assume
that there is one there) that is used to tell the form that you're done
entering the new value (and that the form should be closed), click on the
Properties icon on the toolbar, and click on the Events tab. Go to the
OnClick event, click in that box, and drop down the list for that combo box;
select [Event Procedure]. Then click on the "three-little-dots" box at the
far right side of the box. The Visual Basic Editor will then open, showing
three lines (one with text, one blank, and another with text), with the
cursor on the blank line (I assume that the command button on your form is
named cmdClose):

Private Sub cmdClose()

End Sub

In this blank line, type the following lines:

If Me.OpenArgs= "True" Then _

[Forms]![frmBookList ]![jcnCollectionsInBooksSubform]![CollectionsID].Requer
y

Close the second form (save the changes!). Close the Visual Basic Editor.

This one didn't work either. I get an error message that says Access
can't find the form frmBookList.

Your visual basic line is nearly identical to the one suggested in the
Knowledge Base article,
http://support.microsoft.com/defaul...port/kb/articles/q279/0/28.asp&NoWebContent=1
which would have been:
Forms!frmBookList!jcnCollectionsinBooksSubform.Form!CollectionsID.SetFocus
Me.Requery

The difference is the Knowledge Base coding uses a "SetFocus" action, then
does "Me.Requery". Also, the Knowledge Base article doesn't show the square
brackets. (Maybe I'm just supposed to know that?) Anyway, I tried adding the
square brackets to the Knowledge Base line, but still nothing. The Knowledge
Base line doesn't give me an error, but it doesn't update the list either.

I can get the Form for adding a new Collection to open up correctly with my
old macro. It's just refreshing the drop down list for the CollectionsID
control. I changed the event from "On GotFocus" to "OnClick", and at least I
have an event that Access recognizes in the subform. But.....here's an
interesting thing....when I tie a macro to the OnClick event and ask for a
Requery, I get an error message (the same one mentioned in the Knowledge
Base article) and that little box that says the action failed, and shows you
where, but THE LIST UPDATES!

Does this help at all? The list is not updating from the current VBA code,
either your initial pass or the code from the Knowledge Base article. Can
that be tweaked somehow? An alternative would be go ahead and use the
Requery macro, then add a couple of return keystrokes to get rid of the
message boxes, but that's pretty messy.

Still hoping!

Alice

 

[Ken Snell]

OK - I need some more info about your form's setup:

RE: Combo box CollectionsID
What is the RowSource for this combo box? (givee the SQL statement if
it's a query)
What is the bound column?
What is the column count?
What is the column width?

RE: Second form
What is the name of the second form?

RE: The error that you say you got: "can't find frmBookList"
When did this error occur?
Was frmBookList open when the error occurred?

Give me the actual VBA codes that you have for the combo box's AfterUpdate
event and the second form's command button's OnClick event. (do a copy/paste
into a message)

Show me the steps that are in your current macros, and identify the events
to which those macros are associated.

Your reference to tying the macro to the OnClick event: is that the OnClick
event of the combo box?

Note: the square brackets that I put in my code for the Forms! etc. stuff
are needed only if you have non-standard characters or blank spaces in your
form or control names. The KB article didn't show them because the example
it uses doesn't contain such characters.

--
Ken Snell
<MS ACCESS MVP>

I'm sorry to report that this didn't work for me.

I'm assuming here that you're using an OpenForm action to open the second
form. And I'm assuming that this OpenForm action is part of the macro that
you're running on the AfterUpdate event. And I'm assuming that this macro
consists of a single "step" involving a Condition that is comparing the
value in the combo box and then, if it matches the "[add new]" value, you
then open the second form. Stop me if I'm off track here.

All of your assumptions were correct.

So, let's do this. Open the first form in design view, click on the combo
box control, click on the Properties icon on the toolbar, and click on the
Events tab. Go to the AfterUpdate event, click in that box, and drop down
the list for that combo box; select [Event Procedure]. Then click on the
"three-little-dots" box at the far right side of the box. The Visual Basic
Editor will then open, showing three lines (one with text, one blank, and
another with text), with the cursor on the blank line (I assume that the
combo box on your form is named CollectionsID):

Private Sub CollectionsID_AfterUpdate()

End Sub

In this blank line, type the following lines:

If Me.CollectionsID.Value = "[add new]" Then _
DoCmd.OpenForm "Nameofsecondform", , , , _
acFormAdd, acDialog, "True"

Change the text Nameofsecondform to the actual name of the second form.

Close the first form (save the changes!).

This didn't work. I cut and pasted directly from your post, but the form
didn't open up when I selected "[add new]"

Open the second form in design view, click on the command button (I assume
that there is one there) that is used to tell the form that you're done
entering the new value (and that the form should be closed), click on the
Properties icon on the toolbar, and click on the Events tab. Go to the
OnClick event, click in that box, and drop down the list for that combo box;
select [Event Procedure]. Then click on the "three-little-dots" box at the
far right side of the box. The Visual Basic Editor will then open, showing
three lines (one with text, one blank, and another with text), with the
cursor on the blank line (I assume that the command button on your form is
named cmdClose):

Private Sub cmdClose()

End Sub

In this blank line, type the following lines:

If Me.OpenArgs= "True" Then _

[Forms]![frmBookList ]![jcnCollectionsInBooksSubform]![CollectionsID].Requer

 

[my-wings]

Thanks for not giving up on me! Here is the information you asked for.
(Short answers are on the same line as your questions:

OK - I need some more info about your form's setup:

RE: Combo box CollectionsID
What is the RowSource for this combo box? (givee the SQL statement if

it's a query)

SELECT [tblCollections].[CollectionsID], [tblCollections].[CollectionType],
[tblCollections].[Description] FROM tblCollections ORDER BY
[tblCollections].[CollectionType], [tblCollections].[Description];

What is the bound column? 1
What is the column count? 3
What is the column width? 0.3";0.8";3.9"

RE: Second form
What is the name of the second form? frmCollections

OK. I've tried so many things with this database, I'm going to set it to the
exact instructions you gave me in the first post answer, with the only
exception that instead of trying to use "On GotFocus" (of the CollectinsID
control) as the trigger event, I'm going to use OnClick.

RE: The error that you say you got: "can't find frmBookList"
When did this error occur?

This error does not occur when I click the down arrow (even though it's
associated with the OnClick event). It occurs after I made my selection.
(Any selection, not just "[add new]".) If I click the down arrow and then
release without making a selection from the list, I don't get the error
message. Here is the VBA code, cut and pasted. (This is the code that's
highlighted in yellow when I click "debug"):

Private Sub CollectionsID_Click()
[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer
y
End Sub

If it's any help, here is the exact error message: "Run-time error '2450';
Microsoft Access can't find the form 'frmBookList' referred to in a macro
expression or Visual Basic Code."

Was frmBookList open when the error occurred?

Yes, because the control I'm clicking is in a subform of frmBooklist.

Give me the actual VBA codes that you have for the combo box's AfterUpdate
event and the second form's command button's OnClick event. (do a copy/paste
into a message)

OK. Here is the AferUpdate code. I don't actually get that far when I use
the VBA code for the OnClick event (because of the error message). When I
change to a macro to open frmCollections from the OnClick event, I can get
past the first error message but then I get the identical error message for
the AfterUpdate code. I've copied and pasted the code below. (This is the
same code that's highlighted when I click "Debug" in the error message):

Private Sub CollectionsID_AfterUpdate()
If Me.CollectionsID.Value = "[add new]" Then _
DoCmd.OpenForm "frmCollections", , , , _
acFormAdd, acDialog, "True"
End Sub

Here is the code from the second form (frmCollections). This form uses a
button labeled "close" and named [Close Collections form]. This was created
with a wizard, so I inserted your code just above the "DoCmd.Close" that was
already there.

Private Sub Close_Collections_form_Click()
On Error GoTo Err_Close_Collections_form_Click
If Me.OpenArgs = "True" Then _




[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer
y
DoCmd.Close
Exit_Close_Collections_form_Click:
Exit Sub
Err_Close_Collections_form_Click:
MsgBox Err.Description
Resume Exit_Close_Collections_form_Click
End Sub

Show me the steps that are in your current macros, and identify the events
to which those macros are associated.

OK. I've replaced my macros with your code, but when I set them back, here's
the story. There are only two, and the AfterUpdate macro is working fine:

AfterUpdate event for CollectionsID control:
grpListUpdates.mcrCollectionNew
Condition: [CollectionsID].[Column](1)="[Add New]"
Action: OpenForm
Form Name: frmCollections
View: Form
Data Mode: Add
Window Mode: Normal

OnClick event for CollectionsID control:
grpListUpdates.mcrCollectionRefresh
Action: Requery
Control Name: CollectionsID

Your reference to tying the macro to the OnClick event: is that the OnClick
event of the combo box?

Yes. Of the CollectionsID combo box.

Thank again!

Alice

 

[Ken Snell]

Ok - thanks for this info. I'll take some time to digest it and then post
back with (hoping!) some solutions for you!

--
Ken Snell
<MS ACCESS MVP>

Thanks for not giving up on me! Here is the information you asked for.
(Short answers are on the same line as your questions:

OK - I need some more info about your form's setup:

RE: Combo box CollectionsID
What is the RowSource for this combo box? (givee the SQL statement if

it's a query)

SELECT [tblCollections].[CollectionsID], [tblCollections].[CollectionType],
[tblCollections].[Description] FROM tblCollections ORDER BY
[tblCollections].[CollectionType], [tblCollections].[Description];

What is the bound column? 1
What is the column count? 3
What is the column width? 0.3";0.8";3.9"

RE: Second form
What is the name of the second form? frmCollections

OK. I've tried so many things with this database, I'm going to set it to the
exact instructions you gave me in the first post answer, with the only
exception that instead of trying to use "On GotFocus" (of the CollectinsID
control) as the trigger event, I'm going to use OnClick.

RE: The error that you say you got: "can't find frmBookList"
When did this error occur?

This error does not occur when I click the down arrow (even though it's
associated with the OnClick event). It occurs after I made my selection.
(Any selection, not just "[add new]".) If I click the down arrow and then
release without making a selection from the list, I don't get the error
message. Here is the VBA code, cut and pasted. (This is the code that's
highlighted in yellow when I click "debug"):

Private Sub CollectionsID_Click()

[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer

y
End Sub

If it's any help, here is the exact error message: "Run-time error '2450';
Microsoft Access can't find the form 'frmBookList' referred to in a macro
expression or Visual Basic Code."

Was frmBookList open when the error occurred?

Yes, because the control I'm clicking is in a subform of frmBooklist.

Give me the actual VBA codes that you have for the combo box's AfterUpdate
event and the second form's command button's OnClick event. (do a copy/paste
into a message)

OK. Here is the AferUpdate code. I don't actually get that far when I use
the VBA code for the OnClick event (because of the error message). When I
change to a macro to open frmCollections from the OnClick event, I can get
past the first error message but then I get the identical error message for
the AfterUpdate code. I've copied and pasted the code below. (This is the
same code that's highlighted when I click "Debug" in the error message):

Private Sub CollectionsID_AfterUpdate()
If Me.CollectionsID.Value = "[add new]" Then _
DoCmd.OpenForm "frmCollections", , , , _
acFormAdd, acDialog, "True"
End Sub

Here is the code from the second form (frmCollections). This form uses a
button labeled "close" and named [Close Collections form]. This was created
with a wizard, so I inserted your code just above the "DoCmd.Close" that was
already there.

Private Sub Close_Collections_form_Click()
On Error GoTo Err_Close_Collections_form_Click
If Me.OpenArgs = "True" Then _

[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer

y
DoCmd.Close
Exit_Close_Collections_form_Click:
Exit Sub
Err_Close_Collections_form_Click:
MsgBox Err.Description
Resume Exit_Close_Collections_form_Click
End Sub

Show me the steps that are in your current macros, and identify the events
to which those macros are associated.

OK. I've replaced my macros with your code, but when I set them back, here's
the story. There are only two, and the AfterUpdate macro is working fine:

AfterUpdate event for CollectionsID control:
grpListUpdates.mcrCollectionNew
Condition: [CollectionsID].[Column](1)="[Add New]"
Action: OpenForm
Form Name: frmCollections
View: Form
Data Mode: Add
Window Mode: Normal

OnClick event for CollectionsID control:
grpListUpdates.mcrCollectionRefresh
Action: Requery
Control Name: CollectionsID

Your reference to tying the macro to the OnClick event: is that the OnClick
event of the combo box?

Yes. Of the CollectionsID combo box.

Thank again!

Alice

 

[Ken Snell]

OK - I have taken a look at your database......

The error that you're getting in frmBookList ("can't find frmBookList") is
occurring because of this line of code that is in the procedure for the
OnClick event of the CollectionsID combo box:








[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer
y

Note the blank space after the word "List". You need to delete that. This
same error is present in your VBA code for the Click event for
Close_Collections_form command button in frmCollections form.

Now, as to the code that you're using in the Click event on frmBookList for
the CollectionsID combo box. The Click event of a combo box occurs when you
select an item in the dropdown list. Your code for that event is this:
Private Sub CollectionsID_Click()








[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer
y
End Sub

You have this code for the AfterUpdate event of the combo box:
Private Sub CollectionsID_AfterUpdate()
If Me.CollectionsID.Value = "[add new]" Then _
DoCmd.OpenForm "frmCollections", , , , _
acFormAdd, acDialog, "True"
End Sub

This code is what is supposed to open your second form. However, the code is
checking for a value of "[add new]" in the combo box. If you check your
BoundColumn and ColumnCount properties for that combo box, you'll find that
the bound column is the first column, so the value of the combo box is the
value of the first column. But [add new] is a value in the second column, so
your code above is never finding the match that you seek. Therefore, the
second form is never being opened.

Thus, because the second form never opens, the code in the Click event then
runs, but it won't add any new info to the combo box's row source list
because this event runs without you having done your "thing" in the second
form to add the new info.

So, get rid of the Click event code for that CollectionsID combo box.
Instead, change the code for the AfterUpdate event to this:
Private Sub CollectionsID_AfterUpdate()
If Me.CollectionsID.Value = "[add new]" Then
DoCmd.OpenForm "frmCollections", , , , _
acFormAdd, acDialog, "True"
















[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer
y
End If
End Sub

The above code will requery the combo box after the second form is closed,
which is what you are seeking to do.

This should get you "in the groove"!

 

[my-wings]

You are a genius and a saint!

My final code for the AfterUpdate event looks like this:

Private Sub CollectionsID_AfterUpdate()
If Me.CollectionsID.Value = "1" Then
DoCmd.OpenForm "frmCollections", , , , _
acFormAdd, acDialog, "True"

[Forms]![frmBookList]![jcnCollectionsinBooksSubform]![CollectionsID].Requery
End If
End Sub

I removed the macros, the CollectionsID OnClick event code, and the code
from the second form, and it all works like a charm.

Thank you so much!

Alice

OK - I have taken a look at your database......

The error that you're getting in frmBookList ("can't find frmBookList") is
occurring because of this line of code that is in the procedure for the
OnClick event of the CollectionsID combo box:

[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer

y

Note the blank space after the word "List". You need to delete that. This
same error is present in your VBA code for the Click event for
Close_Collections_form command button in frmCollections form.

Now, as to the code that you're using in the Click event on frmBookList for
the CollectionsID combo box. The Click event of a combo box occurs when you
select an item in the dropdown list. Your code for that event is this:
Private Sub CollectionsID_Click()

[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer

y
End Sub

You have this code for the AfterUpdate event of the combo box:
Private Sub CollectionsID_AfterUpdate()
If Me.CollectionsID.Value = "[add new]" Then _
DoCmd.OpenForm "frmCollections", , , , _
acFormAdd, acDialog, "True"
End Sub

This code is what is supposed to open your second form. However, the code is
checking for a value of "[add new]" in the combo box. If you check your
BoundColumn and ColumnCount properties for that combo box, you'll find that
the bound column is the first column, so the value of the combo box is the
value of the first column. But [add new] is a value in the second column, so
your code above is never finding the match that you seek. Therefore, the
second form is never being opened.

Thus, because the second form never opens, the code in the Click event then
runs, but it won't add any new info to the combo box's row source list
because this event runs without you having done your "thing" in the second
form to add the new info.

So, get rid of the Click event code for that CollectionsID combo box.
Instead, change the code for the AfterUpdate event to this:
Private Sub CollectionsID_AfterUpdate()
If Me.CollectionsID.Value = "[add new]" Then
DoCmd.OpenForm "frmCollections", , , , _
acFormAdd, acDialog, "True"

[Forms]![frmBookList ]![jcnCollectionsinBooksSubform]![CollectionsID].Requer