Managing strings via VBA

[Lestatos]

Good evening dear programmers .
Well my current question is basicly theoretical .
I think it would help also other beginners in VBA programming in Access .
Here is the explanation of my question :
- We have a few tables with different information
- We have a public function called CreateDynamicReport
- CreateDynamicReport accepts as arguments :
-rpt_Title as String (used in the Footer as Title )
-rpt_Data as String (used as identifier for the selected fields in a table )
We have a form , that is calling the CreateDynamicReport function via a control button with On_Click event
On the form we have 5 checkboxes with labels .
3 of the checkboxes refer to records in Table1
2 of the checkboxes refer to records in Table2
Checkboxes are unbound.
If we assume that we want to set the rpt_Data argument according to the checkboxes value (True or False ) - then how can we have SELECT expression (as String ) stored in one variable in the calling form(or Control) , refering to two different tables(and selected fields ... ) ?
Best Regards.

 

[MarkK]

Theoretically if you need an object to expose a string you write a Property Get procedure. You can write a Function that returns a string, but a function is more commonly used to implement a behaviour of an object rather than simply exposing a string based on the current settings of that instance.

So if you needed, for example, to expose a field list for an SQL statement as a function of what checkboxes are selected on a form you might do something like . . .

private m_names
private m_boxes

Private Property Get FieldNames
[COLOR="Green"]'  Exposes an array of the field names that may need to appear in the SQL[/COLOR]
   If IsEmpty(m_name) then m_names = Array("Field1", "SecondField", "3Field3", "LastField")
   FieldNames = m_names
End Property

Private Property Get CheckBoxes
[COLOR="Green"]'  Exposes an array of the check boxes that, when checked, cause fields to appear in the SQL[/COLOR]
   If IsEmpty(m_boxes) then m_boxes = Array(me.chk1, me.chkSecond, me.chk3field, me.chkLast)
   ChekcBoxes = m_boxes
End Property

Property Get SQL_FieldList() As String
[COLOR="Green"]'  Polls the check boxes, and writes the corresponding
'  field to the list if the check box is checked
[/COLOR]   Dim i as integer
   Dim flds as string

   For i = 0 to 3
      if CheckBoxes(i) then flds = flds & ", " & FieldNames(i)
   Next
   If flds <> "" then SQL_FieldList = Mid(flds, 3)  [COLOR="Green"]'trim the leading ", "[/COLOR]
End Property

. . . so in this case we predefine a list of the field names you'll need, and an array of the check boxes that will trigger the appearance of said field names, and those will remain static for the lifetime of the form. Then the user can interact with the checkboxes, setting some and clearing others, but every time you read the SQL_Fieldlist property, it will return a field list defined by the checked check boxes.

And you can construct the whole SQL statement this way, based on settings on the form, for the fields that will appear, and for the constraints in the WHERE clause.

Hope that helps,