Bob Collison
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_O RG_KEY As String
DEBUG_F22500_PARM_PERIOD_O RG_KEY = F22500_PARM_PERIOD_ORG_KEY
GoTo STEP_110
STEP_110:
Me!F22500_LKUP_ORG_MEMBER_ KEY.Requer y
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_MS G_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.
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
On Error GoTo STEP_999
GoTo STEP_100
STEP_100:
Dim DEBUG_F22500_PARM_PERIOD_O
DEBUG_F22500_PARM_PERIOD_O
GoTo STEP_110
STEP_110:
Me!F22500_LKUP_ORG_MEMBER_
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_MS
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.
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!somecontrol name would apply. Can you clarify?
I will try the AfterUpdate Event for the Requerying.
Thanks,
Bob C.
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!somecontrol
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.
ASKER
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_MS TR].ORG_ME MBER_KEY, [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LK UP_LONG_NA ME] AS MEMBER_ORG_LKUP, [22_ORGANIZATION_MEMBER_MS TR].PADDED _PERIOD_KE Y, [22_ORGANIZATION_MEMBER_MS TR].PERIOD _ORG_KEY
FROM (22_ORGANIZATION_MEMBER_MS TR INNER JOIN 21_ORGANIZATION_MSTR ON [22_ORGANIZATION_MEMBER_MS TR].PERIOD _ORG_KEY = [21_ORGANIZATION_MSTR].PER IOD_ORG_KE Y) INNER JOIN 10_MEMBER_MSTR ON [22_ORGANIZATION_MEMBER_MS TR].SYS_ME MBER_KEY = [10_MEMBER_MSTR].SYS_MEMBE R_KEY
WHERE ((([22_ORGANIZATION_MEMBER _MSTR].PER IOD_ORG_KE Y) Like [Forms]![F-22-500 - Organization Member Reports Form]![F22500_PARM_PERIOD_ ORG_KEY]))
ORDER BY [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LK UP_LONG_NA ME];
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.
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_MS
FROM (22_ORGANIZATION_MEMBER_MS
WHERE ((([22_ORGANIZATION_MEMBER
ORDER BY [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LK
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.
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.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_MS TR].ORG_ME MBER_KEY, [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LK UP_LONG_NA ME] AS MEMBER_ORG_LKUP, [22_ORGANIZATION_MEMBER_MS TR].PADDED _PERIOD_KE Y, [22_ORGANIZATION_MEMBER_MS TR].PERIOD _ORG_KEY
FROM (22_ORGANIZATION_MEMBER_MS TR INNER JOIN 21_ORGANIZATION_MSTR ON [22_ORGANIZATION_MEMBER_MS TR].PERIOD _ORG_KEY = [21_ORGANIZATION_MSTR].PER IOD_ORG_KE Y) INNER JOIN 10_MEMBER_MSTR ON [22_ORGANIZATION_MEMBER_MS TR].SYS_ME MBER_KEY = [10_MEMBER_MSTR].SYS_MEMBE R_KEY
WHERE ((([22_ORGANIZATION_MEMBER _MSTR].PER IOD_ORG_KE Y) Like [Forms]![F-22-500 - Organization Member Reports Form]![F22500_PARM_PERIOD_ ORG_KEY] & "*"))
ORDER BY [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LK UP_LONG_NA ME];
Note the addition of the & "*" to the WHERE Clause.
Thanks,
Bob C.
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_MS
FROM (22_ORGANIZATION_MEMBER_MS
WHERE ((([22_ORGANIZATION_MEMBER
ORDER BY [SYS_MEMBER_LKUP] & " ~ " & [21_ORGANIZATION_MSTR].[LK
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)?
ASKER
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.
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_
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.
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.
ASKER
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.
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.
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.
ASKER
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.
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.
ASKER
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.
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.
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.
ASKER
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.
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.
The answer is, as long as the RowSource query of the combo references the F22500_PARM_PERIOD_ORG_KEY
Where ORG_KEY = Forms!yourform!somecontrol
Personally, I use the AfterUpdate event of the controlling combo or text field to requery the dependent combo.