Stick Values into ComboBox

[AustinMan]

I have a form defaulted as Continuous Forms.

Here is my code in back of form.


Private Sub Form_AfterUpdate()

Dim x1 As String
Dim x2 As String

Dim strSQL As String

If Combo1.Value = "ALL" And Combo2.Value = "ALL" Then

strSQL = "SELECT [Personnel Combined Table].[Professional Title], Sum([Personnel Combined Table].[FTE Total Employees]) AS [FTE Total Employees], Sum([Personnel Combined Table].[FTE Total]) AS [FTE Total], Avg([Personnel Combined Table].[Average Annual Salary]) AS [Average Annual Salary], Max([Personnel Combined Table].[Maximum Annual Salary]) AS [Maximum Annual Salary], Min([Personnel Combined Table].[Minimum Annual Salary]) AS [Minimum Annual Salary]"
strSQL = strSQL & " FROM [Personnel Combined Table] GROUP BY [Personnel Combined Table].[Professional Title];"

Else
If Combo1.Value = &quot;ALL&quot; And Combo2.Value <> &quot;ALL&quot; Then
x2 = &quot;ALL&quot;
x1 = Combo2.Value

strSQL = &quot;SELECT [Personnel Combined Table].[Professional Title],[Personnel Combined Table].[Agency Type], Sum([Personnel Combined Table].[FTE Total Employees]) AS [FTE Total Employees], Sum([Personnel Combined Table].[FTE Total]) AS [FTE Total], Avg([Personnel Combined Table].[Average Annual Salary]) AS [Average Annual Salary], Max([Personnel Combined Table].[Maximum Annual Salary]) AS [Maximum Annual Salary], Min([Personnel Combined Table].[Minimum Annual Salary]) AS [Minimum Annual Salary]&quot;
strSQL = strSQL & &quot; FROM [Personnel Combined Table] GROUP BY [Personnel Combined Table].[Professional Title],[Personnel Combined Table].[Agency Type] HAVING ((([Personnel Combined Table].[Agency Type])= &quot; & Chr(34) & x1 & Chr(34) & &quot);&quot;

GoTo QueryMaker

Else
If Combo1.Value <> &quot;ALL&quot; And Combo2.Value = &quot;ALL&quot; Then
x2 = Val(Right([Combo1], 2))
x1 = &quot;ALL&quot;

strSQL = &quot;SELECT [Personnel Combined Table].[Professional Title],[Personnel Combined Table].[HHSC Region], Sum([Personnel Combined Table].[FTE Total Employees]) AS [FTE Total Employees], Sum([Personnel Combined Table].[FTE Total]) AS [FTE Total], Avg([Personnel Combined Table].[Average Annual Salary]) AS [Average Annual Salary], Max([Personnel Combined Table].[Maximum Annual Salary]) AS [Maximum Annual Salary], Min([Personnel Combined Table].[Minimum Annual Salary]) AS [Minimum Annual Salary]&quot;
strSQL = strSQL & &quot; FROM [Personnel Combined Table] GROUP BY [Personnel Combined Table].[Professional Title],[Personnel Combined Table].[HHSC Region] HAVING ((([Personnel Combined Table].[HHSC Region])= &quot; & x2 & &quot);&quot;

GoTo QueryMaker
Else
x1 = Combo2.Value
x2 = Val(Right([Combo1], 2))
strSQL = &quot;SELECT [Personnel Combined Table].[Professional Title],[Personnel Combined Table].[Agency Type],[Personnel Combined Table].[HHSC Region], Sum([Personnel Combined Table].[FTE Total Employees]) AS [FTE Total Employees], Sum([Personnel Combined Table].[FTE Total]) AS [FTE Total], Avg([Personnel Combined Table].[Average Annual Salary]) AS [Average Annual Salary], Max([Personnel Combined Table].[Maximum Annual Salary]) AS [Maximum Annual Salary], Min([Personnel Combined Table].[Minimum Annual Salary]) AS [Minimum Annual Salary]&quot;
strSQL = strSQL & &quot; FROM [Personnel Combined Table] GROUP BY [Personnel Combined Table].[Professional Title],[Personnel Combined Table].[Agency Type],[Personnel Combined Table].[HHSC Region] HAVING ((([Personnel Combined Table].[Agency Type])= &quot; & Chr(34) & x1 & Chr(34) & &quot AND (([Personnel Combined Table].[HHSC Region])= &quot; & x2 & &quot);&quot;

GoTo QueryMaker
End If
End If
End If


QueryMaker:
'Combo1.Value = x2
'Combo2.Value = x1
Me.RecordSource = strSQL
Me.Requery
strSQL = &quot;&quot;

I have no problem changing the values of the combobox if there are records that previously match the criteria.

When I select a combination when no records match the criteria, the values in the comboboxes are orphaned, not sticking. I am trying to reset the comboboxes when no records are found to allow the combobox to accept values again. When I try to reselect, the value is selected, but the bound field do not take place, becomes invisible and not selected. I hope I am making myself clear.

Any suggestions would be great.

Thanks in advance!!

 

[Blanch]

Without seeing the app, I might suggest a couple of things. Ensure that you have quotes around parameters that are strings in your SQL. Also you do not need to use the Goto Keyword in this case, the If Then Blocks will terminate properly. You could make all of the IF Blocks IF...THEN...ELSEIF...THEN...ELSE...END IF blocks. You might want to look into using line continuation characters for readability. And lastly, It apears that the top line of SQL is the same for ever query. Set it's value once outside the IF Block and append the Where clause based on choices.

Hope this helps!

Jim

 

[AustinMan]

There are slight differences in the top line of the SQL statement.

The form works making a selection on the comboboxes and execute.

The SQL statements work OK.

The problem I am having is that when the combination of values of the two comboboxes (one selected value per combobox) does not produce a recordset, the values used to select are there, are shown visually; but when I try to reselect a different combination in the comboboxes (a change), in either one or both, the values in the combobox are visible to be selected; however, the value once selected does not appear in the combobox as the selected value, it goes invisible until a combination that has records are available. Even at that time, the values are not visible until you re-enter the combobox. It recognizes the value used, it just does not refill the selected value intially. Hope this helps.

What event or process are necessary to reset the comboboxes. Their values are based on queries. I tried Requery, Repaint, and Refresh. I do not where in Combobox events it would be appropriate to &quot;reset&quot; the combobox.

This code is behind the form not in a module.

Thanks again for your quick reply.

 

[Blanch]

Are the combobxes bound or unbound? If they are unbound you should not have any trouble with them. If you needed to &quot;reset&quot; the comboboxes the best way would be to do the following:

Dim strSql as string
with combo1
strSql = .rowsource
.rowsource = &quot;&quot;
.requery
.rowsource = strsql
.requery
end with

try this little trick, it's not pretty, but it will &quot;reset&quot; the SQL statement.

 

[AustinMan]

Thanks again Jim for your quick response.

I tried that technique but the comboboxes are still behaving strangely.

I checked for missing references - none.

Yes, Both of these comboboxes are unbound.

The Queries of these unbound forms are union queries inserting the option for &quot;ALL&quot;.

It is driving me bonkers. When records are returned, these are summary totals by Professional Title, everything is swell. When no records matching the criteria are returned, the values are visually there. The comboboxes, upon re-entry recognises the values based on the queries, it just not want to visually place the selection on the form in the combobox even though the value is available when a selection is made and a secondary run would produce records results and the values are still not visually there until I enter again.

All these SQL statements represent these four options.

1. Summary totals info for all Professional Titles for all Regions and Agency Types.

2. Summary totals from a specific Region for all Professional Titles.

3. Summary totals from a specific Agency Type for all Professional Titles.

4. Summary totals based on a specific Region and Agency Type.

Are any variables still somewhere in Access memory.

The variables x1,x2,strSQL are used only in that subroutine, not globally. Can that be a source of the problem???

Seeing the code for the form, given more explanation for purpose,any last suggestions before I just &quot;live&quot; with this visual nuisance.

Thanks again.

 

[Blanch]

I think at this point the only way that I might be able to lend any more help would be to actually see the form itself. I think it would be too much work to try and recreate the situation myself.

It sounds like it should work.

One last question? Are these comboboxes in the header of the form?

Jim

 

[AustinMan]

Yes, these comboboxes are located in the form header.

Is there a better place to place them?

 

[Blanch]

Nope.

The SQL in the comboxes isn't changing, just the recordset of the form - right?

As long as those combocoxes are not bound to a field on that form - and the rowsource of the combox doesn't change dramaticallly between refreshes - it should work.