Solved

Add dropdown to current program

Posted on 2011-03-08
11
185 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
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
 

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
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.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

911 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

18 Experts available now in Live!

Get 1:1 Help Now