create public function - Access 2003, XP OS

[juengelj]

Ok, pros... I have like 80 rectangles on a form that i want to kinda act like check boxes. I have already figured out how to get them to appear/dissappear when the list box value that corresponds to the rectangle is populated or not. I have also figured out how to change the color of the box depending on if the "Actual" corresponding check box is set true/false accordingly...

Now the problem, I don't want to have to replicate the code for EVERY box. Is there a way to get this to run depending on which rectangle box I select? I am at a loss here and would appreciate any help... thanks in advance.

Here's what I have that works for setting the value to the listbox rowid and then changing the color of the rectangle to represent true or false...

 Private Sub BoxCheck()
'Sets focus to 1st Bill List Box and selects line 0.
Me.lst1st_Bills.SetFocus
Me.lst1st_Bills.ListIndex = 0
 
'If the 1st Bills "Paid" Box is Red, then will change to Green and set chkPaid value to "True"
'and refresh.
If Me.Ctl1stBoxPaid1.BackColor = 255 Then
Me.Ctl1stBoxPaid1.BackColor = 65280
Me.chkPaid.SetFocus
Me.chkPaid.Value = True
Me.Refresh
Else
 
'If the 1st Bills "Paid" Box is Green, then will change to Red and set chkPaid value to "False"
'and refresh.
Me.Ctl1stBoxPaid1.BackColor = 255
Me.chkPaid.SetFocus
Me.chkPaid.Value = False
Me.Refresh
End If
End Sub
 

Here is what the form looks like to give you an idea of the useage... hopefully you all can make it out ok. Thanks again.

[ADezii]

Ok, pros... I have like 80 rectangles on a form that i want to kinda act like check boxes. I have already figured out how to get them to appear/dissappear when the list box value that corresponds to the rectangle is populated or not. I have also figured out how to change the color of the box depending on if the "Actual" corresponding check box is set true/false accordingly...

Now the problem, I don't want to have to replicate the code for EVERY box. Is there a way to get this to run depending on which rectangle box I select? I am at a loss here and would appreciate any help... thanks in advance.

Here's what I have that works for setting the value to the listbox rowid and then changing the color of the rectangle to represent true or false...

 Private Sub BoxCheck()
'Sets focus to 1st Bill List Box and selects line 0.
Me.lst1st_Bills.SetFocus
Me.lst1st_Bills.ListIndex = 0
 
'If the 1st Bills "Paid" Box is Red, then will change to Green and set chkPaid value to "True"
'and refresh.
If Me.Ctl1stBoxPaid1.BackColor = 255 Then
Me.Ctl1stBoxPaid1.BackColor = 65280
Me.chkPaid.SetFocus
Me.chkPaid.Value = True
Me.Refresh
Else
 
'If the 1st Bills "Paid" Box is Green, then will change to Red and set chkPaid value to "False"
'and refresh.
Me.Ctl1stBoxPaid1.BackColor = 255
Me.chkPaid.SetFocus
Me.chkPaid.Value = False
Me.Refresh
End If
End Sub
 

Here is what the form looks like to give you an idea of the useage... hopefully you all can make it out ok. Thanks again.

I find your request very confusing, but I'll make an attempt to at least point you in the right direction. The following code will:

1. Populate a List Box named lstRectangles with the Names of all Rectangles contained on the Form
   

   Dim ctl As Control, lstBox As ListBox
    
   Set lstBox = Me![lstRectangles]
    
   lstBox.RowSourceType = "Value List"
    
   For Each ctl In Me.Controls
     If ctl.ControlType = acRectangle Then
       lstBox.AddItem ctl.Name
     End If
   Next
    

2. In The AfterUpdate() Event of the List Box, the code will change the BackColor to Red if a Rectangle has been selected, and Green if it has not.
   

   Private Sub lstRectangles_AfterUpdate()
   Dim ctl As Control
   Dim intCurrentRow As Integer
    
   Set ctl = Me![lstRectangles]
    
   For intCurrentRow = 0 To ctl.ListCount - 1
     If ctl.Selected(intCurrentRow) Then
       Me.Controls(ctl.ItemData(intCurrentRow)).BackColor = vbRed
     Else
       Me.Controls(ctl.ItemData(intCurrentRow)).BackColor = vbGreen
     End If
   Next intCurrentRow
   End Sub
    

