Add dropdown to current program
I have added a drop down that selects values from a separate table to filter search results. I can't see how to fit the drop down into the current procedure. Also the drop down does not need to be selected every time - just when the user wants to. Any help is appreciated.
Private Sub cmdRunQuery_Click()
If Me.lstFieldList.ItemsSelected.Count = 0 Then
MsgBox "Select some field names first."
Exit Sub
End If
Dim qDef As Object
Dim SQL As String
Dim vItem As Variant
' loop through selected field names
For Each vItem In Me.lstFieldList.ItemsSelected
SQL = SQL & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
Next vItem
' build new SQL statement
SQL = "Select " & Mid(SQL, 2) & " from [F442audt]"
SQL = SQL & " Where [D_Date] Between [Begin Date] and [End Date]"
' save query with new SQL statement
Set qDef = CurrentDb.QueryDefs("qryF442audt")
qDef.SQL = SQL
Set qDef = Nothing
' run query
DoCmd.OpenQuery "qryF442audt"
End Sub
correction
'check if drop down has value selected
if me.combo0 & ""<>"" then
SQL=SQL & " And [FieldName]= " & me.combo0
' if the fieldName is Text data type, use this
' SQL=SQL & " And [FieldName]= '" & me.combo0 & "'"
end if
'check if drop down has value selected
if me.combo0 & ""<>"" then
SQL=SQL & " And [FieldName]= " & me.combo0
' if the fieldName is Text data type, use this
' SQL=SQL & " And [FieldName]= '" & me.combo0 & "'"
end if
sorry, if you intend to add the value selected from the combo as field name in the query, use this
other wise give an explanation how do you want to use the value from the drop down box..
other wise give an explanation how do you want to use the value from the drop down box..
Private Sub cmdRunQuery_Click()
If Me.lstFieldList.ItemsSelected.Count = 0 Then
MsgBox "Select some field names first."
Exit Sub
End If
Dim qDef As Object
Dim SQL As String
Dim vItem As Variant
' loop through selected field names
For Each vItem In Me.lstFieldList.ItemsSelected
SQL = SQL & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
Next vItem
'check if drop down has value selected
if me.combo0 & ""<>"" then
SQL=SQL & ",[" & me.combo0 & "]"
end if
' build new SQL statement
SQL = "Select " & Mid(SQL, 2) & " from [F442audt]"
SQL = SQL & " Where [D_Date] Between [Begin Date] and [End Date]"
' save query with new SQL statement
Set qDef = CurrentDb.QueryDefs("qryF442audt")
qDef.SQL = SQL
Set qDef = Nothing
' run query
DoCmd.OpenQuery "qryF442audt"
End SubASKER
I suppose they would want to see the value in the result query. When I select the drop-down value I get this screen shot.
Example.jpg
Example.jpg
ASKER
The values are in a separate table.
first create a query showing the fields you want to show in the result.
copy the SQL view and post here..
copy the SQL view and post here..
ASKER
Here is an example of a possible select. It is filtering on EDV and that value is in the main table under C_ACCOUNT. I was putting the combo box values in a seperate table so they would easily be selectable.
SELECT F442audt.D_DATE, F442audt.C_ACCOUNT, F442audt.[C_RECORDTYPE(4)]
FROM F442audt
WHERE (((F442audt.C_ACCOUNT)="EDV"));
what is the rowsource of the combo box?
ASKER
When I am trying to pull the value from the accounts table it is
SELECT Accounts.C_ACCOUNT FROM Accounts;
SELECT Accounts.C_ACCOUNT FROM Accounts;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's it - Thanks
Open in new window