[RESOLVED] Stuck with search query in VB6 and Access

[BHCluster]

As stated above I am bit stuck whit search query I have used the code from the tutorial on this forum to connect the access file and display data from the table which works fine, where I am getting stuck is when I use this code:

Private Sub txtSearch_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
frmDisplay.Show
Unload Me
frmDisplay.lstResult.Clear
Dim strSQL As String
    'build the SQL statement based on what the user typed in txtSearch
  strSQL = "SELECT * FROM Data"
  If txtSearch.Text <> "" Then
    strSQL = strSQL & " WHERE Name = " & Val(txtSearch.Text)
  End If
     'close the recordset (required before reloading it)
  ar.Close
     'load the new data
  ar.Open strSQL, ac, adOpenKeyset, adLockPessimistic, adCmdText
     'show the data
  frmDisplay.lstResult.AddItem ar.Fields("Name")
  frmDisplay.lstResult.AddItem ar.Fields("Surname")
  frmDisplay.lstResult.AddItem ar.Fields("Tel")
  frmDisplay.lstResult.AddItem ar.Fields("Mobile")
  frmDisplay.lstResult.AddItem ar.Fields("Firma")
  frmDisplay.lstResult.AddItem ar.Fields("EMail")
  frmDisplay.lstResult.AddItem ar.Fields("Address")
  frmDisplay.lstResult.AddItem ar.Fields("Web")
  frmDisplay.lstResult.AddItem ar.Fields("Account")
End If
End Sub

I get the result which is the first record in a table always but not what I have searched for. If anybody knows what I am doing wrong please help!

[si_the_geek]

That behaviour should only happen if txtSearch.Text is blank. What is the value of strSQL just before you reach the ar.Open line? (the easiest way to show us is to use Debug.Print strSQL , then copy & paste from the Immediate window)

Is there any code in frmDisplay which could be running too? (most likely in the Form_Load event)

[BHCluster]

In the Form_Load event only code that I have is

 Me.Top = (Screen.Height - Me.Height) / 2
  Me.Left = (Screen.Width - Me.Width) / 2

And just to clarify I do this

Private Sub txtSearch_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
frmDisplay.Show
Unload Me
frmDisplay.lstResult.Clear
Dim strSQL As String
    'build the SQL statement based on what the user typed in txtSearch
  strSQL = "SELECT * FROM Data"
  If txtSearch.Text <> "" Then
    strSQL = strSQL & " WHERE Name = " & Val(txtSearch.Text)
  End If
     'close the recordset (required before reloading it)
  ar.Close
     'load the new data
Debug.Print strSQL
  ar.Open strSQL, ac, adOpenKeyset, adLockPessimistic, adCmdText
     'show the data
  frmDisplay.lstResult.AddItem ar.Fields("Name")
  frmDisplay.lstResult.AddItem ar.Fields("Surname")
  frmDisplay.lstResult.AddItem ar.Fields("Tel")
  frmDisplay.lstResult.AddItem ar.Fields("Mobile")
  frmDisplay.lstResult.AddItem ar.Fields("Firma")
  frmDisplay.lstResult.AddItem ar.Fields("EMail")
  frmDisplay.lstResult.AddItem ar.Fields("Address")
  frmDisplay.lstResult.AddItem ar.Fields("Web")
  frmDisplay.lstResult.AddItem ar.Fields("Account")
End If
End Sub

to get the value of strSQL

[si_the_geek]

Yes, it will then be printed to the Immediate window (use the View menu to see it), and you can copy & paste it here.


However, there is no need to do that at the moment, as I've just spotted the likely cause of the problem - you are unloading the current form before you get the value from the textbox. Doing that means that the form is automatically re-loaded with the default values for the controls (which presumably is blank for the textbox).

If you move the Unload Me to just before the End If it should work properly. Note that this is just one example of why you should always be careful with Unload Me , as any code that runs after it can cause problems.

[BHCluster]

Now I get "Data type mismatch in criteria expression"

[BHCluster]

SELECT * FROM Data WHERE NameSELECT * FROM Data WHERE Name = 0
= 0

That is from the immediate window

[si_the_geek]

Based on that output, it looks like you've run it twice, and left the cursor just after Name (I often do that kind of think myself). Unfortunately debug.print doesn't automatically start on a new line.

The error you are getting means that the data type of the value (in this case 0) and the field (Name) are not compatible. Presumably Name is a text based field, which case you need to put single quotes around the value, ie:

    strSQL = strSQL & " WHERE Name = '" & Val(txtSearch.Text) & "'"

For an explanation and examples of delimiters to use around values within SQL statements, see the FAQ article How do I use values (numbers, strings, dates) in SQL statements?

[GaryMazzone]

Also why use the Val function (doesn't that convert to number) if looking for text (string) data?

[BHCluster]

Now I am getting blank results display and

SELECT * FROM Data WHERE Name = '0'
SELECT * FROM Data WHERE Name = '0'

in the immediate window whith this code

Private Sub txtSearch_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
frmDisplay.Show
frmDisplay.lstResult.Clear
Dim strSQL As String
    'build the SQL statement based on what the user typed in txtSearch
  strSQL = "SELECT * FROM Data"
  If txtSearch.Text <> "" Then
    strSQL = strSQL & " WHERE Name = '" & Val(txtSearch.Text) & "'"
  End If
     'close the recordset (required before reloading it)
  ar.Close
     'load the new data
Debug.Print strSQL

  ar.Open strSQL, ac, adOpenKeyset, adLockPessimistic, adCmdText
     'show the data
  frmDisplay.lstResult.AddItem ar.Fields("Name")
  frmDisplay.lstResult.AddItem ar.Fields("Surname")
  frmDisplay.lstResult.AddItem ar.Fields("Tel")
  frmDisplay.lstResult.AddItem ar.Fields("Mobile")
  frmDisplay.lstResult.AddItem ar.Fields("Firma")
  frmDisplay.lstResult.AddItem ar.Fields("EMail")
  frmDisplay.lstResult.AddItem ar.Fields("Address")
  frmDisplay.lstResult.AddItem ar.Fields("Web")
  frmDisplay.lstResult.AddItem ar.Fields("Account")
Unload Me
End If
End Sub

[GaryMazzone]

Remove the Val function from the code.

WHERE Name = '" & Val(txtSearch.Text) & "'"
Should be

WHERE Name = '" & txtSearch.Text & "'"

[BHCluster]

Disregard my last post yeah that was it. It is working now perfect. Thanks a lot for your help it means so much!