Setting the recordsource of subform based on user's criteria in Pa

[Guest]

Please Help…. I am at a loss… I have tried this a couple of different ways...

I have an unbound form where a user can input certain criteria,
click on a “Search†button (that fires the macro, that runs the code below)
which would then populate the subform with the recordset (results) based on
the criteria entered.

I am getting a “Compile Error: Invalid use of property†on
allRecords = CurrentDb.OpenRecordset(“qry_All_Documentsâ€, dbOpenDynaset)

What am I doing wrong?



Public Function Search() As String

‘Error Handling
On Error GoTo Err_Search_Click

'Establish connection to ActiveX Data Objects
Dim trs As ADODB.Connection
Set trs = CurrentProject.Connection

'Declare Recordset
Dim allRecords As New ADODB.Recordset
allRecords.ActiveConnection = trs

‘Determine which query we want to run based on User’s Criteria
If (Forms!Search_frm!Criteria_A is True AND
Forms!Search_frm!Criteria_B is True) Then

allRecords = CurrentDb.OpenRecordset(“qry_All_Documentsâ€, dbOpenDynaset)

End If

If (Forms!Search_frm!Criteria_A is False AND
Forms!Search_frm!Criteria_B is True) Then

allRecords = CurrentDb.OpenRecordset(“qry_Btype_Documentsâ€, dbOpenDynaset)

End If


‘Set Search_subform equal to allRecords
Set Forms!Search_frm!Search_subform.Form.RecordSource = allRecords

‘Shut down connection and recordset
allRecords.Close
Set allRecords = Nothing
Set trs = Nothing

Exit_Search_Click:
Exit Function

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Function

 

[Guest]

Disregard... solution found... posted here for other users...

Public Function Search() As String

'Error Handling
On Error GoTo Err_Search_Click

'Declare Variables & assign user input to variables
Dim MySQL As String

Dim Doc_Type As Integer
Doc_Type = Forms!Search_frm!Doc_Type

Dim Buyer As String
If IsNull(Forms!Search_frm!Buyer.Value) Or (Forms!Search_frm!Buyer = 0) Then
Buyer = " Like '%'"
Else
Buyer = " = " & Forms!Search_frm!Buyer & " "
End If

Dim Doc_No As String
If IsNull(Forms!Search_frm!Document_Number.Value) Then
Doc_No = " Like '%'"

Else
Doc_No = " Like '" & "%" & Forms!Search_frm!Document_Number.Value & "%"
& "'"
End If


'Establish connection to ActiveX Data Objects
Dim trs As ADODB.Connection
Set trs = CurrentProject.Connection

'Declare Recordset
Dim allRecords As New ADODB.Recordset
allRecords.ActiveConnection = trs

MySQL = " 'Award' AS Doc_Type, Award.Award_ID AS System_ID, "
MySQL = MySQL & "Award.Award_No AS Document_No, "
MySQL = MySQL & "Award.Buyer AS Buyer "
MySQL = MySQL & "Award.Total_Award_Value AS Dollar_Value "
MySQL = MySQL & "FROM Award "
MySQL = MySQL & "WHERE Award.Award_No" & Doc_No & " And Award.Buyer" & Buyer

With allRecords
'Specify a cursortype and lock type that will not allow updates
..CursorType = adOpenStatic
..CursorLocation = adUseClient
..LockType = adLockOptimistic
'open the recordset based on the query using the existing connection
..Open MySQL, trs
End With

'if the recordset is empty
If allRecords.BOF And allRecords.EOF Then
MsgBox "There are no records in the database."
Exit Function
End If


Set Forms!Search_frm!Search_Results_subform.Form.Recordset = allRecords

'Loop through recordset and display info for each record
Do Until allRecords.EOF

'bind the controls of the form to the proper field in the recordset (which has
'the same field names as the Query from which it is generated)

Forms!Search_frm!Search_Results_subform.Form!Doc_Type.ControlSource =
"Doc_Type"

Forms!Search_frm!Search_Results_subform.Form!System_ID.ControlSource =
"System_ID"

Forms!Search_frm!Search_Results_subform.Form!Document_No.ControlSource =
"Document_No"

Forms!Search_frm!Search_Results_subform.Form!Dollar_Value.ControlSource =
"Dollar_Value"

allRecords.MoveNext
Loop

Exit_Search_Click:
Exit Function

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Function