3. It's the best I could come up with given the information provided - hope it is close to what you were expecting.

[juengelj]

I find your request very confusing, but I'll make an attempt to at least point you in the right direction.

Sorry, about the confusion... I am quite the newb, and am still trying to understand the language... if you know what I mean.

Basically, what this particular form of my DB is for is this:
I am able to add monthly expenses/bills as well as view/edit from this form via listbox selection... simple enough. There are a total of Four listboxes one for 1st Bills, 1st Expenses, 15th Bills and 15th Expenses resepectively. That part I got down and don't have an issue with data selection and population of the listboxes... all good there.

The second part, which is the area of confusion is this:

SCENARIO.. There is 1 table, and one of the fields are named "Paid" which is true/false. The form, has 4 listboxes. We will concentrate on Listbox1. For the sake of discussion, Lets says I only have 1 record.

Ok, now...

I created 56 rectangles, colored RED, and lined them up with the individual listbox rows. When "Rectangle1" which represents the "paid" column of the record assigned to listbox1.row0 is "clicked", it will change the background color of the "RECTANGLE" to GREEN which in turn updates the record to set "paid" value = true.

I have successfully pulled this off using the code in the original post. However, I don't want to have to repeat that code for EACH rectangle on the form. I am wondering if there is a way to create a global-type function that will resolve this function for me? I hope this cleared it up a little. Again, I thank you for your time and appologize for any confusion.

[ADezii]

Sorry, about the confusion... I am quite the newb, and am still trying to understand the language... if you know what I mean.

Basically, what this particular form of my DB is for is this:
I am able to add monthly expenses/bills as well as view/edit from this form via listbox selection... simple enough. There are a total of Four listboxes one for 1st Bills, 1st Expenses, 15th Bills and 15th Expenses resepectively. That part I got down and don't have an issue with data selection and population of the listboxes... all good there.

The second part, which is the area of confusion is this:

SCENARIO.. There is 1 table, and one of the fields are named "Paid" which is true/false. The form, has 4 listboxes. We will concentrate on Listbox1. For the sake of discussion, Lets says I only have 1 record.

Ok, now...

I created 56 rectangles, colored RED, and lined them up with the individual listbox rows. When "Rectangle1" which represents the "paid" column of the record assigned to listbox1.row0 is "clicked", it will change the background color of the "RECTANGLE" to GREEN which in turn updates the record to set "paid" value = true.

I have successfully pulled this off using the code in the original post. However, I don't want to have to repeat that code for EACH rectangle on the form. I am wondering if there is a way to create a global-type function that will resolve this function for me? I hope this cleared it up a little. Again, I thank you for your time and appologize for any confusion.

Thanks for the explanation, some of the fog has actually lifted. There should be a fairly and more efficient way to accomplish this. I'll see what I can come up with later on.

[MMcCarthy]

To set up a public (global) type function you create it in a module as opposed to the form.

Function doSomething(variable As Datatype) As Datatype
 
    ' your code here
 
    doSomething=Your return value here
 
End Function
 

This allows you to pass one or more values to the function and return a value from the function.

You then call this function as required in your code

Me!mycontrol = doSomething(SomeValue)

Try writing the fuction yourself in a module and we will try to help if you run into problems.

[ADezii]

Sorry, about the confusion... I am quite the newb, and am still trying to understand the language... if you know what I mean.

Basically, what this particular form of my DB is for is this:
I am able to add monthly expenses/bills as well as view/edit from this form via listbox selection... simple enough. There are a total of Four listboxes one for 1st Bills, 1st Expenses, 15th Bills and 15th Expenses resepectively. That part I got down and don't have an issue with data selection and population of the listboxes... all good there.

The second part, which is the area of confusion is this:

SCENARIO.. There is 1 table, and one of the fields are named "Paid" which is true/false. The form, has 4 listboxes. We will concentrate on Listbox1. For the sake of discussion, Lets says I only have 1 record.

Ok, now...

I created 56 rectangles, colored RED, and lined them up with the individual listbox rows. When "Rectangle1" which represents the "paid" column of the record assigned to listbox1.row0 is "clicked", it will change the background color of the "RECTANGLE" to GREEN which in turn updates the record to set "paid" value = true.

