Dealing with Run-time error 2427 with a search form and subform

[apk19]

Hi all,



I am having difficulties in dealing with a run-time error on a search form that opens a selected record. Records in the form update as you type into the search box. The opening of the record when there is one is not an issue, but when there are no records I get run-time error 2427.

Code is:

Private Sub Command69_Click()
    If Me.qrySearchApp_subform.Form.ClientID = "" Then
    MsgBox "No record selected.", vbOKOnly
    Else
    DoCmd.OpenForm "Applicants", , , "ClientID = """ & Me.qrySearchApp_subform.Form.ClientID & """"
    End If

End Sub

Trying to find a quick fix for the error that will bounce the user back into the form if there are no records. Not sure if the "If" statement should be IsNull or if there should be a Boolean statement instead.

Any help would be greatly appreciated.

[ranman256]

are you sure: Me.qrySearchApp_subform.Form.ClientID is the correct path?
did you use BUILDER to get this path? (it never gets it wrong)

also less confusing, you can use single quotes:
DoCmd.OpenForm "Applicants", , , "[ClientID] = "'" & Me.qrySearchApp_subform.Form.ClientID & "'"


and it could be null and error , unlike: If isNull(Me.qrySearchApp_subform.Form.ClientID)
so the 1st statement should be: on error resume next

[isladogs]

Error 2427 = You entered an expression that has no value. The expression may refer to an object that has no value, such as a form, a report, or a label control. (followed by a name here)
As Ranman suggested you have used an incorrect name

[Gicu]

In case the name is correct please try:

Private Sub Command69_Click()
    If isNull(Me.qrySearchApp_subform.Form.ClientID) Then
          MsgBox "No record selected.", vbOKOnly
    Else
          DoCmd.OpenForm "Applicants", , , "ClientID = """ & Me.qrySearchApp_subform.Form.ClientID & """"    'this assumes ClientID is a string
    End If


End Sub

Cheers,

[apk19]

Tried your suggestion Gicu, but I get the same 2427 error.

Ranman, didn't use the builder but I assumed this is correct as when there are one or more records showing the openform command works.

"ClientID" is an indexed field for a selected record. There are a few others. Screenshot attached shows when there is no record in the search box.

I built a query: qrySearchApp, which was then dropped into the form as a subform. As the screenshot should show, the subform is a continuous form.

[Gicu]

Is the ClientID numeric or text?

[apk19]

Is the ClientID numeric or text?

It's a string: 4 letters and 4 numbers.

[Gicu]

I think I (we) misunderstood your question. The code you have there makes no sense if looking to open another form ("Applicants") that match the ID in a subform "filtered" by your search box in your main form.

If Me.qrySearchApp_subform.Form.ClientID = "" Then
    MsgBox "No record selected.", vbOKOnly
Else
    DoCmd.OpenForm "Applicants", , , "ClientID = """ & Me.qrySearchApp_subform.Form.ClientID & """
End If

You could use a dCount on the subform's record source (dCount("*","tblTableName","[ClientID] Like '" & Forms!frmYourForm!txtSearchBox & "'")=0) to see if there are no records returned or use the subform recordset.recordcount property:

If Me.qrySearchApp_subform.Form.Recordset.RecordCount =0 Then
    MsgBox "No record selected.", vbOKOnly
Else
    DoCmd.OpenForm "Applicants", , , "ClientID = """ & Me.qrySearchApp_subform.Form.ClientID & """
End If

Cheers,
Vlad

[apk19]

I think I (we) misunderstood your question. The code you have there makes no sense if looking to open another form ("Applicants") that match the ID in a subform "filtered" by your search box in your main form.

If Me.qrySearchApp_subform.Form.ClientID = "" Then
    MsgBox "No record selected.", vbOKOnly
Else
    DoCmd.OpenForm "Applicants", , , "ClientID = """ & Me.qrySearchApp_subform.Form.ClientID & """
End If

You could use a dCount on the subform's record source (dCount("*","tblTableName","[ClientID] Like '" & Forms!frmYourForm!txtSearchBox & "'")=0) to see if there are no records returned or use the subform recordset.recordcount property:

If Me.qrySearchApp_subform.Form.Recordset.RecordCount =0 Then
    MsgBox "No record selected.", vbOKOnly
Else
    DoCmd.OpenForm "Applicants", , , "ClientID = """ & Me.qrySearchApp_subform.Form.ClientID & """
End If

Cheers,
Vlad

My apologies, Vlad. My coding abilities are basic at best.

Your code was perfect. Many thanks!

[Gicu]

Hey, glad to help!
Stay safe!