Combobox display initial value issue ...

[svpreston]

I have 2 Comboboxes, one for Region and one for Country, and am attempting to initalize/cascade them.
Here is the basic info for the the underlying tables:

tblRegion
========
regionAbbreviation (Text, PK)
regionName (Text)
regionOverseer (Text, Foreign Key...essentially a parameter)

tblCountry
========
countryCode (Text, PK)
countryName (Text)
regionAbbreviation (Text, Foreign Key)

Some relational constraints of which to be aware:

1. Every Region has at least one Country
2. Every Region has one and only one Overseer
3. An Overseer may be responsible for more than one Region

Characteristics of the Comboboxes:

1. Both use 2 columns with the Name field (first column) being displayed and the Abbreviation or Code field (second column) being the bound column.
2. The nominal case is that both comboboxes will have multiple items to select and in that case the comboboxes are initialed with a query containing a UNION that inserts the string "<ALL>" (same string in both columns).
3. When the case arises that an Overseer manages a single Region or a Region contains a single Country, the Union query is bypassed such that the "<ALL>" string is excluded.

The comboboxes are used to create a filter condition to drill down into a potentially very large recordset.

Now to the issue at hand ...

The Region combobox is initialized first. The initialization is accomplished by setting the RowSource property to an appropriate query. The first entry in the item list is then loaded to the combobox Value property by the following line of code:

Me.cbxRegion.Value = Me.cbxRegion.Column(0,0)

This always works whether there are multiple items or just one. If there is just one I set the Enabled property to FALSE; no point in reselecting the only item avaliable.

The Country combobox is then initialized in a similar manner using appropriate code that is virtually identical to that used for the Region combobox.

The problem I am having is that if there is only a single Country, the Name is not displayed in the combobox but is contained in the selection list. Note that i do not have a problem with the Region combobox when it is initialized with a single Region.

I have searched this forum and others and found similar inquiries and a couple of possible solutions; some clearly don't work while others appear to but result in run-time errors later in the execution of the code (run-time error '2115').

I do not have a simply test case I can upload at this time; also new to the forum and appear to be restricted as to my ability to perform uploads.

Any assistance/guidance that can be offered will be greatly appreciated.

 

[DavidAtWork]

Am I right in assuming the region cbx rowsource is dependent on or set by the overseer, so if the overseer selects a region from the list, is the country cbx supposed to display all countries containing the selected region, if so can't you reset the country cbx rowsource based on the selected region. This approach should see only countries in the country cbx related to previously selected region, whether it be one or many.
This rowsource reset can be done using the afterUpdate event of region cbx using:

Me.cbxCountry.Rowsource = "SELECT tblCountry.countryName, tblCountry.countryCode  FROM tblCountry WHERE  tblCountry.regionAbbreviation = '" & Me.cbxRegion & "'"
Me.cbxCountry.Requery

David

 

[svpreston]

David,

That is indeed what I am doing; your assumption is correct. The problem I am having is getting the Country to display in the combobox after RowSource is set to the appropriate query. If there are multiple rows returned everything works fine. If only a single row is returned then nothing is displayed in the combobox.

I have ascertained that the query is indeed returning the desired records and that the combobox has those very records as its item list.

As I mentioned in my initial post, essentially the same code is being used to initialize both the Region and the Country comboboxes. The Region combobox always works as desired, i.e., if a single region is returned for an Overseer it is displayed in the combobox. The Country combobox only seems to display an entry if there are 2 or more Countries returned. By the way, there is actually a third combobox in the filter mechanism which again uses essentially identical code only on a different table and it also always works as expected; it also always contains multiple items from which to make a selection.

 

[DavidAtWork]

I have a similar situation in one of my databases and there's no problem in the display of the 2nd cbx even if only one record in the second.
Have you checked any criteria used is working as you expect, also may need to check the properties of the cbx

David

 

[svpreston]

David,

Thanks for the prompt response ... I appreciate your efforts on my behalf.

The comboboxes are identical except for names, etc. In fact, the Country combobox was created by doing a copy-paste of the Region combobox.

All criteria are working as I expect. I have verified all generated SQL using Debug.Print; nothing appears to be incorrect.

I did find one post when initially researching the issue that suggested the Primary Keys on the tables needed to be numeric. I can not believe that that could possible have any bearing on the issue. Both the Region and Country tables have alpha primary keys.

To reiterate the issue I am experiencing, the following lines of code work for the Region combobox:

