title: Access VBA Public Function - Variable for Control and Field?
body: <p>I want to set up a public function like the following:</p>
  
  <p>(A)</p>
  
  <pre><code>Public Function CheckYNField(chkControl As Control, chkField As String)
  If chkField = "Y" Then
      chkControl = -1
  ElseIf chkField = "N" Then
      chkControl = 0
  Else: chkControl = 0
  End If
  End Function
  </code></pre>
  
  <p>(B)</p>
  
  <pre><code>Public Function CheckYNFlipper(chkControl As Control, chkField As String)
  If chkField = "Y" Then
      chkField = "N"
      chkControl = 0
  ElseIf chkField = "N" Then
      chkField = "Y"
      chkControl = -1
  Else: chkField = "Y"
      chkControl = -1
  End If
  End Function
  </code></pre>
  
  <p>The reason for this is, that I have a form, which has an underlying SQL table.  I have no control over the data, but I must represent it, for the ability to maintain it.  I have 10 fields, in the underlying table, which have a Y or N as their values.  The data types are actually nvarchar(10).  At the same time, I have to show these fields as checkbox controls, on the form, for ease of use.  </p>
  
  <p>The above code, is an attempt I am making to A - set the checkbox control to align with the current value in the table--> -1 = Y and 0 = N, and to B - update the table value, and switch the check box to checked or unchecked, from what it was, to the opposite, based on the onclick event of that control.</p>
  
  <p>I want to make chkField and chkControl variables to the public function, that would be the table-field, and the form-control.  I can't seem to get the right syntax, and was hoping someone might have clarification on how to do this.</p>
  
  <p>for the form load and current, I tried this:</p>
  
  <pre><code>CheckYNField Forms("frmFormNameA").Controls(chkCheckNameA), tblTableName.FieldA
  </code></pre>
  
  <p>for the on click, I tried this:</p>
  
  <pre><code>CheckYNFlipper Forms("frmFormNameA").Controls(chkCheckNameA), tblTableName.FieldA
  </code></pre>
  
  <p>I've tried some other methods, but doesn't seem to be working.  I'm doing something wrong, but I can't tell what.  Appreciate any tips!</p>
  
  <hr>
  
  <p><strong>Edit/Update:</strong>
  I tried Kostas K.'s solution, abandoning the idea of making a public functions with parameters for the fields and controls.  I put the following, on load and on current:</p>
  
  <pre><code>With Me
      If .txtfieldboundtablefieldA.Value = "Y" Then
          .unboundchkA.Value = True
      ElseIf .txtfieldboundtablefieldA.Value = "N" Then
          .unboundchkA.Value = False
      Else: .unboundchkA.Value = False
      End If
  End With
  </code></pre>
  
  <p>This is on a continuous form, so that it can show like a giant grid.  There are the identifying bound field controls, and then a series of these checkboxes, to display the Y/N true/false status of each of these particular fields.  I can't bound the checkboxes to the fields, because it changes the field value in the table to -1 or 0, and we need it to stay Y or N.  I added a bound text field, to hold the table/field value for each row (hence the call to a text box control in the above revised code).  The checkbox is unbound, and is there to display the field value, and allow the user to check and uncheck, so I can use on-click code to change the table field value between Y and N.</p>
  
  <p>The above code is not seeming to show the correct checkbox value for each bound text field, based on each row.  It shows based on the row that currently has focus.  If I click on a row, where the table field is Y, all rows checkboxes on the form show true.  If I move to a row, where the table field is N, all checkboxes for all rows change to false.  I am struggling to just initially get 1 checkbox to show accurately, on every row of the continuous form, based on every record in the table.  This is a small table, like 30 records.  I really didn't think it would be so difficult to present this the way we need to. </p>
  
  <p>Any ideas, how I could better do this?</p>