URGENT: Searching with ADO for matches

[Spooked]

Hoping for some advice here:

I am currently using Visual Basic 6 with Microsoft Access to enter tool information into a tooling database.

In this front-end application I have a form for ordering new tools with a couple of text boxes and a drop down combo.

What I need to be able to do is search the existing records in a table called Tool_Order for an exact match to the data entered in to the form so that I can warn the user if they attempt to order the same tool again.

The Tool_Order table from access is in a datagrid filled from an adodc connection and in the code I have used ADO to update the information stored in the Access tables.

How can I search the datagrid fields for matches to the text box inputs? Is there another way with the method I am using (ADO)?

Any help greatly appreciated..

[Spooked]

The only method I can think of is to create a new recordset and check to see if it is empty or not. Here's my code:

strQuery = "SELECT DISTINCTROW Tips.Tip_Size_ID, Tips.Tip_Size_OD, Tip_Types.Tip_Description FROM Tip_Types INNER JOIN Tips ON Tip_Types.Tip_Type_ID = Tips.Tip_Type_ID WHERE (((Tips.Tip_Size_ID)=txtTipsize.Text) AND ((Tips.Tip_Size_OD)=txtTipsize2.Text) AND ((Tip_Types.Tip_Description)=cboTipTypes.Text))"
rsQuery.Open strQuery, myConnection, adOpenDynamic, adLockOptimistic

If rsQuery.RecordCount > 0 Then

If (MsgBox("An exact match of the tool you are ordering is already selected for ordering. Do you wish to order another?", vbQuestion) = vbYes) Then

I get an object variable or with block not set error with this code so I'm guessing I haven't used the correct format for my SQL statement. Can I use txtTipsize.text in the statement in that format? Can anyone see what the problem is?

[Negative0]

Did you declare the recordset in this function???

dim rsQuery as New Recordset

Hope this helps

[Spooked]

sure did

[Ianpbaker]

Hi spooked

Your 99% There just change it to this

strQuery = "SELECT DISTINCTROW Tips.Tip_Size_ID, Tips.Tip_Size_OD, Tip_Types.Tip_Description FROM Tip_Types INNER JOIN Tips ON Tip_Types.Tip_Type_ID = Tips.Tip_Type_ID WHERE (((Tips.Tip_Size_ID)='" & txtTipsize.Text & "'") AND ((Tips.Tip_Size_OD)='" & txtTipsize2.Text & "') AND ((Tip_Types.Tip_Description)='" & cboTipTypes.Text & "'"))"

The single pips I put in are providing all the fields are strings.

Hope this helps

Ian

[Spooked]

Seem to be getting error after txtTipsize.Text & "'")

Error

Expected: End of statement

[Ianpbaker]

Sorry Spooked

change the "'"))" ath the end to "'))"


Ian