Link to home
Start Free TrialLog in
Avatar of Bob Collison
Bob CollisonFlag for Canada

asked on

Access 2010 ComboBox Requery Not Working

Hi Experts,

For some reason the following code does not cause the ComboBox to Requery.  As a result the lookup field doesn't display any selections.

There query that is being run contains a Parameter previously selected on the form hence the need to requery so that only the subset of data specified by the Parameter is in the list in the ComboBox.

The 'DEBUG' Field contains the value of the Parameter so that I can see that it is populated and that it contains the correct data, which it does.

Private Sub F22500_LKUP_ORG_MEMBER_KEY_GotFocus()
On Error GoTo STEP_999
GoTo STEP_100
STEP_100:
Dim DEBUG_F22500_PARM_PERIOD_ORG_KEY As String
DEBUG_F22500_PARM_PERIOD_ORG_KEY = F22500_PARM_PERIOD_ORG_KEY
GoTo STEP_110
STEP_110:
Me!F22500_LKUP_ORG_MEMBER_KEY.Requery
GoTo STEP_900
STEP_900:
'Step 900-Shutdown.
STEP_990:
'Step 990-Exit Event.
Exit Sub
STEP_999:
'Step 999-Form Error Handler Code.
Dim M00000_999 As Form
Set M00000_999 = [Forms]![M-00-000 - Scout Admin System - Main Menu]
Dim DEBUG_ERR_MSG_OPTION_999 As String
'DEBUG_ERR_MSG_OPTION_999 = "Yes" 'Un-Comment for use.
If M00000_999!LKUP_SYS_ERR_MSG_OPTION = "Yes" Or DEBUG_ERR_MSG_OPTION_999 = "Yes" Then
   MsgBox Err.DESCRIPTION & " (" & Err.Number & ")"
   Resume STEP_990
Else
   Resume STEP_990
End If
End Sub

Any ideas why this isn't working and/or is there a better way to do this?

Thanks,
Bob C.
Avatar of PatHartman
PatHartman
Flag of United States of America image

It took me a bit to make sense of the code.  It is quite dense and not well formatted as well as being a style I haven't seen in over 40 years.  I'm not sure I've ever seen this waterfall, goto style in VBA but there were adherents back in the 70's when I was working with COBOL/CICS.

The answer is, as long as the RowSource query of the combo references the F22500_PARM_PERIOD_ORG_KEY form field, requering in the GotFocus event will work.  Just setting the variable in this procedure doesn't affect the RowSource query.  It should have a WHERE clause something like:

Where ORG_KEY = Forms!yourform!somecontrolname

Personally, I use the AfterUpdate event of the controlling combo or text field to requery the dependent combo.
Avatar of Bob Collison

ASKER

Hi Pat,

Well I'm from the old school too!  The GoTos I put in here primarily to control while I was debugging.  I do use it in complicated events as I find it makes debugging easy.

As mentioned the Requery isn't working and I can't figure out why.

I'm not sure where the Where ORG_KEY = Forms!yourform!somecontrolname would apply.  Can you clarify?

I will try the AfterUpdate Event for the Requerying.

Thanks,
Bob C.
The RowSource of the dependent combo is or should be a query.  Please post it and tell us the name of the form and the name of the control that controls the combo.
Hi Pat,

The Form Name Is:
F-22-500 - Organization Member Reports Form

The Name of the field (unbound) that is being Requeried is:
F22500_LKUP_ORG_MEMBER_KEY

The RowSource is the Query below.

Here is the Query Source Code.
SELECT [22_ORGANIZATION_MEMBER_MSTR].ORG_MEMBER_KEY, [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LKUP_LONG_NAME] AS MEMBER_ORG_LKUP, [22_ORGANIZATION_MEMBER_MSTR].PADDED_PERIOD_KEY, [22_ORGANIZATION_MEMBER_MSTR].PERIOD_ORG_KEY
FROM (22_ORGANIZATION_MEMBER_MSTR INNER JOIN 21_ORGANIZATION_MSTR ON [22_ORGANIZATION_MEMBER_MSTR].PERIOD_ORG_KEY = [21_ORGANIZATION_MSTR].PERIOD_ORG_KEY) INNER JOIN 10_MEMBER_MSTR ON [22_ORGANIZATION_MEMBER_MSTR].SYS_MEMBER_KEY = [10_MEMBER_MSTR].SYS_MEMBER_KEY
WHERE ((([22_ORGANIZATION_MEMBER_MSTR].PERIOD_ORG_KEY) Like [Forms]![F-22-500 - Organization Member Reports Form]![F22500_PARM_PERIOD_ORG_KEY]))
ORDER BY [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LKUP_LONG_NAME];

I tried the Requery in the AfterUpdate Event of the field where the value is selected but it doesn't work either.

Thanks,
Bob C.
LIKE should only be used if the user is entering partial values and you would need to use wildcards either at the beginning or end of the string or both.  If this control is a combo, then change the relational operator to =.  If the user really is entering a partial string, add the appropriate wild cards.

Make sure that the form with the referenced control is open.  What happens if you run the query directly?  Do you get the list you expect?

For future reference, do not use embedded spaces or special characters in column or object names.  You are forcing Access to require that everything be encased in square brackets which makes everything even harder to read.  Just because Access lets you get away with it doesn't make it good practice.
Hi Pat,

