Retrieve recs in numerical order?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Table "Contacts" is on the many side of a 1 - many relationship, so I have
the following fields:

Primary key - ContactID (Autonumber) Unique
Foreign key - AcctNum (Text)
Text Field - LastName (Text)

In a module, I am running a search on this table that looks like the
following:
rsContacts.FindFirst ( "[AcctNum] = '" & AcctNum & "'") where rsContacts
has been set to:

Set rsContacts = db.OpenRecordset("Contacts", dbOpenDynaset)

and that works fine but I want the results to be returned to me in the order
they were entered (because they will then be inserted into a document in this
order after going through a loop), meaning by "ContactID" from lowest number
to greatest. What can I add into my find criteria (or some other way) to
get my desired results. Which in other words is:

Ultimately I want all [LastName] for [AcctNum] = xxxx sorted ascending by
[ContactID].

THANKS in advance!
 
Instead of using the table itself, create a parameter query that is ordered
however you want it.
 
Thanks, I kind of figured, but I don't know how to open a query as a DAO
recordset? Do I have to do a querydef in order to do that?

Klatuu said:
Instead of using the table itself, create a parameter query that is ordered
however you want it.

Sandie said:
Table "Contacts" is on the many side of a 1 - many relationship, so I have
the following fields:

Primary key - ContactID (Autonumber) Unique
Foreign key - AcctNum (Text)
Text Field - LastName (Text)

In a module, I am running a search on this table that looks like the
following:
rsContacts.FindFirst ( "[AcctNum] = '" & AcctNum & "'") where rsContacts
has been set to:

Set rsContacts = db.OpenRecordset("Contacts", dbOpenDynaset)

and that works fine but I want the results to be returned to me in the order
they were entered (because they will then be inserted into a document in this
order after going through a loop), meaning by "ContactID" from lowest number
to greatest. What can I add into my find criteria (or some other way) to
get my desired results. Which in other words is:

Ultimately I want all [LastName] for [AcctNum] = xxxx sorted ascending by
[ContactID].

THANKS in advance!
Click to expand...
Click to expand...
 
Here is some code I extracted from one of my apps that may give you some idea
on how to do it. In this case, the query has 2 parameters. You just need to
pass a value for however many parameters you need to filter your data. Set
the sort order in the query itself.

Dim rstItms As Recordset 'Recordset to load ITM Name in Header
Dim qdf As QueryDef 'Query def to load data

Set qdf = CurrentDb.QueryDefs("qselSCCBhdr")
qdf.Parameters(0) = Me.cboResource
qdf.Parameters(1) = Me.cboPeriod
Set rstItms = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)


Sandie said:
Thanks, I kind of figured, but I don't know how to open a query as a DAO
recordset? Do I have to do a querydef in order to do that?

Klatuu said:
Instead of using the table itself, create a parameter query that is ordered
however you want it.

Sandie said:
Table "Contacts" is on the many side of a 1 - many relationship, so I have
the following fields:

Primary key - ContactID (Autonumber) Unique
Foreign key - AcctNum (Text)
Text Field - LastName (Text)

In a module, I am running a search on this table that looks like the
following:
rsContacts.FindFirst ( "[AcctNum] = '" & AcctNum & "'") where rsContacts
has been set to:

Set rsContacts = db.OpenRecordset("Contacts", dbOpenDynaset)

and that works fine but I want the results to be returned to me in the order
they were entered (because they will then be inserted into a document in this
order after going through a loop), meaning by "ContactID" from lowest number
to greatest. What can I add into my find criteria (or some other way) to
get my desired results. Which in other words is:

Ultimately I want all [LastName] for [AcctNum] = xxxx sorted ascending by
[ContactID].

THANKS in advance!
Click to expand...
Click to expand...
Click to expand...
 
Thanks!! Worked flawlessly!!

Klatuu said:
Here is some code I extracted from one of my apps that may give you some idea
on how to do it. In this case, the query has 2 parameters. You just need to
pass a value for however many parameters you need to filter your data. Set
the sort order in the query itself.

Dim rstItms As Recordset 'Recordset to load ITM Name in Header
Dim qdf As QueryDef 'Query def to load data

Set qdf = CurrentDb.QueryDefs("qselSCCBhdr")
qdf.Parameters(0) = Me.cboResource
qdf.Parameters(1) = Me.cboPeriod
Set rstItms = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)


Sandie said:
Thanks, I kind of figured, but I don't know how to open a query as a DAO
recordset? Do I have to do a querydef in order to do that?

Klatuu said:
Instead of using the table itself, create a parameter query that is ordered
however you want it.

:

Table "Contacts" is on the many side of a 1 - many relationship, so I have
the following fields:

Primary key - ContactID (Autonumber) Unique
Foreign key - AcctNum (Text)
Text Field - LastName (Text)

In a module, I am running a search on this table that looks like the
following:
rsContacts.FindFirst ( "[AcctNum] = '" & AcctNum & "'") where rsContacts
has been set to:

Set rsContacts = db.OpenRecordset("Contacts", dbOpenDynaset)

and that works fine but I want the results to be returned to me in the order
they were entered (because they will then be inserted into a document in this
order after going through a loop), meaning by "ContactID" from lowest number
to greatest. What can I add into my find criteria (or some other way) to
get my desired results. Which in other words is:

Ultimately I want all [LastName] for [AcctNum] = xxxx sorted ascending by
[ContactID].

THANKS in advance!
Click to expand...
Click to expand...
Click to expand...
 
Of course it works flawlessly, I wrote it :)

Glad I could help.

Sandie said:
Thanks!! Worked flawlessly!!

Klatuu said:
Here is some code I extracted from one of my apps that may give you some idea
on how to do it. In this case, the query has 2 parameters. You just need to
pass a value for however many parameters you need to filter your data. Set
the sort order in the query itself.

Dim rstItms As Recordset 'Recordset to load ITM Name in Header
Dim qdf As QueryDef 'Query def to load data

Set qdf = CurrentDb.QueryDefs("qselSCCBhdr")
qdf.Parameters(0) = Me.cboResource
qdf.Parameters(1) = Me.cboPeriod
Set rstItms = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)


Sandie said:
Thanks, I kind of figured, but I don't know how to open a query as a DAO
recordset? Do I have to do a querydef in order to do that?

:

Instead of using the table itself, create a parameter query that is ordered
however you want it.

:

Table "Contacts" is on the many side of a 1 - many relationship, so I have
the following fields:

Primary key - ContactID (Autonumber) Unique
Foreign key - AcctNum (Text)
Text Field - LastName (Text)

In a module, I am running a search on this table that looks like the
following:
rsContacts.FindFirst ( "[AcctNum] = '" & AcctNum & "'") where rsContacts
has been set to:

Set rsContacts = db.OpenRecordset("Contacts", dbOpenDynaset)

and that works fine but I want the results to be returned to me in the order
they were entered (because they will then be inserted into a document in this
order after going through a loop), meaning by "ContactID" from lowest number
to greatest. What can I add into my find criteria (or some other way) to
get my desired results. Which in other words is:

Ultimately I want all [LastName] for [AcctNum] = xxxx sorted ascending by
[ContactID].

THANKS in advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top