[RESOLVED] Searching for compound names in a access db file?

[Justin M]

Hey guys I ws wondering. I have a database made in access 2000 that is called compound names.

i use it in a program where the user enters a compound like FeO2 or something in text1 and press the search button. i would like it so it would search the database to look for the compound in text1 and if there is a match then put the compound name which is in the column debside the first colum that has compounds and put it in text2.

If there is not a match found then have text2 say no matches found.

I can use this code right?

Dim con As ADODB.Connection
Dim strSQL As String
Dim strCol As String
Set con = New ADODB.Connection
con.CursorLocation = adUseClient
con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.path & "\compoundname.mdb;" & "Jet OLEDB:Database Password=****;"
strSQL = "SELECT * FROM table1"
strCol = "compound"
Call FillCombo(List2, con, strSQL, strCol)
con.Close
Set con = Nothing

but see this code originally was for another prog i had, but i dont want it search using what is in list 2, i would like it to use text1

Can you hook a brotha up? Thanks alot!!!!!

[Hack]

?????????????? ???????????? And what is the problem?

[dilettante]

Missing WHERE clause in the SQL query?

[Justin M]

Oh yea, the code I posted would load what is in a column in a database file into list2,

so my problem is I need to modify the code so it searches for the compound is the first column of the compounds database file and if there is a match found between text1 and the first colum, then text2 would display what is in the other column for that row.

How would I set up a Where clause in that code?

thanks!

[si_the_geek]

Spend some time checking out the tutorials in the "SQL" section of our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[Justin M]

Alrighty, I think I got it, but when I go to try it says the select statement contains reserved word or an argument name that is spelled wrong.

heres the code I have now.

Private Sub Command1_Click()
 
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim strCol As String
Set rs = New ADODB.Recordset
'Set con = ADODB.Connection
Set con = New ADODB.Connection
con.CursorLocation = adUseClient
 
con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\compoundnamedb.mdb;" & "Jet OLEDB:Database Password=;"
strSQL = "Select Compound, Compoundname, From Table1 " & _
"Where (Compound = '" & Text1.Text & "')"
rs.Open strSQL, con, adOpenStatic, adLockOptimistic
' then when your rs returns...
If rs.EOF = True Then  ' might also be able to check if rs.RecordCount=0
  Text2.Text = "Unknown"
 
 
Else
   ' note: use of & vbNullString below prevents errors if a field is null
   rs.MoveFirst
   Text2.Text = rs.Fields("Compoundname") & vbNullString
 '  Label26.Caption = rs.Fields("TypeOfFile") & vbNullString
 'Label36.Caption = rs.Fields("Description") & vbNullString
End If
 
 
rs.Close
Set rs = Nothing
End Sub

When I go to debug it highlights the line

rs.Open strSQL, con, adOpenStatic, adLockOptimistic

I hope this code above searches for a match between text1 and the compound colum in the database file and if there is a match put what is in the same row but over in compoundname column in text2

[si_the_geek]

The problem is a typo which causes a syntax error that confuses the database engine - you have a comma after the last field name in the Select list, so it thinks that "From" is also the name of a field, rather than the keyword as you intended.

The rest of your code should work, but is not ideal. Here are a few things that it would be best to tidy up:

• As you have declared and opened the connection in that routine, you should also close it properly (in the same way you have for the recordset).
• There is no need for the rs.MoveFirst, as you are already there when the recordset is first opened (which was the last action that was done with it).
• It is not a good idea to set the .CursorLocation to adUseClient unless you really need to, as it can cause problems and slow things down (particularly for multiple user systems). In this case there is no need for it.
• The parameters you used for rs.Open are not suited to the way you are using the data - it would be better (quicker and fewer problems for multi-user systems) to specify the apt parameters, as explained here.

[Justin M]

Excellent, thanks for your help!