Solved

Add dropdown to current program

Posted on 2011-03-08
11
184 Views
Last Modified: 2012-05-11
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

Open in new window

0
Comment
Question by:JohnMac328
  • 6
  • 5
11 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
test this


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 " And [FieldName]= " & me.combo0
      ' if the fieldName is Text data type, use this
   '  SQL=SQL " And [FieldName]= '" & 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 Sub

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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..
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 Sub

Open in new window

0
 

Author Comment

by:JohnMac328
Comment Utility
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
0
 

Author Comment

by:JohnMac328
Comment Utility
The values are in a separate table.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
first create a query showing the fields you want to show in the result.
copy the SQL view and post here..
0
 

Author Comment

by:JohnMac328
Comment Utility
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"));

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
what is the rowsource of the combo box?
0
 

Author Comment

by:JohnMac328
Comment Utility
When I am trying to pull the value from the accounts table it is

SELECT Accounts.C_ACCOUNT FROM Accounts;
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
try this


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, sWhere 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]"
 
  'check if drop down has value selected
   if me.combo0 & ""<>"" then
      sWhere= "[C_ACCOUNT]='" & me.combo0 & "'"
      SQL=SQL & " And " & sWhere
   end if

  ' save query with new SQL statement

   Set qDef = CurrentDb.QueryDefs("qryF442audt")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' run query
   DoCmd.OpenQuery "qryF442audt"
 

End Sub

Open in new window

0
 

Author Closing Comment

by:JohnMac328
Comment Utility
That's it - Thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now