Formatting text in combo box 1

[SColbert]

I have an unbound combo box on a form (Combo36) that contains all of the company names in the database and is used to select & quickly 'jump' to the form for the company chosen. The database contains both "Active" and "Inactive" companies that are differentiated using a checkbox control (checked for Active, unchecked for Inactive). Is there a way to format the text in the combo box to show Active & Inactive companies in different text colors? For example, when the drop down arrow is clicked to select a company, the resulting list of names would show Inactive companies in Red text.

Any assistance is greatly appreciated!

 

[scriverb]

I do not believe so but you can surely provide a very obvious and easy to use comobbox that your users can make informed choices. There are a couple of techniques.

1. You can provide for the word ACTIVE or INACTIVE along with the name of the company(i.e. ABC Company Name Inc. -(ACTIVE) or CBS Broadcasting Inc. - (INACTIVE) ). This is done in the SQL query that is indicated in the RowSource of the ComboBox.

2. The RowSource Query can be changed back and forth between ACTIVE and INACTIVE companyes with the click of a Command Button. The button would modify the SQL to select one or the other status values. The ComboBox Label and the button's caption would change to indicate what the Combo was displaying and what a click of the button would provide.

Let me know if either of these options sounds good to you and I can get you started on how to do either.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SColbert]

Thanks for the input - I believe Option #2 would be best suited to my user group. I anxiously await your response!

 

[scriverb]

Well this is a simple one.

1. Create a Command Button on your form near your comobox and title it cmdChngRowSource. Give it a .caption property of 'ACTIVE'. This command button's caption will indicate to your users what kind of records are being viewed in the combobox at the present time. It will change as the button is clicked.

2. Create two saved queries and name them qryActive and qryInActive.

qryActive

Select A.[CompanyName] & " -(Active)" FROM [i][red]yourtablename[/red][/i] as A WHERE A.[[i][red]statusfieldname[/red][/i]] = "Active" 
ORDER BY A.[CompanyName];

qryInActive

Select A.[CompanyName] & " -(Active)" FROM [i][red]yourtablename[/red][/i] as A WHERE A.[[i][red]statusfieldname[/red][/i]] = "InActive" 
ORDER BY A.[CompanyName];

3. Now in the OnClickevent procedure put the following code:

If Me.[[blue][i]cmdChngRowSource[/i][/blue]].caption = "Active" then
   Me.[[red][i]CompanyComboName[/i][/red]].RowSource = "qryInActive"
   Me.[[blue][i]cmdChngRowSource[/i][/blue]].caption = "InActive"
Else
   Me.[[red][i]CompanyComboName[/i][/red]].RowSource = "qryActive"
   Me.[[blue][i]cmdChngRowSource[/i][/blue]].caption = "Active"
End If
Me.[[red][i]CompanyComboName[/i][/red]].Requery
Me.[[red][i]CompanyComboName[/i][/red]].SetFocus
Me.[[red][i]CompanyComboName[/i][/red]].DropDown

This button now change the rowsource and requeries the combobox. The button caption changes to indicate what records are being displayed. Just update the blue and red code above and it should work as advertised.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SColbert]

Perfect - star to you!

One more question - how can I hide the combo box until the Active/Inactive button has been pressed? When the form opens initially, the button reads "Active", but the combo box is displaying all records - could be confusing.

I tried setting the visible property of the combo box to false at Form/Open, but that's not working.

Thanks

 

[scriverb]

If you only want the query to show Active or InActive then change the RowSource for the query to the qryActive and save the form in design view. Now it will initially only show the active records. If you want a third option for ALL then we can modify the code to provide three toggles to the button. This would require another query with no criteria selection and a modification to selectively put the Active or Inactive designation with the Company Name.

Just let me know if you wanted to go in that direction.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[TheAceMan1]

How are ay SColbert . . . .

Just backing up [blue]scriverb[/blue] a little. . . . . .

You could add a custom field to the query/SQL of the ListBox [blue]RowSource[/blue]. Using an [blue]IIF[/blue] statement you could see tagged Active/NonActive together in the list. You'd just have to switch display of the listbox to the custom field!

NameTag: IIf(CheckBoxName=True, "CompanyName" & " (Active)", "CompanyName" & " (Non Active)")

See Ya! . . . . . .

 

[SColbert]

scriverb - I think this last option would work best:
Have the form open up initially showing "Active" companies only; have the command button on the form programmed to let the user choose if they want to see "Active", "Inactive" or "All".

Your additional assistance is much appreciated!

 

[scriverb]

After your most recent update and reviewing the thread I am going to recommend an Option Group rather than a toggle button as I previously described. The reason for this is that you now have three possibile options to have the user select. When there were just two then toggling back and forth between them worked quite well. Now an option group with three toggle buttons within seems appropriate.

Create an option group on your form (see Option Group tool in toolbox). Now select the Toggle Button tool and create three button within the option group. You will need to reselect the Toggle button tool each time to make another button. Name the Option Group frame an appropriate name and also name the buttons Toggle1, Toggle2, and Toggle3. Their Default values should correspond to their named number. Toggle1, default: 1, Toggle2, default: 2, Toggle3, default: 3. Resize them so that the buttons are of equal size both H eight and width. Squeeze them together and align them top and bottom. Give each Toggle button a caption that is appropriate for the action to be taken: All, Active, Inactive

Now the code to make this work goes behind the Option Group frames AfterUpdate event procedure.

Select Case Me.[i][red]optiongroupname[/red][/i]
   Case 1
      Me.[[i][red]CompanyComboName[/red][/i]].RowSource = "qryAll"      
   Case 2
      Me.[[i][red]CompanyComboName[/red][/i]].RowSource = "qryActive"
   Case 3
      Me.[[i][red]CompanyComboName[/red][/i]].RowSource = "qryInActive"
End Select 
Me.[[i][red]CompanyComboName[/red][/i]].Requery
Me.[[i][red]CompanyComboName[/red][/i]].SetFocus
Me.[[i][red]CompanyComboName[/red][/i]].Dropdown

This requires you to create three queries one for each group that you want to display. qryAll, qryActive, qryInActive

Set the RowSource of the CompanyCombo to qryAll as the default query. Also, set the default value of the Option Group to 1 which will depress the All button.

This should get you started on this process. Let me know if you need more assistance.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SColbert]

Thanks again for your assistance. I came to the same conclusion on using an optiong group as well & have put it in place.