Form - Event Procedure

[qlan]

Hi,

Below is what I have for my Event Procedure:


Private Sub LastUpdate_Click()
On Error GoTo LastUpdate_Click_Error
Dim MyStr As String

DoCmd.Hourglass True
If CritStr = "" Then
MsgBox "Please SEARCH the projects first before Sorting!"
Else
MyStr = CritStr & "ORDER BY tblStatus.EnteredDate"

If Me!DescendingOrder = True Then
MyStr = MyStr & "DESC;"
Else
MyStr = MyStr & "ASC;"
End If
Me.Repaint
Me!FindList.RowSource = MyStr
Me!Feedback.Caption = "Search Completed" + Chr$(13) + Chr(10) + "Ordered by LastUpdate"
End If

Exit_LastUpdate_Click:
DoCmd.Hourglass False
Exit Sub

LastUpdate_Click_Error:
ErrN = Err
ErrS = Error$ & ":" & Me.Name
R = funError()
MsgBox ErrS
Resume Exit_LastUpdate_Click

End Sub



Then I also have a squery statement:


SQLquery = "SELECT MAXIMUM tblStatus.EnteredDate as [LastUpdate] "
SQLquery = SQLquery & "FROM (tblProjects INNER JOIN tblStatus ON tblProjects.ProjectNum = tblStatus.ProjectNum),"

However, it did not give me any result. Thanks

 

[Remou]

I think you will find that you are missing spaces in MyStr. Here, for example:

MyStr = MyStr & "DESC;"

You would need to say:

MyStr = MyStr & " DESC;"

You do not need to specify ascending, it is the default.

You do not seem to be setting the sort order (OrderBy) anywhere, nor do you seem to be setting OrderByOn anywhere and I do not see where the new string is replaceing the current record source.

 

[TheAceMan1]

How are ya qlan . . .

Your setting the [blue]RowSource[/blue] of [purple]FindList[/purple] to criteria only! . . . This will never work!

You need to append the criteria too some SQL?

If you post the SQL involved we can nail it! . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[qlan]

I did a test with this simple query in Access:

SELECT Max (tblStatus.EnteredDate)
FROM tblStatus
where tblStatus.Projectnum = '2019VN';

It gives me the last Status EnteredDate of 11/09/2006, which is correct according to our records.

However, in VB Codes, If I Do

SQLquery = "SELECT Max (tblStatus.EnteredDate)"
SQLquery = SQLquery & "FROM tblProjects LEFT JOIN tblStatus ON tblProjects.ProjectNum = tblStatus.ProjectNum,"

Then run my form, entering 2019VN as a projectnumber. It did not return any result.

If I change my VB codes to just

SQLquery = "SELECT tblStatus.EnteredDate as [LastUpdate],"

It then asks for a Parameter Value, "tblStatus.EnteredDate". Whatever, I entered here will show as my LastUpdate. Why it could not grasp the date which is 11/09/2006 in this case as my LastUpdate? Any sugessions?

Thanks so much!