Indexing/Keys

[scottco]

I am using the following to access a table;
frmEstimateDetail.datEstimate.Recordset.Index = "EstimateRef" , and then accessing the table by using the Seek method. This is OK, but I would like to move through the table in "EstimetRef" and "Item" order so that the form displayed makes more sense.

How do you use more than 1 index ?

Cheers

 

[chiph]

In your SQL, use an ORDER BY statement after your WHERE clause. Example:

SELECT EstimateRef, Item, ItemDescription
FROM EstimateDetail
WHERE EstimateRef = 123456
ORDER BY EstimateRef ASC, Item ASC

The "ASC" means ascending order. You can also use "DESC" for descending order.

Chip H.

 

[Guest_imported]

Thanks Chip,

I have a form which displays various fields from a table. I move thru them by using MoveNext/MovePrevious etc.
I use
frmEstimateDetail.datEstimate.Recordset.Index "EstimateRef"
frmEstimateDetail.datEstimate.Recordset.MoveFirst
frmEstimateDetail.datEstimateHeader.Recordset.Index "EstimateRef"

to position to the first record, but when I MoveNext/MovePrevious, the EstimateRef order is OK, but the ItemReference order is all over the place.

I have used SQL on DBGrids etc and that's OK, But I can't seem to grasp what is involved here.

Any further assistance would be very gratefully received.

Regards,

A very budding beginner.

Graham (ScottCo)

 

[chiph]

Oh, you're using data controls.
<grrrr>

In that case, you need to build your SQL dynamically, then assign it to the datacontrol's RecordSource property, and re-run the query.

To build the SQL dynamically, you just concatenate some strings together, like so:
[tt]
sSQL = &quot;SELECT Col1, Col2, Col3 &quot;
sSQL = sSQL & &quot;FROM MyTable &quot;
If bAscending Then
[tab]sSQL = sSQL & &quot;ORDER BY Col1 ASC, Col2, ASC&quot;
Else
[tab]sSQL = sSQL & &quot;ORDER BY Col1 DESC, Col2, DESC&quot;
End If

' Re-run the query
datEstimate.RecordSource = sSQL
datEstimate.Refresh
[/tt]

You wouldn't then use the Index property, as the SQL has done it for you already.

Note that if you're using MSAccess as a database, it doesn't exactly follow the SQL standards -- use the Access help files to build your statement, but keep in mind that your SQL will have to change some if you decide to switch to SQL Server, DB2, or Oracle as a database.

And when you get some time, start learning about ADO. As far as I know, it's the only data access technology that will be supported under the next version of VB (VB7, aka VB.NET)

Chip H.