How to assign a record set to a sub form and display the results ?? 1

[egstatus]

Hi everyone,

I am having a problem setting a DAO record set to a subform and displaying the results

I have both the main form and the subform unbound.
I populate 3 combo boxes, then I use what ever the user has selected from those combo boxes to set up a search.

Here is my code:

    Dim ITPDB As DAO.Database
    Dim ITPRS As DAO.Recordset
    Dim strQuery As String
           
    On Error GoTo ErrorHandler
    
    Set ITPDB = currentdb()
    
    Screen.MousePointer = 11 'vbhourglass
    
    strQuery = "SELECT LotNumber, FormName, SubFormName, SubField, ImagePath, ImageName FROM ITP " & _
               "WHERE LotNumber = '" & Trim(cmbLotNumber.Value) & "'" & _
               " AND FormName = '" & Trim(cmbFormName.Value) & "'" & _
               " AND SubFormName = '" & Trim(cmbSubform.Value) & "' ORDER BY FormName;"

    Set ITPRS = ITPDB.OpenRecordset(strQuery)

    If ITPRS.RecordCount <= 0 And ITPRS.EOF Then
        GoTo ExitHere
    End If

    Set Me.ITPSearchResultsSubForm.Form.Recordset = ITPRS
    ITPSearchResultsSubForm.Visible = True
ExitHere:
    Screen.MousePointer = 0 'default

    If Not ITPRS Is Nothing Then
        ITPRS.Close
        Set ITPRS = Nothing
    End If
    If Not ITPDB Is Nothing Then
        ITPDB.Close
        Set ITPDB = Nothing
    End If

    'MsgBox "Number Of Dups Found = " & lNofDups
    Exit Sub
    
ErrorHandler:
    If Err.Number = 3704 Then
        Resume Next
    Else
        Call HandleUnexpectedError(Err.Number, Err.Description)
    End If
    'Unload Me
    'Exit Sub
    GoTo ExitHere
    Resume Next

I checked the record set and it returns records. however I can't get to display the resuts in the sub form.

The fields in the sub form show up as #name?

What Am I doing wrong??

I have attached an image.

Thanks in advance.

EG

 

[PHV]

Why is the subform unbound ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[egstatus]

The sub form will get populated based on the results that they choose from the 3 combo boxes.

Once they have made the selections from the combo boxes, I build a query and try to assign the result to the subform to display the results fromt the query.

I don't want to show 20000 records on the subform when the form gets loaded.

Thanks

 

[PHV]

Bound the form to a query with the following SQL:
SELECT LotNumber, FormName, SubFormName, SubField, ImagePath, ImageName
FROM ITP
WHERE 1=2

And then in your VBA replace this:
Set Me.ITPSearchResultsSubForm.Form.Recordset = ITPRS

with this:
Me!ITPSearchResultsSubForm.Form.RecordSource = strQuery




Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[egstatus]

Thanks,
That did it.

I apreciate.

EG.