I have successfully pulled this off using the code in the original post. However, I don't want to have to repeat that code for EACH rectangle on the form. I am wondering if there is a way to create a global-type function that will resolve this function for me? I hope this cleared it up a little. Again, I thank you for your time and apologize for any confusion.

I've developed a system for you which will significantly reduce the volume of code, and at the same time reduce redundancy, and increase the code efficiency. It involves the creation of a Private Sub-Procedure in your Form's Code Module. This Procedure will be called from the Click() Event of each Rectangle (1 line of code), and the Procedure itself will accept 2 Arguments: 1 to specifically identify the Rectangle whose Click() Event was triggered, and the other a specific Reference to a List Box Control. All logic is contained within the Procedure which need not return any value. Follow the below listed steps carefully and you should be OK:

1. Copy and Paste the following code segment to the General Declarations Section of the Form's Code Module. It will now show up as a Private Sub-Procedure for the Form.
   

   Private Sub FormatRectangles(rec As Rectangle, lst As ListBox)
   Const conBackStyleNormal As Integer = 1
    
   'Allows for color to show through
   rec.BackStyle = 1
    
   'Sets focus to 1st Bill List Box and selects line 0.
   lst.SetFocus
   lst.ListIndex = 0
    
   'If the 1st Bills "Paid" Box is Red, then will change to Green
   'and set chkPaid value to "True" and refresh.
   If rec.BackColor = vbRed Then
     rec.BackColor = vbGreen
       Me![chkPaid].SetFocus
       Me![chkPaid].Value = True
         Me.Refresh
   Else
    'If the 1st Bills "Paid" Box is Green, then will change to Red
    'and set chkPaid value to "False" and refresh.
     rec.BackColor = vbRed
       Me![chkPaid].SetFocus
       Me![chkPaid].Value = False
         Me.Refresh
   End If
   End Sub
    

2. For each Rectangle, make the call to FormatRectangles() in its Click() Event by passing a Reference to the Rectangle and the required List Box in that order. I'll use the 1st Rectangle and 1st List Box as defined by you as an example.
   

   Private Sub Ctl1stBoxPaid1_Click()
     Call FormatRectangles(Me![Ctl1stBoxPaid1], Me![lst1stBills])
   End Sub

3. If you have any problems related to what I have shown you, please feel free to ask.

[ADezii]

To set up a public (global) type function you create it in a module as opposed to the form.

Function doSomething(variable As Data type) As Datatype
 
    ' your code here
 
    doSomething=Your return value here
 
End Function
 

This allows you to pass one or more values to the function and return a value from the function.

You then call this function as required in your code

Me!my control = doSomething(Some Value)

Try writing the function yourself in a module and we will try to help if you run into problems.

Sorry Mary, I don't want you to think that I was contradicting you, and your solution to this problem (ref Post #6). I feel as though the code should be contained within a Private Sub-Procedure on the Form for the following reasons:

1. The context of the Code as far as scope, is strictly limited to the confines of the Form, that is why I placed it there Privately as opposed to Publicly in a Standard Code Module. The Overhead is also reduced.
2. The Procedure need not return any value, hence the use of a Sub-Procedure as opposed to a Function.
3. A Public Function will certainly work, but would require fully qualified References to the Controls as opposed to using the Me Keyword. If another Form was using this logic, however, a Public Routine would be needed.
4. If my Account is deleted the next time I log on, I'll understand why. (LOL).

[MMcCarthy]

Sorry Mary, I don't want you to think that I was contradicting you, and your solution to this problem (ref Post #6). I feel as though the code should be contained within a Private Sub-Procedure on the Form for the following reasons:

1. The context of the Code as far as scope, is strictly limited to the confines of the Form, that is why I placed it there Privately as opposed to Publicly in a Standard Code Module. The Overhead is also reduced.
2. The Procedure need not return any value, hence the use of a Sub-Procedure as opposed to a Function.
3. A Public Function will certainly work, but would require fully qualified References to the Controls as opposed to using the Me Keyword. If another Form was using this logic, however, a Public Routine would be needed.
4. If my Account is deleted the next time I log on, I'll understand why. (LOL).

I'll let you away with it this time :) Only because you provided such a neat solution though (LOL)

No harm for the OP to know how to create a public function anyway.

[juengelj]

