How do I streamline this code

General
1

Okay I have been trying to find a cleaner way to do this but no luck yet. The code works but it’s ugly and cludgy.

Background: I have a table that stores information and based on a users selection a query is run to populate the RadioButtons. Because there is not an equal number of responses I have created 20 instances for the RadioButton to account for growth and disparity in data.

The user then selects the appropriate RadioButton and the program moves on.

Currently I have two Event Handlers in the RadioButton
Name = radNotes
radNotes.Opening

' ----> Variables <----
// Have these declared and ready for use...
Var rsData As RowSet = dbFinances.SelectSQL( "SELECT * FROM tblCannedNotes WHERE noteSource=?", frmMaster.cboDaily.Text )
Var rBtn01 As New DesktopRadioButton
Var rBtn02 As New DesktopRadioButton
Var rBtn03 As New DesktopRadioButton
Var rBtn04 As New DesktopRadioButton
Var rBtn05 As New DesktopRadioButton
Var rBtn06 As New DesktopRadioButton
Var rBtn07 As New DesktopRadioButton
Var rBtn08 As New DesktopRadioButton
Var rBtn09 As New DesktopRadioButton
Var rBtn10 As New DesktopRadioButton
Var rBtn11 As New DesktopRadioButton
Var rBtn12 As New DesktopRadioButton
Var rBtn13 As New DesktopRadioButton
Var rBtn14 As New DesktopRadioButton
Var rBtn15 As New DesktopRadioButton
Var rBtn16 As New DesktopRadioButton
Var rBtn17 As New DesktopRadioButton
Var rBtn18 As New DesktopRadioButton
Var rBtn19 As New DesktopRadioButton
Var rBtn20 As New DesktopRadioButton
Var i As Integer = rsData.RowCount

' ----> Preliminary Code <----
Me.RemoveAll

' ----> Main Code <----
Try
  If( rsData <> Nil ) Then // So long as there is a record return it then close the rowset...
    rBtn01.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 0, rBtn01 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn02.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 1, rBtn02 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn03.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 2, rBtn03 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn04.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 3, rBtn04 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn05.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 4, rBtn05 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn06.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 5, rBtn06 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn07.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 6, rBtn07 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn08.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 7, rBtn08 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn09.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 8, rBtn09 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn10.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 9, rBtn10 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn11.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 10, rBtn11 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn12.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 11, rBtn12 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn13.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 12, rBtn13 )
    rsData.MoveToNextRow
    If ( rsData.Column( "noteValue" ).StringValue = "" )Then Exit
    rBtn14.Caption = rsData.Column( "noteValue" ).StringValue
    Me.AddAt( 13, rBtn14 )
    
  End If
  rsData.Close
Catch error As DatabaseException
  MessageBox( "Error: " + error.Message )
End Try

Me.SelectedIndex = -1

radNotes.SelectionChanged

' ----> Variables <----
Var rsData As RowSet = dbFinances.SelectSQL( "SELECT * FROM tblCannedNotes WHERE noteSource=?", frmMaster.cboDaily.Text )
Var iCnt As Integer = radNotes.SelectedIndex - 1

' ----> Preliminary Code <----

' ----> Main Code <----
For iLoop As Integer = 0 To iCnt
  rsData.MoveToNextRow
Next
frmMaster.txtNotes.Text = rsData.Column( "noteValue" ).StringValue

I know there is a way to do this dynamically and really streamline the code, I just haven’t figured it out.

2

Why try and guess how many rows you are going to need to present. Just generate a new one when you need to. I’m not sure RadioButtons are the right choice for this? A popup menu could be better?

Sub radNotes.Opening
   Var rsData As RowSet = dbFinances.SelectSQL( "SELECT * FROM tblCannedNotes WHERE noteSource=?", frmMaster.cboDaily.Text )
   Me.RemoveAll
   while rsData <> nil
      Var rBtn As New DesktopRadioButton
      rBtn.Caption = rsData.Column( "noteValue" ).StringValue
      // Store some appropriate value in the tag to prevent having to look items back up in the database.
      // Alternatively store a key to the item to make the search return a single row.
      rBtn.Tag = rsData.Column( "noteValue" ).StringValue
      Me.AddAt( 0, rBtn )
      rsData.MoveToNextRow
   wend
   rsData.Close
   Me.SelectedIndex = -1
End Sub

Sub radNotes.SelectionChanged
   if radNotes.SelectedIndex = -1 then
      // if SelectedIndex = -1 then they selected nothing.
      frmMaster.txtNotes.Text = ""
   else
      frmMaster.txtNotes.Text = radNotes.ItemAt( radNotes.SelectedIndex ).Tag
   end if
End Sub
3

Thank you! This is exactly what I was looking to do!

4

Without reading all, this part is incorrect

5

Very true. Converting someone else’s code, I missed fixing that.

Sub radNotes.Opening
   Var rsData As RowSet = dbFinances.SelectSQL( "SELECT * FROM tblCannedNotes WHERE noteSource=?", frmMaster.cboDaily.Text )
   Me.RemoveAll
   if rsData = Nill then exit sub
   While Not rs.AfterLastRow
      Var rBtn As New DesktopRadioButton
      rBtn.Caption = rsData.Column( "noteValue" ).StringValue
      // Store some appropriate value in the tag to prevent having to look items back up in the database.
      // Alternatively store a key to the item to make the search return a single row.
      rBtn.Tag = rsData.Column( "noteValue" ).StringValue
      Me.AddAt( 0, rBtn )
      rsData.MoveToNextRow
   wend
   rsData.Close
   Me.SelectedIndex = -1
End Sub

Sub radNotes.SelectionChanged
   if radNotes.SelectedIndex = -1 then
      // if SelectedIndex = -1 then they selected nothing.
      frmMaster.txtNotes.Text = ""
   else
      frmMaster.txtNotes.Text = radNotes.ItemAt( radNotes.SelectedIndex ).Tag
   end if
End Sub
6

Still need a review

7

Yeah, I ran the first iteration and it essentially was an endless loop. I fixed it and all works now! I appreciate the education on this!

I do have another question, when creating say dynamic DesktopTextFields, does the .Tag attribute work the same?

I used this to get around the endless loop:

'While rsData <> nil
For Each row As DatabaseRow In rsData
  'While Not rsData.AfterLastRow
  Var rBtn As New DesktopRadioButton
  rBtn.Caption = rsData.Column( "noteValue" ).StringValue
  // Store some appropriate value in the tag to prevent having to look items back up in the database.
  // Alternatively store a key to the item to make the search return a single row.
  rBtn.Tag = rsData.Column( "noteValue" ).StringValue
  Me.AddAt( 0, rBtn )
  'rsData.MoveToNextRow
Next
'Wend
8

Tag exists on pretty much any control. ListBoxes have even more support. Each row has a RowTag, each cell on the row has a CellTag. They are all Variants which allows you to store a value of pretty much any type, including an instance of a class.

9

Ah! Nice to have someone explain it… now I can experiment and see new ways to streamline my apps.

10

Have you considered a listbox with checkboxes?
No need to consider the number of rows in advance, and having dozens of rows will still fit on a dialog.

1 Like