February 11th, 2005, 06:10 AM
|
|
Contributing User
|
|
Join Date: Feb 2005
Posts: 56
Time spent in forums: 19 h 50 m 7 sec
Reputation Power: 4
|
|
|
Using a query and then adapting it on click
I use a query in my form to bring up results:
Code:
Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String, strFROM As String, strWHERE As String, strCUSTOMER As String, strORDERBY As String
strSELECT = "*"
strFROM = "[mainstatic]"
strWHERE = ""
If Not IsNull(Shopnumbertxt) Then strWHERE = strWHERE & " And [mainstatic]![Job number] = [Forms]![view job]![shopnumbertxt]"
If Not IsNull(jobstatusfilter) Then strWHERE = strWHERE & " And [mainstatic]![Job status] = [Forms]![view job]![jobstatusfilter]"
If Not IsNull(priorityfilter) Then strWHERE = strWHERE & " And [mainstatic]![priority] = [Forms]![view job]![priorityfilter]"
If Not IsNull(datestarttxt) Then strWHERE = strWHERE & " And [mainstatic]![Date Issued] >= [Forms]![view job]![datestarttxt]"
If Not IsNull(enddatetxt) Then strWHERE = strWHERE & " And [mainstatic]![Date Issued] <= [Forms]![view job]![enddatetxt]"
strCUSTOMER = ""
If aramiskacheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "aramiska" & Chr(34) & " Or [mainstatic]![contract] ="
If bespokecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bespoke" & Chr(34) & " Or [mainstatic]![contract] ="
If btcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bt" & Chr(34) & " Or [mainstatic]![contract] ="
If coralcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "coral" & Chr(34) & " Or [mainstatic]![contract] ="
If kingstoncheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "Kingston" & Chr(34) & " Or [mainstatic]![contract] ="
If mducheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "mdu" & Chr(34) & " Or [mainstatic]![contract] ="
If pipexcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "pipex" & Chr(34) & " Or [mainstatic]![contract] ="
If patientlinecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "patientline" & Chr(34) & " Or [mainstatic]![contract] ="
If residentialcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "residential" & Chr(34) & " Or [mainstatic]![contract] ="
If sportstvcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "sports tv" & Chr(34) & " Or [mainstatic]![contract] ="
If strCUSTOMER <> "" Then
strCUSTOMER = Left$(strCUSTOMER, Len(strCUSTOMER) - 28)
strWHERE = strWHERE & " And [mainstatic]![Customer]=" & strCUSTOMER
Else
strWHERE = strWHERE
End If
strSQL = "SELECT" & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
strORDERBY = ""
If strORDERBY <> "" Then strSQL = strSQL & "ORDER BY" & strORDERBY
BuildSQLString = True
End Function
And on the change of a dropdown or the click of a button I call
Code:
Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
CurrentDb.QueryDefs("qryshopSelect").SQL = strSQL
RefreshDatabaseWindow
Me.Form.RecordSource = "qryshopselect"
Me.Refresh
This just updates the form to run the query again using the new variables.
What I would like Is to have an image that when clicked sorts the fields using the current SQL query and adding a ORDER BY to the end... Is that possible?
If no one knows then does anyone know how to use images and give them values like a checkbox or something, like true / false?? Many thanks,
pete.
|
Dev Articles Community Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