[*] Copy and Paste the following code segment to the General Declarations Section of the Form's Code Module. It will now show up as a Private Sub-Procedure for the Form.

Private Sub FormatRectangles(rec As Rectangle, lst As ListBox)
Const conBackStyleNormal As Integer = 1
 
'Allows for color to show through
rec.BackStyle = 1
 
'Sets focus to 1st Bill List Box and selects line 0.
lst.SetFocus
lst.ListIndex = 0
'If the 1st Bills "Paid" Box is Red, then will change to Green
'and set chkPaid value to "True" and refresh.
If rec.BackColor = vbRed Then
  rec.BackColor = vbGreen
    Me![chkPaid].SetFocus
    Me![chkPaid].Value = True
      Me.Refresh
Else
 'If the 1st Bills "Paid" Box is Green, then will change to Red
 'and set chkPaid value to "False" and refresh.
  rec.BackColor = vbRed
    Me![chkPaid].SetFocus
    Me![chkPaid].Value = False
      Me.Refresh
End If
End Sub
 

[*] For each Rectangle, make the call to FormatRectangles() in its Click() Event by passing a Reference to the Rectangle and the required List Box in that order. I'll use the 1st Rectangle and 1st List Box as defined by you as an example.

Private Sub Ctl1stBoxPaid1_Click()
  Call FormatRectangles(Me![Ctl1stBoxPaid1], Me![lst1stBills])
End Sub

[*] If you have any problems related to what I have shown you, please feel free to ask.[/list]

Wow, you are a mad scientist! Great idea... :) That idea actually works pretty good. The only thing is... well, check this out...

'Sets focus to 1st Bill List Box.
lst.SetFocus
lst.ListIndex = 0

...in the code you set the declaration for the list.index in the code to "0", which makes this NOT possible to use on all the boxes? Now I have tried adding a field to your Sub() like this...

Private Sub FormatRectangles(rec As Rectangle, lst As ListBox, ind as index)

...But that doesn't work either. As far as I can tell, if the code does NOT select the listbox and row before adding the check, it will not update the appropriate "paid" column... which obviously is a problem. Do you have any suggestions for getting around this? In the mean time... trust me I am working very hard to resolve this problem on my own as well... It just takes me LOADS of time being that I am a newb! :)

Thanks again, your suggestion to get around my last problem proved to be quite useful in other areas as well... I really appreciate you ALL who spend time here helping others, it speaks volumes to your characters! God Bless.

[ADezii]

Wow, you are a mad scientist! Great idea... :) That idea actually works pretty good. The only thing is... well, check this out...



...in the code you set the declaration for the list.index in the code to "0", which makes this NOT possible to use on all the boxes? Now I have tried adding a field to your Sub() like this...

Private Sub FormatRectangles(rec As Rectangle, lst As ListBox, ind as index)

...But that doesn't work either. As far as I can tell, if the code does NOT select the listbox and row before adding the check, it will not update the appropriate "paid" column... which obviously is a problem. Do you have any suggestions for getting around this? In the mean time... trust me I am working very hard to resolve this problem on my own as well... It just takes me LOADS of time being that I am a newb! :)

Thanks again, your suggestion to get around my last problem proved to be quite useful in other areas as well... I really appreciate you ALL who spend time here helping others, it speaks volumes to your characters! God Bless.

If I interpret you correctly, the Numeric Value at the end of the Rectangle Field Name should coincide with the Numbered Row in the associated List Box. If you are passing Rectangle2 and lst1stBills to the FormatRectangles() Sub Procedure, then the 2nd item in lst1stBills should be selected:

Call FormatRectangles(Me![Rectangle2], Me![lst1stBills])

If this is your case, then here is your solution:

1. Decorate a Private Variable named intRectangleID in your Form's Code Module.
   

   Private intRectangleID As Integer

2. For each Rectangle, 'PRIOR' to making the call to FormatRectangles(), assign the proper value to intRectangleID. I'll stay with Rectangle2 as my example.
   

   Private Sub Rectangle2_DblClick(Cancel As Integer)
     intRectangleID = 2       'Insert Unique Value for each Rectangle
     Call FormatRectangles(Me![Rectangle2], Me![lst1stBills])
   End Sub
    

