Textbox and Subform

[mattloflin]

Hey guys Probably an easy question but I can't get it to click:

I have a form (frmSearch) that has a subform (frmDatabase) the frmSearch has textboxes that limit the frmDatabase off of that material.

Question: once I get this searched criteria down to 1 entry I want to set the value of the column to a textbox.

EX. (variable) = ColumnA

So if it is only 1 item then the (variable) should equal the text in ColumnA.

Also I have a If statement running to make sure it equals 1

If txtcount.value = 1 then
Global Variable = ColumnA
end if

how can i do that?

[PianoMan64]

I guess I need to understand the reason why you would want to do that, are you trying to pull a peace of data from a particular column in the table that you displaying in frmDatabase?

If so, I have a few suggestions for you:

1. If you change the frmDatabase to simply be a ListControl in your frmSearch.
2. You then take the input from each of the frields and build your Criteria statement that you're going to pass to the ListControl to display the record(s) on the screen. You can keep displaying it as many times as you want until you get down to a manageable level of data.
3. You then would select from the values that you have displayed in the ListControl to select the particular data item from doubleclicking on the selected item that you want to get the value from.

The following example is taken from an accual project that I worked on before.
Please take note of the variable names and the control names that are used.

the following are Control Name for the List Control
ListInvoices is the name of the List Control
TheAmount is a TextBox Control on the form
TheCompany is the TextBox Control on the form
and so on....

By simply building the string, you can then pass that string to the Listcontrol and requery the listcontrol and it will display that you passed the listcontrol rowsource value.
See code below.

Option Compare Database
 
Private Sub ClearSearch_Click()
    Me.ListInvoices.RowSource = ""
    Me.TheAmount = Null
    Me.TheCompany = Null
    Me.TheIDNo = Null
    Me.TheInvoiceNo = Null
    Me.TheVendorNo = Null
    Me.TheBatchNumber = Null
    DoCmd.GoToControl "TheIDNo"
 
End Sub
 
Private Sub SearchBtn_Click()
 
    Dim SQL1 As String
    Dim Criteria As String
 
    Me.StatusMsg = "Searching..."
    Me.Repaint
    SQL1 = "SELECT [CFS Invoice View].IDNO as [ID No], [CFS Invoice View].Vendor, " & _
           "[CFS Invoice View].COMPANY, [CFS Invoice View].INVOICENO, " & _
           "[CFS Invoice View].AMOUNT, [CFS Invoice View].RECEIVED, " & _
           "[CFS Invoice View].BatchNo,  [CFS Invoice View].UserName, " & _
           "[CFS Invoice View].Status " & _
           "FROM [CFS Invoice View] " & _
           "WHERE ("
    If IsNull(Me.TheIDNo) And IsNull(Me.TheVendorNo) And IsNull(Me.TheCompany) And IsNull(Me.TheInvoiceNo) _
       And IsNull(Me.TheAmount) And IsNull(Me.TheBatchNumber) Then
       SQL1 = Left(SQL1, Len(SQL1) - 7)
       SQL1 = SQL1 & "ORDER BY [CFS Invoice View].Status DESC;"
    Else
    If Not IsNull(Me.TheIDNo) Then
        If Len(Criteria) > 0 Then
            Criteria = Criteria & " AND (([CFS Invoice View].IDNo)=" & Me.TheIDNo & ")"
        Else
            Criteria = Criteria & "(([CFS Invoice View].IDNo)=" & Me.TheIDNo & ") "
        End If
    End If
    If Not IsNull(Me.TheVendorNo) Then
        If Len(Criteria) > 0 Then
            Criteria = Criteria & " AND (([CFS Invoice View].Vendor)=" & Me.TheVendorNo & ")"
        Else
            Criteria = Criteria & "(([CFS Invoice View].Vendor)=" & Me.TheVendorNo & ") "
        End If
    End If
    If Not IsNull(Me.TheCompany) Then
        If Len(Criteria) > 0 Then
            Criteria = Criteria & " AND (([CFS Invoice View].Company)=" & Me.TheCompany & ")"
        Else
            Criteria = Criteria & "(([CFS Invoice View].Company)=" & Me.TheCompany & ") "
        End If
    End If
    If Not IsNull(Me.TheInvoiceNo) Then
        If Len(Criteria) > 0 Then
            Criteria = Criteria & " AND (([CFS Invoice View].INVOICENO) = '" & Me.TheInvoiceNo & "')"
        Else
            Criteria = Criteria & "(([CFS Invoice View].INVOICENO) = '" & Me.TheInvoiceNo & "') "
        End If
    End If
    If Not IsNull(Me.TheAmount) Then
        If Len(Criteria) > 0 Then
            Criteria = Criteria & " AND (([CFS Invoice View].AMOUNT)=" & Me.TheAmount & ")"
        Else
            Criteria = Criteria & "(([CFS Invoice View].AMOUNT)=" & Me.TheAmount & ") "
        End If
    End If
    If Not IsNull(Me.TheBatchNumber) Then
        If Len(Criteria) > 0 Then
            Criteria = Criteria & " AND (([CFS Invoice View].BatchNo)=" & Me.TheBatchNumber & ")"
        Else
            Criteria = Criteria & "(([CFS Invoice View].BatchNo)=" & Me.TheBatchNumber & ") "
        End If
    End If
 
 
    SQL1 = SQL1 & Criteria & ") ORDER BY [CFS Invoice View].Status DESC;"
 
    'MsgBox SQL1, vbOKOnly
    End If
    Debug.Print SQL1
 
    Me.ListInvoices.RowSource = SQL1
    Me.ListInvoices.Requery
    Me.StatusMsg = ""
    Me.Repaint
 
 
End Sub
 

In order to pull a particular value from the list of items that you have displayed in the ListControl you would have code something like this:

Sub ListInvoices_DblClick()
         Dim varItem as Variant
         Dim theValueYouWant as Variant
 
         For Each varItem in ListInvoice.ItemsSelected
                  theValueYouWant = me.ListInvoice.Column(1,varitem)
        Next
End Sub
 

If you want to pass that value to a control on the main form that you're working with, then you can, if you want to be able to select that record and open it up in another form for editing, then you can do that also.

If you need more code examples of how to do that, let me know,

Hope that helps,

Joe P.