Change text box properties based on listbox values? 2

[Scootman]

I have a problem that's been really bugging me the last few days. I tried looking through search, but I think my problem is a bit more unique than I first thought...

I have a form that contains a list box and 37 text boxes. The text boxes have the names of d0 through d36.

When the form is ran, if d0 is a value in the list box, I want to change the backcolor for the text box on the form that is named d0.

There will be many times where d0 through d36 will be on the list box (the rowsource for the listbox is an SQL query). If that happens, then I want to change the backcolor for all text boxes that share the same name as the values in the list box.

I could provide backstory to this problem if someone wanted it. In short - I have a monthly calendar on an unbound form where I'm wanting to highlight the dates on the calendar if they exist in the table that the list box is reading from. The dates on the calendar are the d0 through d36 text boxes. I click on the text box and then click on a command button next to the calendar to go to that particular day's record (through a different form).

Does anyone have any solutions? I really appreciate any help and will offer any more information I can if needed.

Thanks!

 

[Remou]

Here is an idea. It assumes that the listbox contains dates and that the 'D' boxes contain days.

    For Each itm In Me.lstList.ItemsSelected
        strDays = strDays & "," & Day(Me.lstList.Column(0, itm))
    Next
    
    strDays = Mid(strDays, 2)
    For i = 0 To 36
        If InStr(strDays, Me("D" & i)) > 0 Then
            Me("D" & i).BackColor = vbYellow
        Else
            Me("D" & i).BackColor = vbWhite
        End If
    Next

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Scootman]

PHV,

I tried taking the list box's values (selected or not) and returning them through code. I also tried using strings to temporarily store the SQL query results, but that didn't work either. I'm self-taught Access knowledge and I've never done anything like this before, so I don't know where to go really.

Where I'm stuck is taking the results that are returned from the SQL query and then telling the form that I want the text boxes on the form with the same names to have their back color changed. And these SQL results always range from d0 to d36 because those are the values that are in my 'textbox' field in the table.

I know a list box is wrong, but its the only way I can actually see the returned results (what I'm trying to do in the previous paragraph is a part of Access I haven't learned anything about yet).

Remou:

I haven't had time to try your code out yet (I have to be at work in a few minutes), but it looks like its looking for selected items in the listbox.

I only used the listbox for storing, I don't actually want to click in it to select items (I want ALL the values from the listbox in other words).

I'll be home later tonight. In the meantime, I'll try to think of another way of phrasing my question.

Thanks

 

[PHV]

A starting point:
For i = 0 To 36
Me.Controls("d" & i).BackColor = vbWhite
Next
With Me!myListBox
For i = 0 To .ListCount - 1
Me.Controls(.Column(0, i))).BackColor = vbRed
Next
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Scootman]

PHV, your code works perfectly, but I also figured it out at work this morning when I was looking at Remou's code (I don't have internet access at work, so I wasn't able to let you guys know earlier).

Here's the code I use:

Dim i As Integer
Dim strdays As String
For i = 0 To List214.ListCount - 1
strdays = strdays & List214.ItemData(i)
strdays = List214.ItemData(i)
Me(strdays).BackColor = vbYellow
Next i


Remou's code was only part of the answer though since I still needed to figure out how to get the list box values out (without having to select anything). I found that answer out by remembering a bit of code from HarleyQuinn, found in this post:

http://www.tek-tips.com/viewthread.cfm?qid=1140267

I had looked it up last year and copied the code for future use at work (but forgot about it). Harleyquinn's code still wouldn't have helped me much though if I didn't have Remou's code from earlier too.

So thanks Remou for leading me to my answer. And thanks PHV for your code too (had I been able to see it when I was at work, it would've saved me some thinking time).

Its probably a good thing I don't have internet access because I would spend ALL my working time on the Tek-tips MS Access forums (and they don't appreciate the value of Microsoft Access where I work).

 

[Scootman]

I meant to also say that I did give HarleyQuinn a much deserved star for his code too.