Solved Search Form - If result <1 then go to result (1 Viewer)

evictme

Registered User.
Local time
Today, 11:52
Joined
May 18, 2011
Messages
168
Hi everyone, hope everyone is well and staying safe!

Back for a little Search function syntax assistance.

I have a search form that opens another form with results in a subform that use the original search form's input. Most of the time, it will display all possible results (it includes name, dob, phones, addresses, etc.). Sometimes, however, it will only produce a single result. In this case it is because only one record matches the criteria.

I would like to have it so the record will automatically be shown in the correct form.

The structure is:

[Worker Form]

[Main Search Form] (for user input)
opens
[Search Form Results]
[sub-form with actual results]

When I double-click the Name field of the Search Form results, it will load that record in the [Worker Form]

How would i get the [Search Form Results] to check onload event if the total results are = 1 to then open that result in the [Worker Form] without the need for the user to double-click the field.

The code below is the on double-click event for the Search-Form with the Subform with Results:
Subform:
Code:
DoCmd.Close acForm, "Search-Workers", acSaveNo
DoCmd.Close acForm, "SearchForm-W", acSaveNo
If CurrentUser() = "Blah blah" Or CurrentUser() = "blah" Then
    DoCmd.OpenForm "NewEmployeeForm", , , , , , Me.EmployeeID
    If CurrentProject.AllForms("NewEmployeeForm").IsLoaded Then
        Forms!NewEmployeeForm.Recordset.FindFirst "EmployeeID=" & Me.EmployeeID
    End If
Else
    DoCmd.OpenForm "Employees", , , , , , Me.EmployeeID
    If CurrentProject.AllForms("Employees").IsLoaded Then
        Forms!Employees.Recordset.FindFirst "EmployeeID=" & Me.EmployeeID
    End If
End If

Any suggestions would be appreciated!
 
Last edited by a moderator:

Micron

AWF VIP
Local time
Today, 12:52
Joined
Oct 20, 2018
Messages
3,476
You lost me. Why don't you just present the results by using unbound controls (combos?) in one form's header to filter the results? If there can be more than one matching record, just display them in the form detail section as a continuous or data sheet format. Or this might be one of the few times I'd use a split form. I don't get the multi-form approach, and if there is no 1 to many relationship (e.g. PO to PO line items) then there's no need for a subform either.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:52
Joined
Feb 19, 2002
Messages
42,971
I have some pretty sophisticated search forms and I do it this way:
The "run" code examines the criteria fields and builds a Where clause for a hard coded query string. I save the updated querydef. I then run a dCount to determine if there are 0, 1, or many rows returned. If 0, I display a message, if 1, I open the singleview edit form which is bound to the querydef I just resaved. If the count is > 1, I open the list form which is bound to the querydef I just resaved. The "many" form, has a double-click action on the main field of a row to open the single view form.
 

evictme

Registered User.
Local time
Today, 11:52
Joined
May 18, 2011
Messages
168
I have some pretty sophisticated search forms and I do it this way:
The "run" code examines the criteria fields and builds a Where clause for a hard coded query string. I save the updated querydef. I then run a dCount to determine if there are 0, 1, or many rows returned. If 0, I display a message, if 1, I open the singleview edit form which is bound to the querydef I just resaved. If the count is > 1, I open the list form which is bound to the querydef I just resaved. The "many" form, has a double-click action on the main field of a row to open the single view form.
Thats exactly what I am looking to do. The Form with the subform/results is only so I can make the results in datasheet view and interactive so they can select the record they are looking for. I want to the dCount portion but im not familiar enough with the syntax to know exactly how to word it for when it opens

Form
1595021557929.png


Search form (Replaces Ctrl-F or thru search button)
1595021565394.png



Results (client name can be double-clicked)
Untitled.png
 

evictme

Registered User.
Local time
Today, 11:52
Joined
May 18, 2011
Messages
168
Thanks for the help folks. Found this solution.

This allows for a search form where you enter the term into a unbound text box. The Results form will come up and load the subform with actual resutls.

SearchFormResults

Onload Event
If Me.SearchQ_Customer_subform1.Form.Recordset.RecordCount = 0 Then
MsgBox "Your search produced 0 results" & _
vbCrLf & " " & _
vbCrLf & "Please try again", vbExclamation, "No records"
DoCmd.Close acForm, Me.Name, acSaveNo
End If


SearchFormResults Subform w/ datasheet

On load
If Me.Form.Recordset.RecordCount = 1 Then
DoCmd.OpenForm "Clients", , , , , , Me.ClientID
If CurrentProject.AllForms("Clients").IsLoaded Then
Forms!Clients.Recordset.FindFirst "ClientID=" & Me.ClientID
End If
If CurrentProject.AllForms("SearchForm-C").IsLoaded Then
DoCmd.Close acForm, "SearchForm-C", acSaveNo
End If
If CurrentProject.AllForms("Search-Clients").IsLoaded Then
DoCmd.Close acForm, "Search-Clients", acSaveNo
End If
End If
 

Users who are viewing this thread

Top Bottom