Changing background color of items in combo list

[Repent]

I've looked at several other posts relating to this issue but all are different from my needs.

I have a form with a combo box on it. The combo box row source is a table. The combo box has times in it, i.e; 6:30am, 8:30am, 10:30am, 12:30pm, 2:30pm, end-days. Underneath that list, is a similiar list for nightshift. The worker selects the combo box and clicks on whatever time is needed for the record.

I'd like to make the listing for the 6:30am to end-days appear with a background color of yellow (daytime) and a font color of black and the 4:30pm to end-nights listing to appear with a black background with white font (or similar colors, would have to see how they look on the form/monitor)

I tried to do conditional formatting, using both the value of and expression of, using both a "" and [] around the times variables, but nothing changes.

Would this be handled by conditional formatting on the combo box, or VBA code in a form event?

thank you in advance for any help

chris

 

[John Big Booty]

What is the nature of the data in the bound column of your Combo Box? Is it a time value or another value?

 

[ChrisO]

Little demo attached that may do the job.

Chris.

 

[Repent]

Little demo attached that may do the job.

Chris.

looks good except that my hours table for both shifts is all on one table. Your solution uses two tables. I can see that it would be easy to do the color changes that way. I'm picking it apart to see how to plug it into my DB.

Seems like though if I change my table into two tables to match your solution I'll break quite a few queries, forms, reports, etc that rely on that table being in one part instead of two.


chris

 

[John Big Booty]

There should be no need to split your tables, all you need to do is change the SQL statements that ChrisO is using to populate the two text boxes.

I've given ChrisO's DB a little tweak to show what I mean.

 

[Repent]

There should be no need to split your tables, all you need to do is change the SQL statements that ChrisO is using to populate the two text boxes.

I've given ChrisO's DB a little tweak to show what I mean.

thanks for the info John. How do I get the controls/code from that form over to my form? This control needs to be in a stack of drop down lists.

I tried dragging and dropping the form onto my form, and that creates a subform. When I size it vertically and horizontally, and click on it, there is no drop down any more because it's size is so narrow. I understand this is because of the subform and it needing room to display it's results. How can I get this control to act as a drop down so I can place it where it goes with all the other drop downs?

chris

 

[John Big Booty]

What you will need to do is recreate ChrisO's control on your form, it's pretty straight forward it consists of three unbound text boxes and a button. My only change to ChrisO's sample was to combine the two tables he had in his sample and adjust the SQL statements in the Row Source of each of two text boxes that form the pseudo drop down. Put the form into design view and have a look at all the elements that make up the control. This will include all the events on the Form and the four elements on that form, along with the Row Source of the two lower boxes.

 

[Repent]

What you will need to do is recreate ChrisO's control on your form, it's pretty straight forward it consists of three unbound text boxes and a button. My only change to ChrisO's sample was to combine the two tables he had in his sample and adjust the SQL statements in the Row Source of each of two text boxes that form the pseudo drop down. Put the form into design view and have a look at all the elements that make up the control. This will include all the events on the Form and the four elements on that form, along with the Row Source of the two lower boxes.

OK so far but is there a better way to copy the code from your example over to my project?

chris

 

[John Big Booty]

Well you can certainly copy and post the code from the example to your DB. You will however need to either name all the objects in your DB to match the references in the code or alternately change the object references in the code to match the object names you've used in your DB.

 

[Repent]

Well you can certainly copy and post the code from the example to your DB. You will however need to either name all the objects in your DB to match the references in the code or alternately change the object references in the code to match the object names you've used in your DB.

Doing the renaming is fine. The reference names you used in your example make sense. I understand that I can also just change the references in the code to match what I've named stuff. I did that with the SQL statements.

the real problem I'm having is trying to understand where to plug in all the VB code that makes up the events. For example, I am getting an error relating to declarations. In looking at the code in the example, the code for the open event and the combo boxes is all on one "page". In my project, there are numerous controls, code, etc. It seems to me that the code in your example needs to be plugged in here and there in my VBA code window. I'm just having a difficult time knowing what part goes where. I do see where both combo boxes have their own events, and code relating to that event. That part seems to be straight forward, just recreate the events for the combo box and copy/paste the code fmor your example over to my project.

The problem is with the declarations and I also see there is code for the form's open event. I don't know where to plug all that in. Hopefully I'm making sense with my issue here and not rambling on......

Is there a button somewhere in the VBA window that basically does the "plug this event code in where it belongs so it will compile without error"?

chris

 

[John Big Booty]

Sorry, you're just going to have to work through the various controls and their events, and copy and paste the code, and adjust.

I'd start with the form event (On Open) and then work through the three objects on the form, that have events (On Click) and the the private module SetVisibility().

 

[Repent]

Sorry, you're just going to have to work through the various controls and their events, and copy and paste the code, and adjust.

I'd start with the form event (On Open) and then work through the three objects on the form, that have events (On Click) and the the private module SetVisibility().

Can you talk more about the Setvisibility private module. What do I do there?

after going step by step through the form event, and the other three events, and placing your code into my project, I can compile W/O error but when I change the form out of design mode, I get the error of "Runtime error 2950 Reserved error" and when I debug, the line that is highlighted in yellow is the line that refers to setfocus, below.
Private Sub SetVisibility(ByVal blnState As Boolean)

Me.cmdShowLists.SetFocus
Me.lstDayShift.Visible = blnState
Me.lstNightShift.Visible = blnState

End Sub


I know we're really close to the solution. I do appreciate all your help.

chris

 

[John Big Booty]

cmdShowLists Refers to the down arrow button, you will either need to change this to match the name of your button or change the name of the button to cmdShowLists.

 

[ChrisO]

Sorry John, I didn’t mean to leave you holding the fort.
At times things seem more difficult than they need be.

Chris, if you can upload a copy of your database John and I can talk you through it.

Chris.

 

[Repent]

Sorry John, I didn’t mean to leave you holding the fort.
At times things seem more difficult than they need be.

Chris, if you can upload a copy of your database John and I can talk you through it.

Chris.

Chris & John;

attached is my DB, cleaned up some to reduce the file size. I tried several more times to get this running to no avail. The time control you setup for me is installed on the form but still giving a debug error. the times that should show up in the drop down combo box are pulled from the tblProductionHours table. As the records step back and forth, i.e. the user is moving through records using the forms VCR controls, the time of the record should show up in the text box belonging to the control.

Thank you Chris and John for looking at this. Your help thus far has saved my sanity to say the least.

chris

 

[ChrisO]

I can’t open an A2007 file.
If you can convert it back to A2003 I will at least have a look at it.

Chris.

 

[Repent]

I can’t open an A2007 file.
If you can convert it back to A2003 I will at least have a look at it.

Chris.

Here it is Chris

chris

 

[ChrisO]

The Form frmProductionNumbers is corrupt.

I can make changes but can’t save them, Access crashes.
I can’t import it into another database and a decompile doesn’t fix it.

If you can get it to be reliable then I’ll have another look.

Chris.

 

[Repent]

The Form frmProductionNumbers is corrupt.

I can make changes but can’t save them, Access crashes.
I can’t import it into another database and a decompile doesn’t fix it.

If you can get it to be reliable then I’ll have another look.

Chris.

Chris;

can you just delete any part of the DB that is not directly related to your solution? Will that fix any error you're receiving? You might be getting the error due to my housecleaning the DB to reduce it's size. If not, I'll repost the DB but this time only remove raw data.

chris

 

[ChrisO]

That’s what I’m attempting to do at the moment.
I don’t know if I’ll be successful till it’s done

Chris.