Populate a text box on an access for via a function in the form's event procedure

Question

I have an access database which has a search form with some text boxes and some check boxes. The user chooses which values to gather on the search form by inputing text or checking one of the boxes. When the search button is clicked, the appropriate results form is opened.

The results form's data source is a query which gives the list of records in the table corresponding with the requested search. In the query, I have 2 IIf fields, 1 combines 2 of the check boxes into a text sentence representing either or both of those 2 boxes which are checked in the record, the other combines 3 of the checkboxes. There are 2 text fields on the results forms whose data sources are the 2 IIf query fields.

There is a third (unbound) text field on the results forms, I have 9 remaining check boxes which i need to combine into a sentence for that field. I've tried this function(Oth) in the on open event of the results forms. The function seems to work, in that it gives me the correct sentence in the MsgBox(fOther) for each record, but I cant get the fOther to populate the unbound text field on the results forms. Ive tried it several different ways, recordset of the form, of the query, of the table. I've tried putting a (similar) funtion in a module and using that function in an update query for a field in the table. I'm not able to populate that (Other) field. I've taken to an attempt at building an IIf field in the query for that (Other) text field, it is very daunting with 9 objects, and all the possibilitie combination, Im not even sure yet if it is possible. Are there any tips one could offer?

Private Sub Oth()
  Dim strOthr(9) As String
  Dim Tt As Integer
  Dim fOther As String

  Me.Recordset.MoveFirst

  Do While Not Recordset.EOF
    If Me.[o-180] Then
      strOthr(0) = "180"
    End If

    If Me.[o-e] Then
      strOthr(1) = "Education"
    End If

    If Me.[o-rcb-f] Then
      strOthr(2) = "Flag"
    End If

    If Me.[o-rcb-m] Then
      strOthr(3) = "Medal"
    End If

    If Me.[o-rcb-b] Then
      strOthr(4) = "Burial"
    End If

    If Me.[o-ra-hb] Then
      strOthr(5) = "Health Benefits"
    End If

    If Me.[o-ra-t] Then
      strOthr(6) = "Transportation"
    End If

    If Me.[o-ra-hl] Then
      strOthr(7) = "Home Loans"
    End If

    If Me.[o-ra-il] Then
      strOthr(8) = "Income Letter"
    End If

    For Tt = 0 To 8
      If Len(strOthr(Tt)) > 0 Then
        If Len(fOther) > 0 Then
          fOther = fOther & ", " & strOthr(Tt)
        Else
          fOther = fOther & strOthr(Tt)
        End If
      End If
    Next Tt

    Me.Other = fOther

    MsgBox fOther

    For Tt = 0 To 8
      strOthr(Tt) = ""
    Next Tt
    fOther = ""

    Me.Recordset.MoveNext
  Loop
End Sub

Why don't you make your results form a subform of your search form and make other an unbound control on your search form? — John W Fowler

John W Fowler John W Fowler · Accepted Answer · 2016-08-03T23:32:27

I had to re read that. So if Other is a field on your results form, then it shouldn't be

Me.other = fother

But

Forms!Resultform.form!other = fother

And then for your search criteria, you might want to just build a WHERE clause you either add to an SQL string to set as your result form recordsource or to set as the filter property using the whole table/query as the results form record source

dim strfilter as string    
If me.[a-b-c] then 
    strfilter = strfilter & "[fld] = " & Chr(34) & "txtcriteria" & Chr(34) & " AND "
End If
If me.[b-x-y] then
    'repeat 
End if
'repeat if for each expression
strfilter = left(strfilter, len(strfilter) - 5)
With forms!frmResuls.form
    .filter = strfilter
    .filteron = true 
End With 
'or
forms!frmResults.form.recordsource = "SELECT * from tbl WHERE " & strfilter & ";"

Populate a text box on an access for via a function in the form's event procedure

1 Answers