Hi,
I'm trying to build a query to use as a filter in the DoCmd.OpenForm function.
I have done this several times before, referencing values from forms e.g.
Point To
Field: LeadID
Table: Lead
Criteria: [Forms]![GridDisplay1]![LeadID]
This filter is used in the procedure:
and it works great, I click a button next to the record I want to select (records displayed on continuous form) and it opens the Lead Details form on that particular record.
However now I am trying to accomplish the same thing, except instead of a continuous form I need to draw from a continuous sub-form. The form "BrokerMgmt" contains a sub-form named "BrokerSearch." The RecordSource for the sub-form is set once the user enters data into a few textboxes on the main form and clicks the search button:
The sub-form then displays basic info such as Name, Company, State, and the "BrokerID" which is the primary key of the "Broker" table. This "BrokerID" is displayed in "Textbox 20" so I set a button next to each result to on_click perform the procedure:
"Point_To_Broker2" is set up in the same style as "Point_To":
Field: BrokerID
Table: Broker
Criteria: [Forms]![BrokerSearch]![Text20]
However instead of the BrokerDetails form opening to the appropriate record I get a msg box asking for a parameter. Why is this? Even if I enter the correct BrokerID as the parameter it still gives me an error saying you can't go to the specified record. Thanks for any help you may provide
I'm trying to build a query to use as a filter in the DoCmd.OpenForm function.
I have done this several times before, referencing values from forms e.g.
Point To
Field: LeadID
Table: Lead
Criteria: [Forms]![GridDisplay1]![LeadID]
This filter is used in the procedure:
Code:
DoCmd.OpenForm "LeadDetails", acNormal, Point_To", , , acDialog, """"
However now I am trying to accomplish the same thing, except instead of a continuous form I need to draw from a continuous sub-form. The form "BrokerMgmt" contains a sub-form named "BrokerSearch." The RecordSource for the sub-form is set once the user enters data into a few textboxes on the main form and clicks the search button:
Code:
Private Sub brkSearch_Click()
Dim argCount As Integer
On Error GoTo Err_Handler
If IsNull(brkFirstName.Value) And IsNull(brkLastName.Value) And IsNull(brkCompany.Value) Then
MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"
Exit Sub
End If
brkQuery = "SELECT * From Broker Where "
If Not IsNull(brkFirstName.Value) Then
brkQuery = brkQuery & "BrokerFirstName Like '" & "*" & brkFirstName.Value & "*" & "'"
argCount = argCount + 1
End If
If Not IsNull(brkLastName.Value) Then
If argCount > 0 Then brkQuery = brkQuery & " AND "
brkQuery = brkQuery & "BrokerLastName Like '" & "*" & brkLastName.Value & "*" & "'"
argCount = argCount + 1
End If
If Not IsNull(brkCompany.Value) Then
If argCount > 0 Then brkQuery = brkQuery & " AND "
brkQuery = brkQuery & "BrokerCompany Like '" & "*" & brkCompany.Value & "*" & "'"
argCount = argCount + 1
End If
Me.BrokerSearch.Form.RecordSource = Forms!BrokerMgmt.brkQuery
Me.BrokerSearch.Form.Requery
Me.BrokerSearch.Form.Command19.Visible = True
Me.BrokerSearch.Form.Text11.Visible = True
Me.BrokerSearch.Form.Text13.Visible = True
Me.BrokerSearch.Form.Text15.Visible = True
Me.BrokerSearch.Form.Text17.Visible = True
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " " & Err.Description
Resume Exit_Handler
End Sub
Code:
DoCmd.OpenForm "BrokerDetails", acNormal, "Point_To_Broker2", , , acDialog, """"
"Point_To_Broker2" is set up in the same style as "Point_To":
Field: BrokerID
Table: Broker
Criteria: [Forms]![BrokerSearch]![Text20]
However instead of the BrokerDetails form opening to the appropriate record I get a msg box asking for a parameter. Why is this? Even if I enter the correct BrokerID as the parameter it still gives me an error saying you can't go to the specified record. Thanks for any help you may provide