Solved

Access 2010 ComboBox Requery Not Working

Posted on 2016-11-16
17
24 Views
Last Modified: 2016-11-22
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.
0
Comment
Question by:Bob_Collison
  • 9
  • 8
17 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41890291
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.
0
 

Author Comment

by:Bob_Collison
ID: 41890338
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41890388
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.
0
 

Author Comment

by:Bob_Collison
ID: 41890407
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41890429
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.
0
 

Author Comment

by:Bob_Collison
ID: 41891414
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.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41891667
My personal preference is CamelCase although others like to use the_underscore to separate words.  Valid characters are upper/lower case letters, numbers, and the underscore so if you like the underscore, use it.  I tend to use it only for emphasis.

When you use the LIKE operator, you MUST use some wild card character either in front of or behind the string otherwise it works like the = operand.  The most common one is the *.

WHERE [22_ORGANIZATION_MEMBER_MSTR].PERIOD_ORG_KEY Like [Forms]![F-22-500 - Organization Member Reports Form]![F22500_PARM_PERIOD_ORG_KEY & "*"
0
 

Author Comment

by:Bob_Collison
ID: 41891785
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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 34

Expert Comment

by:PatHartman
ID: 41891828
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)?
0
 

Author Comment

by:Bob_Collison
ID: 41891934
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41892153
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.
0
 

Author Comment

by:Bob_Collison
ID: 41892185
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41892276
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.
0
 

Author Comment

by:Bob_Collison
ID: 41892387
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.
0
 

Author Closing Comment

by:Bob_Collison
ID: 41898004
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41898011
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.
0
 

Author Comment

by:Bob_Collison
ID: 41898081
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now