Refreshing a drop down list on a form 2

[domino3]

I have a form with a field of categories populated from a drop down box. I have created a button for users to click when they wish to add a new category. This opens a query in alphabetical order, which they can then add a new category to the bottom of.

I would like the users to be able select the record they want, and then be able to add the new category if necessary by cicking the button, then the query closing down and the same record still showing on the screen.

I have tried adding requery to the button on exiting the button, but that just clears the screen completely of any record. I've also tried adding me.requery to on click, but that doesn't show the new category on the form in my field.

The code I now have is:

Private Sub Command31105_Click()
On Error GoTo Err_Command31105_Click
Dim stDocName As String
stDocName = "Q_resource_categories"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Me.Requery
Exit_Command31105_Click:
Exit Sub
Err_Command31105_Click:
MsgBox Err.Description
Resume Exit_Command31105_Click
End Sub

Can anyone tell me how to refresh the list without losing the record already on the screen? Thank you.

 

[MajP]

What is happening is when you open the query code execution does not stop so the requery happens immediately, before you add anything to the query. The trick that I use is to open a form for editing, but open it as "dialog". This will stop code execution until the pop up form is closed and then the original form requeries.

 

[postermmxvicom]

domino3,

I believe I fixed a similar problem this way:

Private Sub FKCustomer_GotFocus()

DoCmd.RunCommand acCmdRefresh

Exit_FKCustomer_GotFocus:
Exit Sub

Err_FKCustomer_GotFocus:
MsgBox Err.Description
Resume Exit_FKCustomer_GotFocus

End Sub



This way, after the user had clicked my "add new customer button" when they clicked the drop down box, it would gain focus and trip the refresh.

I thought of this solution because hitting F5 (refresh) would cause the forms drop down lists to be populated with the latest data. Does this help? Or is your situation different?

 

[domino3]

The idea above sounds good, but I'm having a problem getting the code to to work. I've added the code but am getting the following error message when I click on the drop down list in the open form:

"The expression On Got Focus you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or Active X Control."

I don't how to fix the error and the rest of the form seems to work fine.

Any help would be much appreciated. Thank you.

 

[domino3]

I didn't know if I'd amended the code incorrectly, so Ive trimmed in down to the following:

Private Sub Combo_category_GotFocus()
DoCmd.RunCommand acCmdRefresh
End Sub

But I still get the same error message. All other macros on the form work OK. I've tried removing the code completely, which removes the error message. But then when I put the code back the error message reappears.

 

[domino3]

What I've also tried to do is put an on lost focus on the click button which supplies the list that can be added to. I don't get an error message, but I don't get the list refreshed on the combo box either.

The code I have is:

Private Sub Command31105_LostFocus()
DoCmd.RunCommand acCmdRefresh
End Sub

Can someone let me know if acCmdRefresh is still the correct code to refresh the list in a different control to the one with the code in, or what I should change it to if is wrong? Thank you.

 

[ProgramError]

try
me.requery
or
thenameofcontrol.requery
instead of refresh


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[domino3]

I'm now trying

Private Sub Command31105_LostFocus()
Combo_category.Requery
End Sub

in the lost focus of the click button I have which brings up the query with the list of categories. The combo_category I am trying to refresh is the combo list populated from the table which the query adds records to, if that makes sense.

I'm now getting a compile error, variable not defined error message when I click on the button to open the query list.

 

[maxhugen]

You need to do as MajP suggested: instead of opening a query, open a form based on that query (or table) in dialog mode:

DoCmd.OpenForm "myForm", , , , , acDialog

Refreshing the form won't help, you need to requery the combo itself. Your code could look like this:

Private Sub Command31105_Click()
On Error GoTo Err_Command31105_Click
Dim stDocName As String

    stDocName = "frmResourceCategories"

    ' once you open the form in dialog mode, this code
    ' will halt until the form is closed
    DoCmd.OpenForm stDocName, , , , , acDialog

    ' now that the form has closed, this code will
    ' continue, so we can now requery the combo
    Combo_category.Requery

