>>>>>>Hi,
>>>>>>how I can insert this SQL statment :
>>>>>>" SELECT * FROM unit, unittype, estate WHERE untestateidno = estateid AND unttypeidno = unittypeid"
>>>>>>
>>>>>>TO this RecordSet :
>>>>>>
>>>>>>With rstUnit
>>>>>> .Index = "PrimaryKey"
>>>>>> .Open "unit", myConn, adOpenKeyset, adLockPessimistic, adCmdTableDirect
>>>>>>End With
>>>>>>
>>>>>>Many thanks
>>>>>>adsc
>>>>>
>>>>>
>>>>>Instead of giving a table name, give your SQL query and use the ORDER BY clause instead of using an index like this :
>>>>>

With rstUnit
>>>>>   .Open "SELECT * FROM unit, unittype, estate WHERE untestateidno = estateid AND unttypeidno = unittypeid ORDER BY FieldName", myConn, adOpenKeyset, adLockPessimistic
>>>>>End With

>>>>
>>>>Eric Hi,
>>>>
>>>>but I need to use the ,adCmdTableDirect to be able to use the .Seek method
>>>
>>>You can't have both!!!
>>
>>So, what do u suggest Eric to solve this. the origenal statment is :
>>With rstUnit
>> .Index = "PrimaryKey"
>> .ActiveConnection = myConn
>> .CursorLocation = adUseServer
>> .CursorType = adOpenKeyset
>> .LockType = adLockPessimistic
>> .Source = " SELECT * FROM unit, unittype, estate " & _
>> "WHERE untestateidno = estateid AND unttypeidno = unittypeid"
>> .Open
>>End with
>>
>>then I changed it to this statment
>>.Open "unit", myConn, adOpenKeyset, adLockPessimistic, adCmdTableDirect
>>
>>but in this case I could not use the sql statment to get the data from the 3 tables.
>>
>>what do u suggest Eric.
>
>Sorry but you can't have it all!!! Normally, the best solution is to forgot the Seek method and use the Where clause to retrieve to least amount of records.

Okey Eric,

Could you please advice me how to issue the Where clause (BUT) after making the Recordset same as the code I posted above.
Meaning ... After I issue the code above I open the form then the user has to select the data from 3 ComboBox, then I'll take the selected ComboBox values and make the search in the current record set and find the record matching the 3 selected values. (I think it's like firing another where clause for the current Select statment with the current Where clause).

Highly appreciate it...