No Match with ADO

[Guest_imported]

I'm using ADO to link to an Access 2000 DB. I'm updating numerous text boxes
on a form with info from the DB via a search using the contents of one of
the text boxes entered by the user. I'm also using the following SQL commandtext
with variables for the field name(s) and Where parameter:

adoJobProfile.RecordSource = "select fldJobNum, fldCaseNum, fldSerialNum
from tblJobProfile where " & strFldName _
& " = '" & strSearch & "'"

If I enter a value in the text box (e.g., job number) that IS in the database,
everything updates just fine. If I enter a value that IS NOT in the database,
however, the check boxes on the form change to gray checks (should be empty)
and no error message is generated (via Msgbox) which is what should happen.
Also, I can no longer search for a valid job number until I restart the
program. I'm using the following selection structure just after the SQL
statement to display the "Invalid Entry" message.

If adoJobProfile.Recordset.Fields(strFldName) <> strSearch Then
intCount = 0
For intCount = 0 To 22
chkPart(intCount).Value = 0
chkPart(intCount).Enabled = False
Next intCount
txtInstr.Enabled = False
intRetVal = MsgBox(conMsg, conBtns, &quot;Invalid Entry&quot
textbox.SelStart = 0
textbox.SelLength = Len(textbox.Text)
Exit Sub
End If

Obviously not being familiar with ADO or SQL, I'm not sure how to code the
selection structure properly to get the desired result. Any help would be
appreciated.

Thanks,
R.T. Smith

 

[MichaelRed]

I am probably not reading this correctly.

NoMatch is 'out-of-favor' w/ Microsoft. They have gone one small step toward STANDARD SLQ and now expect you to use the EOF property to check the various search results. This is also why the controls 'go grey' - you are at EOF for te recordset, so no field values are 'defined'.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over