Thanks for all your comments.

Many of the fields I use for filtering use partial values.  e.g. Postal Codes.  Unless I make a mistake I only use Like where partial values apply and Equal when whole values apply.  I had tried both Like and Equal in the code where it is currently Like.  It was originally Equal as this value will never be partial.

Thanks for your comment regarding Spaces and Special Characters in Column / Object Names.  Yes I do have spaces in Form / Report / Query Names so I guess I should eliminate the Spaces which is not too difficult over time  I also have Underscores in Table / Field Names and I assume that they are also undesirable.  Is this correct?  What is the best way to code them?  Is Upper Camel Case formatting acceptable / best for these Objects as well?  I want to get it right as this is a major code change and obviously I would prefer not to have to drop the use of the Underscores.

The Form is open when the query is run.  If I hard code the values (including those with partial values) in the query it works correctly.

Thanks,
Bob C.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Pat,

Thanks for the suggestions clarifications.

I have modified the Query Design to have the following SQL Code and I still have the problem.

SELECT [22_ORGANIZATION_MEMBER_MSTR].ORG_MEMBER_KEY, [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LKUP_LONG_NAME] AS MEMBER_ORG_LKUP, [22_ORGANIZATION_MEMBER_MSTR].PADDED_PERIOD_KEY, [22_ORGANIZATION_MEMBER_MSTR].PERIOD_ORG_KEY
FROM (22_ORGANIZATION_MEMBER_MSTR INNER JOIN 21_ORGANIZATION_MSTR ON [22_ORGANIZATION_MEMBER_MSTR].PERIOD_ORG_KEY = [21_ORGANIZATION_MSTR].PERIOD_ORG_KEY) INNER JOIN 10_MEMBER_MSTR ON [22_ORGANIZATION_MEMBER_MSTR].SYS_MEMBER_KEY = [10_MEMBER_MSTR].SYS_MEMBER_KEY
WHERE ((([22_ORGANIZATION_MEMBER_MSTR].PERIOD_ORG_KEY) Like [Forms]![F-22-500 - Organization Member Reports Form]![F22500_PARM_PERIOD_ORG_KEY] & "*"))
ORDER BY [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LKUP_LONG_NAME];

Note the addition of the & "*" to the WHERE Clause.

Thanks,
Bob C.
Does the query return the list you expect when you run it directly (make sure the form is open and there is a value in the referenced control)?
Hi Pat,

Yes the form is open and yes the Query in ComboBox B (referred to below) runs directly ok.

The issue is with the Query being refreshed in ComboBox B in the scenario below.  i.e. The effect is that the ComboBox B Refresh is not working.

Immediately prior to the cursor going to ComboBox A where the Like [Forms]![F-22-500 - Organization Member Reports Form]![F22500_PARM_PERIOD_ORG_KEY] & "*" value is set in its field in the form and can be seen as having been set when the LostFocus Event is triggered.  The LostFocus Event also does a SetFocus to the next ComboBox B Field where the GotFocus Event does the Requery to provide a list in ComboBox B.  The issue is that there is nothing in the ComboBox B to select.

Thanks,
Bob C.
Can you post a database with just the form and the tables needed to power it?

I'm not sure why you are attempting to control how a user moves through the controls.  If you set the tab order, Access will handle movement as long as the user tabs.  If the user clicks into a different field than the one that is logically "next", Access will allow it and your code shouldn't care.  Once you start adding code for stuff like this, you end up fighting with Access for control over the form and you will always loose.
Hi Pat,

This form is used to run a number of different reports (4).  The reports require different sets of parameters, some shared and some unique to the reports.

Therefore depending upon the requirements for the report selected, it prompts for the required parameters for that particular report.

This is why I can't use the standard tabbing functionality.  In fact the tabbing isn't an issue as I have other forms that do the same thing but for different sets of reports and their associated parameters without this problem.

Thanks,
Bob C.
On my forms that control multiple reports with various criteria, I use a table to define what is required/optional/not used and show/hide the controls so that only the controls that are available are visible so that automatically controls where the user can tab.  Then I highlight the label if the field is required so those fields stand out more.

Controlling one combo from another is a fairly trivial task and in theory what you are doing should work so there is obviously some disconnect that I'm only going to see if I have access to the form that isn't working.
Hi Pat,

Thanks for the insight into what you use.

Due to the size of my application and the complexity of the form and associated tables its very difficult to provide a copy for you to take a look at.

I'm trying a different approach that probably will ultimately be a better way to do this but it will take a couple of days so I won't close this case probably until Monday.

Thanks again,
Bob C.
Hi Pat,

My new approach is to have a Generic table to store the Lookup Data.  The data is created on the fly for each Lookup using a Unique Key based On the User Id, Parent Object and Child Object and deleted from the table when it is no longer needed.  The ComboBox Source is then the Generic table.

Thanks for all you insight / assistance.
Bob C.
That leads me to believe that the text strings were not properly delimited in the query.  The query needs to evaluate to:

Where SomeField Like "xxxx*"

So that the string xxx followed by the wildcard is enclosed in quotes and I don't think your SQL was doing that.
Hi Pat,

You may well be correct.  Unfortunately at this time I can't test it but I have made a note in my documentation to make sure I do it in the future.

Thanks,
Bob C.