Search Conflict

[jass1220]

hi .. am trying to find a record int eh recordset that has two conditions

///////////////////////////////////////
Dim strSearch As String
strSearch = "[section_ref]= '" & intSectionRef_frmSectionTrainee & "' And [trainee_id]='" & intTraineeID & "'"
rsSectionTrainee.MoveFirst
rsSectionTrainee.Find (strSearch)
rsSectionTrainee![grade] = Me.txtGrade
rsSectionTrainee.Update
//////////////////////////////////

i get this error (arguments are of teh wrong type, are out of acceptable range, or are in conflict with other one)


thanks

 

[MichaelRed]

strSearch = "[section_ref]= '" & intSectionRef_frmSectionTrainee & "' And [trainee_id]='" & intTraineeID & "'"


The naming convention would indicate the variables referenced are of type INTEGER, which would generally be used in conjunction with a field type of some numeric, the ctriteria expression casts the variable as string type.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CCLINT]

You will need to call the Find method twice, the first time with the first part of the criteria, and the second time with the last part...

 

[jass1220]

thanks alot .. but its still not working and giving the same error,, i guess the problem is caused by the single and double quotes .. am not sure .. can u help in this ?!

thanks again

i tried this after your advice

////////////////////////////////
Dim strRef As String
Dim strID As String

strRef = intSectionRef_frmSectionTrainee
strID = intTraineeID

strSearch = "[section_ref]= '" & strRef & "' And [trainee_id]='" & strID & "'"
rsSectionTrainee.MoveFirst
rsSectionTrainee.Find (strSearch)
rsSectionTrainee![grade] = Me.txtGrade
rsSectionTrainee.Update
/////////////////////////////////////////

 

[jass1220]

To CCLINT


How .. didn't get u well ?! i mean how it will be implemented ?


thanks for ur help

 

[CCLINT]

You will either need to requery the recordset using the desired criteria, or use a client side cursor and the Filter method:

rsSectionTrainee.Filter="[section_ref]= '" & strRef & "'
strSearch=[trainee_id]='" & intTraineeID & "'"
rsSectionTrainee.Find (strSearch),,adSearchForward


Or, also using a client side cursor use this method:

rsSectionTrainee.Sort = "[section_ref]
rsSectionTrainee.MoveFirst
strSearch="[section_ref]= '" & strRef & "'
rsSectionTrainee.Find (strSearch),,adSearchForward
If Not rsSectionTrainee.EOF then
If rsSectionTrainee("[section_ref]&quot = strRef Then
strSearch=[trainee_id]='" & intTraineeID & "'"
rsSectionTrainee.Find (strSearch),,adSearchForward
If rsSectionTrainee("[section_ref]&quot = strRef Then
'Record found
End If
End If
End If

(This was typed up out of my thoughts, so no testing was done but the idea should work)

In any case you need to take MichaelRed advice for numeric and strings (if the field is text, enclose the criteria in single quotes, if numeric, use no quotes.

 

[jass1220]

hi .. thanks for the help .. i used the following way and its working perfectly ... its simple one .....

///////////////////////////////
While rsSectionTrainee.EOF = False
If rsSectionTrainee![section_ref] = strRef Then
If rsSectionTrainee![trainee_id] = strID Then
rsSectionTrainee![grade] = Me.txtGrade
rsSectionTrainee.Update
Unload Me
End If
End If

rsSectionTrainee.MoveNext
Wend
/////////////////////////////////////////

thanks again

 

[leiphasw]

JASS1220: Your code will work, but you are essentially scanning the entire table yourself to find the row you are looking for. If that table is large, you may bog down pretty quickly since you will on the average scan half the rows in the table everytime. I'd recommend just doing the whole operation in a single SQL update command. This way the database will use its own optimizations using its indexes to find the rows to operate on. If the table gets large, very little performance affect will be caused since the database engine will find the rows quickly if they are indexed appropriately.


dim tRecordsAffected as Long

MyConn.Execute "UPDATE Trainee SET grade = " & Me.txtGrade & " WHERE section_ref = '" & strRef & "' AND trainee_id = '" & strID & "'", tRecordsAffected

If tRecordsAffected > 0 then
' it worked and update a row(or more)
else
' it didn't work and no rows were affected
endif