3. I showed the Routine in its entirety, but change only Line #9 to reflect the new Value for ListIndex in FormatRectangles().
   

   Private Sub FormatRectangles(rec As Rectangle, lst As ListBox)
   Const conBackStyleNormal As Integer = 1
    
   'Allows for color to show through
   rec.BackStyle = 1
    
   'Sets focus to 1st Bill List Box and selects line 0.
   lst.SetFocus
   lst.ListIndex = intRectangleID - 1        'ListIndex will now coincide with Rectangle#
    
   'If the 1st Bills "Paid" Box is Red, then will change to Green
   'and set chkPaid value to "True" and refresh.
   If rec.BackColor = vbRed Then
     rec.BackColor = vbGreen
       Me![chkPaid].SetFocus
       Me![chkPaid].Value = True
         Me.Refresh
   Else
    'If the 1st Bills "Paid" Box is Green, then will change to Red
    'and set chkPaid value to "False" and refresh.
     rec.BackColor = vbRed
       Me![chkPaid].SetFocus
       Me![chkPaid].Value = False
         Me.Refresh
   End If
   End Sub

4. As always, any problems please feel free to ask.

[ADezii]

Wow, you are a mad scientist! Great idea... :) That idea actually works pretty good. The only thing is... well, check this out...



...in the code you set the declaration for the list.index in the code to "0", which makes this NOT possible to use on all the boxes? Now I have tried adding a field to your Sub() like this...

Private Sub FormatRectangles(rec As Rectangle, lst As ListBox, ind as index)

...But that doesn't work either. As far as I can tell, if the code does NOT select the listbox and row before adding the check, it will not update the appropriate "paid" column... which obviously is a problem. Do you have any suggestions for getting around this? In the mean time... trust me I am working very hard to resolve this problem on my own as well... It just takes me LOADS of time being that I am a newb! :)

Thanks again, your suggestion to get around my last problem proved to be quite useful in other areas as well... I really appreciate you ALL who spend time here helping others, it speaks volumes to your characters! God Bless.

If I interpret you correctly, the Numeric Value at the end of the Rectangle Field Name should coincide with the Numbered Row in the associated List Box. If you are passing Rectangle2 and lst1stBills to the FormatRectangles() Sub Procedure, then the 2nd item in lst1stBills should be selected:

Call FormatRectangles(Me![Rectangle2], Me![lst1stBills])

If this is your case, then this should point you in the right direction:

1. Decorate a Private Variable named intRectangleID in your Form's Code Module.
   

   Private intRectangleID As Integer

2. For each Rectangle, 'PRIOR' to making the call to FormatRectangles(), assign the proper value to intRectangleID. I'll stay with Rectangle2 as my example.
   

   Private Sub Rectangle2_DblClick(Cancel As Integer)
     intRectangleID = 2       'Insert Unique Value for each Rectangle
     Call FormatRectangles(Me![Rectangle2], Me![lst1stBills])
   End Sub
    

3. I showed the Routine in its entirety, but change only Line #9 to reflect the new Value for ListIndex in FormatRectangles().
   

   Private Sub FormatRectangles(rec As Rectangle, lst As ListBox)
   Const conBackStyleNormal As Integer = 1
    
   'Allows for color to show through
   rec.BackStyle = 1
    
   'Sets focus to 1st Bill List Box and selects line 0.
   lst.SetFocus
   lst.ListIndex = intRectangleID - 1        'ListIndex will now coincide with Rectangle#
    
   'If the 1st Bills "Paid" Box is Red, then will change to Green
   'and set chkPaid value to "True" and refresh.
   If rec.BackColor = vbRed Then
     rec.BackColor = vbGreen
       Me![chkPaid].SetFocus
       Me![chkPaid].Value = True
         Me.Refresh
   Else
    'If the 1st Bills "Paid" Box is Green, then will change to Red
    'and set chkPaid value to "False" and refresh.
     rec.BackColor = vbRed
       Me![chkPaid].SetFocus
       Me![chkPaid].Value = False
         Me.Refresh
   End If
   End Sub

4. As always, any problems please feel free to ask.

[juengelj]

[*] As always, any problems please feel free to ask.

Thank you very kindly ADezii... I will play around with this and let you know. :)

[ADezii]

Thank you very kindly ADezii... I will play around with this and let you know. :)

You are quite welcome.