Search Command on a Form

Question

Hi all, I tried real hard to find the answer.

I have a form (frmWebmail Data Input) where data entry is entered daily and loaded into a table (tblWebmail Data Input). The form has combo boxes, text boxes, a Save, Undo, Search, and Return to Main Menu command buttons.

When I click on the Search button it opens up a second form called frmSearch (does this make it a sub-form?).

This frmSearch form in turn requests the user to 1) enter text for the search (text box with values entered) and 2) to choose the field to search (based on a table). For example, text to search 'Alex' and field to search 'Agent Name'. When I hit the Search button here using these criteria, I get the following error code:

 "Run-Time error'424': Object required."

When I go to VBA (so not so good at that), the code that is in bold is where the debugger identified the error:

Option Compare Database

Private Sub cboSearchField_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then

        MsgBox "You must select a field to search."

    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then

        MsgBox "You must enter a search string."

    Else

        'Generate search criteria

        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

        'Filter frmWebmailDataEntry based on search criteria

Form_frmWebmailDataEntry.RecordSource = "select * from tblWebmail Data Entry where " & GCriteria

        Form_frmWebmailDataEntry.Caption = "Webmail Data Entry (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

        'Close frmSearch

        DoCmd.Close acForm, "frmSearch"

        MsgBox "Results Found."

    End If

On Error GoTo Err_cmdSearch_Click

    Screen.PreviousControl.SetFocus

    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdSearch_Click:

    Exit Sub

Err_cmdSearch_Click:

    MsgBox Err.Description

    Resume Exit_cmdSearch_Click

End Sub

Private Sub Detail_Click()

End Sub

I didn't know how to paste a capture of the VBA, sorry.

So these are the things I would appreciate your help on:

1. making the Search command work.

  a) if records are located, the frmSeach form closes and the frmWebmail Data Input form opens with all records meeting the criteria! 

  b) I think I can work out the no records found, but if someone has any suggestions. I figure once the command runs, I would have a pop up stating there were no records found, click on OK, then just back to an empty frmSearch form. Any other way to think about it?

Sorry for asking for so much.

I've used the Wizard and taken some ideas/coding from some other places, but I still can't make this work.

Thank you all muchly for any help you can provide!

Answer 1

Marusca:

You wrote:

"When I click on the Search button it opens up a second form called frmSearch (does this make it a sub-form?)."

And I just wanted to make sure you got an answer to that.  No, that isn't a subform.  A subform is a form which is actually ON a parent form and it is housed on that parent form by what is known as a subform control.  When you have a subform then it is USUALLY (but not necessarily always) linked to the parent form by means of the Master/Child links in those properties of the subform control.  The subform control is what handles the interface between the parent and child form and the master/child links is the place which defines which field, or fields, are actually the link between them.

For more about subforms you might want to check these "quick tutorials" out on my website:

http://www.btabdevelopment.com/ts/ewtrhtrts

http://www.btabdevelopment.com/ts/refer2sfrms

Answer 2

The form must be open before you can refer to it.

You also left out the s in forms.  Try:

   FormS!frmWebmailDataInput.Caption = . . .

Answer 3

Hi Marshall,

I did as you asked and then more as I relabelled and corrected in the forms/tables and switchboard everything that had a space in it. Perhaps I missed one? Right now, I get the...

Run-Time error '2450' MS Access can't find the form 'frmWebmailDataINput' referred to in a macro expression or Visual Basic Code.

Here is the VBA that I entered. I've looked everywhere to find my typo, but I can't. Any suggestions?

  Forms!frmWebmailDataInput.RecordSource = "select * from [tblWebmailDataInput] where " & GCriteria

        Form!frmWebmailDataInput.Caption = "Webmail Data Entry (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

Thanks muchly!

Marusca

Answer 4

Assuming the rest is ok, the problem is that you have spaces in the table name, tblWebmail Data Entry.

Any name that contains a any character except alphanumeric and underscore must be enclosed in [ ]

Also, it is not correct to use the Form_ syntax.  It will work in some situations, but will not work in others.

Try using:

   Forms!frmWebmailDataEntry.RecordSource = "select * from [tblWebmail Data Entry] where " & GCriteria

You can check to see if the form's new record source does not contain any records bu adding this code:

   If Forms!frmWebmailDataEntry.Recordset.RecordCount = 0 Then

      MsgBox "No records found"

   End If