Debug help!

[yhchen]

Text search in a form

Hi guys

I know very little about VBA codes but managed to find a well-written VBA code which I can use for my form - which you can enter text and search to display the result in the subform. It works perfectly well...

Private Sub cmdSearch_Click()
Dim LSQL As String
Dim LSearchString As String

If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

LSearchString = txtSearchString

'Filter results based on search string
LSQL = "select * from A2AtivatedMembers"
LSQL = LSQL & " where Surname LIKE '*" & LSearchString & "*'"

Form_Data.RecordSource = LSQL

lblTitle.Caption = "Customer Details: Filtered by '" & LSearchString & "'"

'Clear search string
txtSearchString = ""

MsgBox "All Record containing " & LSearchString & "."

However, I would like to create the 2nd text box to enable the search for "Forename1" and I made the following amendments (which the previous code still exists):

(amended part shown as red)

Private Sub cmdSearch2_Click()
Dim LSQL As String
Dim LSearchString As String

If Len(txtSearchString2) = 0 Or IsNull(txtSearchString2) = True Then
MsgBox "You must enter a search string."

Else

LSearchString = txtSearchString2

'Filter results based on search string
LSQL = "select * from A2AtivatedMembers"
LSQL = LSQL & " where Forename1 LIKE '*" & LSearchString & "*'"

Form_Data.RecordSource = LSQL

lblTitle.Caption = "Customer Details: Filtered by '" & LSearchString & "'"

'Clear search string
txtSearchString2 = ""

MsgBox "All Record containing " & LSearchString & "."

End If

End Sub

after the amendments, it works ok but it gives me an error message saying: Run-time error '424' object required.


Please can anyone help?

Many thanks in advance for any help can be offered.

 

[MarkK]

You haven't said what fails. Do you get an error message? It seems to cause a bug in the other routine? What are the symptoms of that bug?

Apart from that, you might consider this approach to searching, which uses only one textbox, no buttons, searches both fields at once, is simpler code, and responds immediately to each character the user enters...

private sub tbSearch_Change()
[COLOR="Green"]  'handles the change event of the text box[/COLOR]
  dim search as string
[COLOR="green"]  'removes any apostrophes from the text to be searched[/COLOR]
  search = strings.replace(me.tbSearch.Text, "'", "")

[COLOR="green"]  'immediately resets the record source of the subform for each character
  'the user types, and searches for the text in both fields[/COLOR]
  Form_Data.RecordSource = _
    "SELECT * FROM A2AMembers " & _
    "WHERE Surname LIKE '*" & search & "*' OR " & _
      "Forename LIKE '*" & search & "*';"
end sub

Hope theres something here you can use.

 

[yhchen]

You haven't said what fails. Do you get an error message? It seems to cause a bug in the other routine? What are the symptoms of that bug?

Apart from that, you might consider this approach to searching, which uses only one textbox, no buttons, searches both fields at once, is simpler code, and responds immediately to each character the user enters...

Hope theres something here you can use.

Thanks lagbolt.

As the database is considerably big, searching same words in two fields may cause operational issues so if possible I would very much like to continue to seek the possibility to work this one out.

It seems once I copied and pasted the text fields and search bottom (and remaned them by adding 2 to distinguish from the previous one), the 1st search is functional but it shows "Run-time error '424' object required.

when I click debug, it highlighted:

lblTitle.Caption = "Customer Details: Filtered by '" & LSearchString & "'"

I have managed to have the 2nd one work as well as the 1st one but it also appears the same error message as above.

Can you help?

 

[MarkK]

Option Explicit is off, and there is no object named lblTitle on your form. Check the name of your label, and consider turning Option Explicit on. This will require you to declare all your variables, but it'll save you headaches like this one.
Also, I doubt you'd find an appreciable speed difference searching on two fields. SQL is blindingly fast.
Have a good one.

 

[yhchen]

Option Explicit is off, and there is no object named lblTitle on your form. Check the name of your label, and consider turning Option Explicit on. This will require you to declare all your variables, but it'll save you headaches like this one.
Also, I doubt you'd find an appreciable speed difference searching on two fields. SQL is blindingly fast.
Have a good one.

Thanks for the prompt reply lagbolt.

I am coming down to silly questions now since you seemed to find what the problem is:

how to switch Explicit on??

Thanks again for your suggestions and help. I hope you know it's been very much appreciated.

xx

 

[MarkK]

Your questions aren't silly.
For existing modules, turn Option Explicit on by typing it at the top of your module, which'll look something like this...

Option Compare Database
Option Explicit

Probably the Option Compare statement is already there.
Turn it on for new modules: In a code window do menu navigation Menu->Tools->Options->Editor tab->Code settings section. Set 'Require Variable declaration' to true (check the checkbox). After that it will appear automatically in new modules.
All the best to you.

 

[yhchen]

Your questions aren't silly.
For existing modules, turn Option Explicit on by typing it at the top of your module, which'll look something like this...

Option Compare Database
Option Explicit

Probably the Option Compare statement is already there.
Turn it on for new modules: In a code window do menu navigation Menu->Tools->Options->Editor tab->Code settings section. Set 'Require Variable declaration' to true (check the checkbox). After that it will appear automatically in new modules.
All the best to you.

It's now working perfectly fine!!

Thank you so so so so much! xxxx

 

[Banana]

ychen,

The green highlights incidates a comment, which doesn't affect how your code executes at all. All comments always start with a '

For some more information on how to debug your code, click and go to the middle section.

HTH.

 

[yhchen]

ychen,

The green highlights incidates a comment, which doesn't affect how your code executes at all. All comments always start with a '

For some more information on how to debug your code, click and go to the middle section.

HTH.

Thanks Banana.