Sorting in Forms

  • Thread starter Thread starter Kelly23
  • Start date Start date
K

Kelly23

I have written coding which when I am within a form, I can select a
value from one column and other relevant fields automatically populate.
This seems to be working fine. After the original test stage, I then
went and added more data into the table. I can sort the data from
within the table database view, but I can't seem to be able to sort the
field within the form. Can I modify the code that I have written to
include this ? Here's my code :

Option Compare Database

Private Sub Scheme_AfterUpdate()
Dim strSQL As String 'declare a variable of type string to be
used to hold our query
Dim rs As DAO.Recordset 'declare a variable of type recordset
to be used to hold the data from our query

strSQL = "SELECT PackageNo, [Project Manager], [OLASCode],
[Budget], [Original Contract Sum] FROM Tbl_Scheme_Details WHERE
SchemeID = " & Scheme.Value
Set rs = CurrentDb.OpenRecordset(strSQL) 'query the DB

If Not rs.EOF Then 'Check that something was returned from the
query
txtPackageNo = rs(0) 'Assign the package no field the value
from the query
txtProjectManager = rs(1) 'Assign the project manager field
the value from the query
txtOLASCode = rs(2) 'Assign the OLAS code field the value
from the query
txtBudget = rs(3) 'Assign the Budget field the value from the
query
txtOriginalContractSum = rs(4) 'Assign the original contract
sum field the value from the query

Else
txtPackageNo = "" 'No data was returned from query so blank
the package no field
txtProjectManager = "" 'No data was returned from query so
blank the project manager field
txtOLASCode = "" 'No data was returned from query so blank
OLASCode field
txtBudget = "" 'No data was returned from query so blank
budget field
txtOriginalContractSum = "" 'No data was returned from query so
blank original contract sum field

End If

rs.Close 'close the recordset object
Set rs = Nothing 'set the recordset object to nothing

End Sub
 
The way it stands, by using unbound controls, the only solution i see is
to sort in the SQL statement itself.

strSQL = "SELECT PackageNo, [Project Manager], [OLASCode], [Budget],
[Original Contract Sum] FROM Tbl_Scheme_Details WHERE SchemeID = " &
Scheme.Value & " ORDER BY [WhicheverField]"

But I don't really understand what you are trying to do... the code you
posted queries the database and loads the values from the first record
only into the textboxes... and then destroys the recordset?

If you saved the query, and created a continuous form based on that
query, it would list all returned records, and you could use access's
built in filter and sort functionality (on the right-click menu)...

hth,

aaron
 
Back
Top