Solved

Add dropdown to current program

Posted on 2011-03-08
11
187 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35070935
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35070942
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35071043
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:JohnMac328
ID: 35071275
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
ID: 35071281
The values are in a separate table.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35071378
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
ID: 35071595
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35071623
what is the rowsource of the combo box?
0
 

Author Comment

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

SELECT Accounts.C_ACCOUNT FROM Accounts;
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35071743
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
ID: 35071791
That's it - Thanks
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

840 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