# Access 2007 Combo Box Requery



## beltsander (Nov 28, 2011)

I have just been moved from 2003 to 2007 and am dealing with errors in my database. I had a subform with combo boxes that were filtered based on my main form. On the combo box on my main form, I had this code:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Company Number] = " & Str(Nz(Me![Combo6], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me![tbl Customer Cross Reference1].Form.[Combo12].Requery
Me![tbl Customer Cross Reference1].Form.[Combo14].Requery

This updated the values in two combo boxes on my subform to values relevant to the customer on the main form.

Now in 2007, the combo box calls up an embedded macro instead of the original code. In the macro, there is the option to Requery an object. Based on the text that is included:

Enter the name of the control to requery. Use only the name of the control rather than the full syntax. Leave blank to requery the underlying table or query for the object. Must be left blank for datasheets.

I set this to requery the subform and not the combo boxes. This now gives me some odd results in that it will change the values in one of my combo boxes, leaves the other with the original filter set from the form load, and does not call up the correct record for the box that does update.

I also attempted to set this to the combo box itself and to subform!combo box and received errors on both attempts.

Can anyone help me with the code to recreate in 2007 what I had in 2003?


----------



## AlbertMC2 (Jul 15, 2010)

Hi

From what I know (Although I could be mistaken) You cannot run a requery macro on an object that isn't the main object (I don't know if my terminology is correct).
Example: your main form is the main active object so you can requery the main form and also your subform because it is an object on the main form just like you can requery any other object on the main form. But since your combo boxes are objects on the subform they cannot be requeried from the main form *through the use of macros*. Only if your subform was the main form could you do this. 

Therefore there are 2 ways to solve this:
(Since you have not specified how the Code/macro was called lets assume you are clicking a button on the main form)
1. Convert your whole macro to VBA. The code you mentioned above should work in Office 2007. So just add it to your OnClick event of the button.
OR
2. Since the button is on the main form, go into the Visual Basic Editor to the main Forms module (The form should be listed on the left side).
Create a function in this module. I am presuming it is only the requery that is not working in the macro so you can create a Function like:

```
Function UpdateCombo()
    Me![tbl Customer Cross Reference1].Form.[Combo12].Requery
    Me![tbl Customer Cross Reference1].Form.[Combo14].Requery  
End Function
```
Then in the macro change the requery to runcode. Then in the arguments give the name of the function: UpdateCombo() 

Hopefully one of these methods will work for you.


----------



## beltsander (Nov 28, 2011)

Hi Albert

I attempted the easy approach first and converted my combo box back to run the VBA code. With this approach, the combo box updates, but the form does not.

I then tried the second path and the main form updates, but it gives me an error 

The expression you entered has a function name that Microsoft Office Access can't find.

Which brings up an Action Failed box showing the UpdateCombo () Argument and error number 2950 where the only option is to stop all macros.

Not sure why, but it seems to be ignoring all VBA code since I updated to 2007. Is there a box somewhere in Access that allows you to turn this on or off?


----------



## AlbertMC2 (Jul 15, 2010)

Hi



> The expression you entered has a function name that Microsoft Office Access can't find.


This means that the function *UpdateCombo *cannot be found. Did you write the VBA Function UpdateCombo in your main form's module? And is it a function? (ie not a Private Sub) 

I see what I forgot to mention in point #1 above (in my previous post) is that when you convert the macro back to the VBA code that you had, you will have to go to the properties of the button on the main form -> go to Event tab and change the "On Click" event from [Embedded Macro] to [Event Procedure]

In Access 2007 and 2010 it seems that Macros are the default event over VBA code. This however does not mean that VBA has been disabled. The macros are supposed to be easier to work with - Kind of like a wizard instead of writing a whole lot of code.


----------



## beltsander (Nov 28, 2011)

Hello

Yes, I did update the properties for the after update to call the event procedure instead of the macro. For some reason it is not recognizing the code. Note this routine is called by a combo box on the main form.

The code for the UpdateCombo is a function and not a Private Sub. Actual VBA code for both options below for reference (note that in this process I have tried several different steps and so some of the combo box numbers have changed from my original post. Also, I moved the lookup reference to the record key to try and improve performance.):


Function UpdateCombo()

Me![tbl Customer Cross Reference1].Form.[Combo23].Requery
Me![tbl Customer Cross Reference1].Form.[Combo21].Requery

End Function

Private Sub Combo87_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo87], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me![tbl Customer Cross Reference1].Form.[Combo23].Requery
Me![tbl Customer Cross Reference1].Form.[Combo21].Requery

End Sub

I have added a button to my subform to Refresh the underlying query and update the combo boxes as a manual secondary step. This does update the fields, but requires an extra step. I also attempted to use the GoToControl to have the macro in effect push the button and then return to the main form as part of the routine. However, with this approach, anytime I attempted to go to the subform it automatically returned me to the main form. I tried using the stop macro and stop all macros at the end of the sequence, but this did not correct the issue.


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Can you give me a bit more info on what it is that your main form and subform does?
What is the relationship between Combo87 on the main form and the 2 comboboxes (Combo23 and Combo21) on the subform? 
I understand you select an ID/Company Number in the main form's Combobox but how is that related to the subform's comboboxes?


----------



## beltsander (Nov 28, 2011)

The main form displays the basic customer information. Under this form, I have a set of five tabs that displays a sub form with some different information about the current customer (sales, projects, etc.) 

The subform I am working with has a list of part numbers cross referencing between my number and theirs. The data set pulls from a table that includes a key, account number, and the two part numbers. The information that displays is filtered by master/child field to relate only to the current customer. However, the combo box that searches the data does not filter by this criteria at the same time. 

To limit the data, I had originally used the VBA code from the original post and it worked fine. Since I have upgraded, I have had no luck using VBA in the database. I have been able to redo most items with the embedded macros, but this one is just not working. Two lines of code seemed a pretty simple way to do this originally, but maybe not the best way?


----------