Exit_Command31105_Click:
    Exit Sub
Err_Command31105_Click:
    MsgBox Err.Description
    Resume Exit_Command31105_Click
End Sub

Max Hugen
Australia

 

[postermmxvicom]

domino3,

I do believe our situations are similar, and that the fix I used will work. However, I must admit I am fairly green at all of this and am more of a hack than an expert - so I would have to defer to the opinion of quite a number of people here.

That being said:

Let me walk you through what I did that got me to a solution that worked.

I first noticed that after adding a new category and returning to my form (which did not display the new category) that if I pressed F5 (refresh) the drop down list would then contain the new choice.

So, I added a refresh button (because I can't expect my users to hit F5). I had the control wizard turned on, added the button and chose "Form operations -> refresh form data". This button accomplished the same thing that F5 did, however, that was still pretty user unfriendly.

So, I copied the code that access made for the button into the GotFocus event of the drop down box - and it worked and all was well.

(I then deleted the now useless button)

Now, my users can either select from the list or hit a button to add another category and when they return to the form, the list shows them the new choice they added among the other choices.

I do not know what anybody else is talking about because I am a hack, so I can't weigh in on what they are saying.

Best of luck, if you get it working please let us know how, so that I may also learn.

Aaron

 

[postermmxvicom]

And to those suggesting the requery, thanks. That is apparently the solution to a different problem of mine, that refresh didn't work with.

 

[domino3]

Thanks for all the replies. I tried your suggestion of a refresh button but get a similar error message to before:

"The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or Active X Control."

I don't get this message with any other form, so am at a loss as to how to continue.

 

[postermmxvicom]

Well, like I said, I am real green, so...iono...I can only say what I did for me, and what it does for me.

But, I am curious, does manually pressing F5 accomplish anything for you as it did for me in my situation?

and

Did you have any luck with with maxhugen's elaboration on MajP's advice?

 

[domino3]

I've now got it working by creating the input form suggested in MajP's posting. Why I didn't do that to begin with I don't know. Thank you all very much for the help and suggestions.

 

[MajP]

To understand what is going on here, this how it works. Assume you have a control such as a combobox that draws it rowsource from a table or query. When you open a form the controls on the form draw their rowsource data. If you change the underlying data nothing happens to the controls data because unless you force it to requery it will not repopulate the rowsource. So the trick to doing what you want can be done in many ways as long as you get the events to take place in the right order:
1) Leave the form you are working on
2) Update the data
3) return to your form and requery the control to show the updated data

Opening a form in dialog is a common solution to this because it allows you to write a procedure that halts code execution while you update your data on the form. Once the dialog form closes the code continues to execute from where you opened the dialog form.

But you could do this other ways as well. As long as you requery the control after you update the data. For example you could always requery the control on the got focus event. However this could be inefficient if the data does not change often.

To see why your original solution did not work you could put a msgbox right after "Me.requery". You would find that as soon as you opened your query the message would pop up before you could enter data. So it would just draw the same old records.

 

[davegillian]

I had exactly the same problem and there's a very simple solution.

For the combo box with the drop down you need the new item adding to add an event for On Got Focus as follows:

Private Sub Combo_GotFocus()

Me.Recalc

End Sub

 

[davegillian]

ACtually I just tried this a bit futher. It seems to work fine on a normal form but on the subform for some reason it gets confused and there's a lot of flickering on screen, an hourglass and a message in the bottom left saying "Calculating" and for the form stops responding.

However...

I have tried:

#Private Sub Combo_GotFocus()

Me.Refresh

End Sub

and it works perfectly so who cares?!

 

[Yardyy]

Hi, I know this threads a bit old, but just to let you know the suggestion of davegillian worked perfect for me, i put the code on the got focus event of the combo box, and pow it works just like it shoudld do. Thanks to all who have contributed in this thread.

Many Thanks
Yurov Ardyy

 

[davegillian]

Glad to hear it! I'm doing something right at least!!!