From Vba, Sort order by Fathers Full Name ??? Not GEDID

[david0121]

I have a form that I'm working on, but I'm having problems getting the data to sort in "Fathers Full Name" order as opposed to GED Family ID.

I have no VBA experience. I have posted a copy of the code that relates to the form that I'm using. When the form loads, All I want to be able to do is sort the data by fathers full name to make it easer to find parents details.


The code i have is as follows -:

Option Compare Database
 
Private Sub cmdSubmit_Click()
Dim rstParents As New ADODB.Recordset
 
If Me.lblID.Caption <> "###" Then
rstParents.Open "SELECT * FROM Individuals " & _
"WHERE ID=" & Me.lblID.Caption, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Me.cmbParents.SetFocus
rstParents!Parents = Me.cmbParents.Text
rstParents.Update
rstParents.Close
Form_Individuals.Form_Current
End If
DoCmd.Close acForm, "AssociateParents", acSaveYes
End Sub
 
 
Private Sub Form_Load()
Dim rstFamilies As New ADODB.Recordset
 
If Me.lblID.Caption = "placeholdertext" Then
Me.lblID.Caption = "###"
Me.lblIndividual.Caption = "no one selected"
End If
 
rstFamilies.Open "SELECT Families.[GED Family ID], Fathers.[Full Name], Mothers.[Full Name] " & _
"FROM (Families " & _
"LEFT JOIN Individuals " & _
"AS Fathers " & _
"ON Families.[Father ID] = Fathers.ID) " & _
"LEFT JOIN Individuals " & _
"AS Mothers " & _
"ON Families.[Mother ID] = Mothers.ID;", CurrentProject.Connection, adOpenStatic, adLockReadOnly
 
Me.cmbParents.AddItem "ID;Father;Mother"
With rstFamilies
Do Until .EOF
Me.cmbParents.AddItem ![GED Family ID] & ";" & _
.Fields("Fathers.Full Name").Value & ";" & _
.Fields("Mothers.Full Name").Value
.MoveNext
Loop
End With
End Sub

I look forward to your reply.

Kind Regards

David

 

[Rx_]

"SELECT * FROM Individuals "
Create a new query and drip the Individuals table into it.
Drop all of the field names into the query.
Choose the sort order you desire and choose the Ascending (or descending) on the field that give you the desired output.
Run the query in record format to validate the results.
in the criteria of the ID field - add a open square bracket My ID close square bracket e.g. [My ID]
Choose the SQL View
Copy the SQL text and replace the SQL in your VBA
You will have to adjust the Where ID= [My ID] and substitute it for the "WHERE ID=" & Me.lblID.Caption

Basically the same process for your other Select statements.