Me.cbxRegion.RowSource = GenerateRegionQuery()
Me.cbxRegion.Requery
Me.cbxRegion.Value = Me.cbxRegion.Column(0,0)

The following lines of code only work if the Country query returns more than one Country:

Me.cbxCountry.RowSource = GenerateCountryQuery()
Me.cbxCountry.Requery
Me.cbxCountry.Value = Me.cbxCountry.Column(0,0)

GenerateRegionQuery() and GenerateCountryQuery() are functions used to build the appropriate queries and I have verified that they are returning the desired SQL string.

I have tried both "Me.cbxCountry.Value" and "Me.cbxCountry" and the end result is unchanged ... a single Country is not displayed whereas the first item in the Country selection list is displayed if there is more than one item in the list.

 

[DavidAtWork]

could you post the code you use in your GenerateCountryQuery() function
The only thing I can think of at this stage is that this function is returning, for whatever reason, some rows that are blank in the CountryName field and they are top of the list.
When you expect only one country to be in the list, is there nothing in the cbx list?

David

 

[svpreston]

David,

Will try to get the code posted soon ... have some obligations this morning. I am not able to detect any blank rows in the records returned.

When I expect one Country, the list does indeed contain that country and apparently nothing else. I can select the country and it then displays in the cbx. I just can't seem to get an initial display presented like I can on all other cbx's.

Note that the Region cbx can end up with only a single region and it works as I expected, i.e., the Region is displayed.

Again, thanks for you time and efforts.

 

[svpreston]

David,

The VBA code for creating both the Region and the Country queries follows. I trust the comments will help you decipher the business rules applicable.

I spent a couple more hours this afternoon attempting to debug the problem. The only thing that is not working is the physical display of value in the Country cbx when there is a single country in the list. I created a test db to see if I could duplicate the issue ... alas, it seems to work fine. I have compared the properties of the two comboboxes and can find no differences other than the names. :banghead:

Here's the code .......................


Public Function createRegionCboxQuery(paramIncludeALL As Boolean) As String
' This function returns an appropriate query to populate the
' Region Combo Box based on Region Overseerer.

Dim strBaseQuery As String
Dim strALLQuery As String
Dim strQuery As String

strBaseQuery = "SELECT regionName, regionAbbreviation FROM vueRegion"
strALLQuery = "SELECT Col1, Col2 FROM tblALL"

' If current user role is "RAMgmt" then we will create a
' query that lists all regions, otherwise we need to tailor
' the query to an individual RA Specialist.

strQuery = strBaseQuery

If (gblCurrentUserRole <> "RAMgmt") Then

strQuery = strQuery & " WHERE regionOverseer = " & gblQuote & gblCurrentUserID & gblQuote

End If

If (paramIncludeALL) Then

strQuery = "(" & strQuery & ") UNION (" & strALLQuery & ")"

End If

createRegionCboxQuery = strQuery

End Function

Public Function createCountryCboxQuery(ByRef regionCbox As ComboBox, paramIncludeALL As Boolean) As String
' This function returns an appropriate query to populate the
' Country Combo Box based on the selection in the Region Combo
' Box passed in as a parameter.

Dim strBaseQuery As String
Dim strALLQuery As String
Dim strQuery As String
Dim regionValue As String

strBaseQuery = "SELECT countryName, countryCode FROM vueCountry"
strALLQuery = "SELECT Col1, Col2 FROM tblALL"

regionValue = Nz(regionCbox.Value, "")
If (regionValue = "") Then
Call writeLogFile("Internal error -- createCountryCboxQuery failed, region null")
Application.Quit
End If

strQuery = strBaseQuery

If (regionValue = "<ALL>") Then

' If the current user role is "RegSpecialist" then we will
' create a query that selects only the current user's (Overseer's)
' countires; otherwise the current user role is assumed to
' be "RAMgmt" and we then want to select everything.

If (gblCurrentUserRole = "RegSpecialist") Then
strQuery = strQuery & " WHERE regionAbbreviation IN " & _
"(SELECT regionAbbreviation FROM vueRegion WHERE regionOverseer = " & _
gblQuote & gblCurrentUserID & gblQuote & ")"
End If
Else
strQuery = strQuery & " WHERE regionAbbreviation = " & _
gblQuote & regionValue & gblQuote
End If

If (paramIncludeALL) Then

strQuery = "(" & strQuery & ") UNION (" & strALLQuery & ")"

End If

createCountryCboxQuery = strQuery

End Function