Textbox on Subform as Query Criteria

matt164

Registered User.
Local time
Today, 06:12
Joined
Apr 4, 2013
Messages
12
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:
Code:
DoCmd.OpenForm "LeadDetails", acNormal, Point_To", , , acDialog, """"
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:
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
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:
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
 
Thanks, that's how I refer to it in my code but the Access prompts in query builder didn't want me to do it that way..but I manually typed it in ignoring the suggestions and it worked! But.. I still get "Error 2105 You can't go to the specified record" .. however it opens the BrokerDetails form to the record I selected so I'm not sure why I'm getting error?
 
How do you refer to it now, because this is wrong if it's a subform:

Criteria: [Forms]![BrokerSearch]![Text20]
 
I'm now referring to it as:

Criteria: [Forms]![BrokerMgmt]![BrokerSearch].[Form]![Text20]

It works and gets me to the right record but still gives an error message
 
Not why on that. I use the wherecondition argument of OpenForm; I've never used the filter argument. Sorry.
 
If the Where condition can suit my needs that will suffice just fine. No need to use the filter query. I entered this as my Where argument:

Code:
"[BrokerID]=" & [Forms]![BrokerMgmt]![BrokerSearch].[Form]![Text20]

And once again I got the 2105 error yet my BrokerDetails form still opened to the specified record.
 
Can you post the db here, or a representative sample?
 
I cannot post the db here, are there any forms/tables in particular you would like a sample of? It's a split db, front end on local machines back end on a shared server. I can post all the code you need as well as form design and table design just not actual data.
 
Last edited:
I don't care about the actual data, just looking at something that exhibited the error so I could try to see why it happens.
 
Okay I removed some of the unnecessary code such as bob larson's FE_Update utility and I added back in the relevant linked tables. Problem arises when clicking "Brokers" button on GUI (Which normally opens on startup but I don't think it will in this version) and then searching for a broker (type "j" in last name box) upon clicking the button next to the record in the subform you receive the error. The database runs painfully slow in design mode, it takes forever just to save something or drag a text box across a form. My code is very rudimentary so if you happen to see anything that might be causing this please do let me know. Thank you!
 
Last edited:
The error isn't in this code, it occurs if you open the BrokerDetails form. This line in the open event causes it:

Me.BrkLeads.Form.cursorlock.SetFocus

Not sure what you're trying to accomplish, as the next line sets focus elsewhere.
 

Users who are viewing this thread

Back
Top Bottom