Datasheet view

[khicon73]

I have a continues form to show all item to be able to check and uncheck to view the query. it's working fine with the code below.

I have 2 questions on this form:
First, how do I sort the record based on user selected?
Second, I need to add a check box and named "chkAll". I would like if this box checked, then the "ItemShow" in the code below would like to turn to "True"...Please help...thanks.


Private Sub cmdOK_Click()
' Display only selected fields in Client
' form in datasheet view

Dim DB As Database
Dim RS As Recordset
Dim qdf As QueryDef
Dim strSQL As String

On Error GoTo HandleErr
' save current record
DoCmd.RunCommand acCmdSaveRecord

Set DB = CurrentDb
Set qdf = DB.QueryDefs("DatasheetView")

strSQL = "Select "
Set RS = Me.RecordsetClone
RS.MoveFirst
Do Until RS.EOF
If RS!ItemShow = True Then
' Build SQL string
strSQL = strSQL & RS!ItemName & ", "
End If
RS.MoveNext
Loop

DoCmd.Close
strSQL = Left(strSQL, Len(strSQL) - 2) & " From tblDisease"
qdf.SQL = strSQL
DoCmd.OpenQuery qdf.Name

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_[_frm_SelectFieldsDialog].cmdOK_Click "
End Select
Resume ExitHere
Resume
End Sub

 

[Remou]

You can use Order By to sort:

<...>
    DoCmd.Close
    strSQL = Left(strSQL, Len(strSQL) - 2) & " From tblDisease"
If Trim([Name of sort field] & "")<>"" Then
    strSQL=StrSQL & " ORDER BY " &  [Name of sort field]
End If

To update all to true, you can either use an update query (below), or the recordsetclone and loop through as you do in the code you show. The update query might look something like this:

strSQL="UPDATE MyTable SET ItemShow=True"
CurrentDb.Execute strSQL

http://lessthandot.com

 

[khicon73]

Thanks Remou for your response...I got the check All working but sort...
Where can I get the [Name of sort field]? I don't understand....

 

[Remou]

I should have said [Name of Control where the user will select sort field]. You said that you wished the user to choose what they wanted to sort by, which suggests that they fill in some control. Let us say that you have a control called cboSortBy:

If Trim(Me.cboSortBy & "")<>"" Then
    strSQL=StrSQL & " ORDER BY [" &  Me.cboSortBy & "]"
End If

In other words, if the user chooses to sort by selecting a field to sort by, add an extra clause to the SQL. YesNo?

http://lessthandot.com

 

[khicon73]

Hi Remou,
I created a combo box as you said and set row source type = field list, row source = tblDisease.

When I run the form and select sort with coding exactly what you posted from reply mail, i got an error...

2467: The expression you entered refers to an object that is closed or doesn't exist.

Thanks...

 

[Remou]

Where did you put the combobox? The header of the continuous form would be a good place. Are you sure of the name?

http://lessthandot.com

 

[khicon73]

Thanks Remou,
I got it...I accidently close the form before run the sql and sorting set equal to cboSortBy...
Now I got it fixed...and thanks for your help...
I'm very appreciated...You deserted a star...Again, thanks..